Question

Neilcse on Tue, 10 Mar 2015 19:40:59


This question is in continuation to the below thread.

In continuation of this thread which was resolved(power shell)

Now I have a requirement to add a code to my batch file to modify the the xml.dtsConfig file to accept the same db_name as a parameter

<Configuration ConfiguredType="Property" Path="\Package.Connections[ConnStaging].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=localhost;Initial Catalog=DBName_MySandBox;Integrated Security=SSPI; Connection Timeout = 10</ConfiguredValue>

</Configuration>

Here in the above I want to modify the dbName DBName_MySandBox to be accepted as a parameter value coming from batch file



Neil


Sponsored



Replies

jmcmullen on Tue, 10 Mar 2015 22:07:22


Hi Neil

I assume you want to call a powershell script from your .bat file in order to achieve this?

Here's an example

Simple batch file:

SET DatabaseName=MyNewDatabaseName

Powershell.exe -executionpolicy remotesigned -File C:\temp\UpdateConfigurationFile.ps1 -DatabaseName "%DatabaseName%"


This is calling a powershell script from the command line and passing in your database name as a parameter. You might want to change the path of the ps1 in the above batch.

The powershell would look like this 

UpdateConfigurationFile.ps1

param([String]$DatabaseName)
[string]$db = 'Catalog='+ $DatabaseName
(Get-Content C:\temp\xml.dtsconfig) | 
Foreach-Object {$_ -replace "Catalog=([A-Za-z0-9_]+)", $db} | Set-Content C:\temp\xml.dtsconfig

It creates a new string for your Inital Catalog and replaces it in the file using regex. 

Note the above script has two hard-coded references to the path of your dtsconfig, again you'll need to change this.

Here's a good tutorial on powershell, it might be worth going through this to get started

https://technet.microsoft.com/en-us/library/hh857337.aspx

Neilcse on Tue, 10 Mar 2015 22:12:56


Thanks Jm,

It is the same batch file which I worked with you to include the var_db, now I need to update the batch file to pass the same parameter to xml config file to update again the db name in it.  I've posted the sample code for the xml configuration file. As the SSIS pkg uses the db name in xml config file for connection.  Earlier we've updated the db name in .SQL file now I need to do the same for xml file from batch file.

Thanks for the tutorial link 

jmcmullen on Tue, 10 Mar 2015 22:16:24


Ok, the code above should work. That calls a powershell script from .bat and updates your dts.config

Neilcse on Wed, 11 Mar 2015 16:44:35


Thanks Jm

Below is the xml.dtsConfig  and .bat file, now in the same bat file, I need to add another line to pass the var_db name to xml.dtsConfig which is in the same folder 

--xml file

<Configuration ConfiguredType="Property" Path="\Package.Connections[ConnStaging].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=localhost;Initial Catalog=$(var_db);Integrated Security=SSPI; Connection Timeout = 10</ConfiguredValue>
</Configuration>

--Bat file

SET DatabaseServer=localhost
SET DatabaseName=SandBox

SET ThisFile=BatchFileName
SET RootFolder=%CD%\
SET LogFile=%RootFolder%%ThisFile%.txt

ECHO ================================================================== > "%LogFile%"
ECHO %ThisFile% >> "%LogFile%"
ECHO. >> "%LogFile%"
ECHO ================================================================== >> "%LogFile%"
ECHO. >> "%LogFile%"
ECHO Database Server: %DatabaseServer% >> "%LogFile%"
ECHO Database Name:   %DatabaseName% >> "%LogFile%"
ECHO. >> "%LogFile%"
ECHO ==================================================================
ECHO %ThisFile%
ECHO.
ECHO ==================================================================
ECHO. 
ECHO Database Server: %DatabaseServer%
ECHO Database Name:   %DatabaseName%
ECHO.
:: ========================================================================
:: CREATE DATABASE
:: ========================================================================
PUSHD %RootFolder%Tables

ECHO %time% - Creating database with table script from %CD% >> "%LogFile%"
ECHO %time% - Creating database with table script from %CD%

sqlcmd -b -S "%DatabaseServer%" -d master -i Script_Objects.sql -v var_db=%DatabaseName% >> "%LogFile%"

IF %ERRORLEVEL% NEQ 0 (
  ECHO %time% - ERROR: Unable to process table script [1001] >> "%LogFile%"
  ECHO %time% - ERROR: Unable to process table script [1001]
  GOTO end
)

ECHO %time% - Updating table defintions with script from %CD% >> "%LogFile%"
ECHO %time% - Updating table defintions with script from %CD%

sqlcmd -b -S "%DatabaseServer%" -d %DatabaseName% -i  Script_ObjectsUpdates.sql -v var_db=%DatabaseName% >> "%LogFile%"

IF %ERRORLEVEL% NEQ 0 (
  ECHO %time% - ERROR: Unable to update table defintions [1002] >> "%LogFile%"
  ECHO %time% - ERROR: Unable to update table defintions [1002]
  GOTO end
)


Neil


jmcmullen on Wed, 11 Mar 2015 17:08:36


Hey Neil

In order to get this working you need to do the following

1. Drop UpdateConfigurationFile.ps1 into one of your folders

2. Update the two paths in UpdateConfigurationFile.ps1 to point to your dts.config

3. Update your batch file with a call to the powershell script that does the replace in dts.config

4. Set the Initial Catalog to a normal database name, $(var_db) will not match the regex pattern.

For Step 3 your batch will look something like this. Update the path from c:\temp\UpdateConfigurationFile.ps1 to whatever you need

SET DatabaseServer=localhost SET DatabaseName=SandBox SET ThisFile=BatchFileName SET RootFolder=%CD%\ SET LogFile=%RootFolder%%ThisFile%.txt ECHO ================================================================== > "%LogFile%" ECHO %ThisFile% >> "%LogFile%" ECHO. >> "%LogFile%" ECHO ================================================================== >> "%LogFile%" ECHO. >> "%LogFile%" ECHO Database Server: %DatabaseServer% >> "%LogFile%" ECHO Database Name: %DatabaseName% >> "%LogFile%" ECHO. >> "%LogFile%" ECHO ================================================================== ECHO %ThisFile% ECHO. ECHO ================================================================== ECHO. ECHO Database Server: %DatabaseServer% ECHO Database Name: %DatabaseName% ECHO. :: ======================================================================== :: CREATE DATABASE :: ======================================================================== PUSHD %RootFolder%Tables ECHO %time% - Creating database with table script from %CD% >> "%LogFile%" ECHO %time% - Creating database with table script from %CD% sqlcmd -b -S "%DatabaseServer%" -d master -i Script_Objects.sql -v var_db=%DatabaseName% >> "%LogFile%" IF %ERRORLEVEL% NEQ 0 ( ECHO %time% - ERROR: Unable to process table script [1001] >> "%LogFile%" ECHO %time% - ERROR: Unable to process table script [1001] GOTO end ) ECHO %time% - Updating table defintions with script from %CD% >> "%LogFile%" ECHO %time% - Updating table defintions with script from %CD% sqlcmd -b -S "%DatabaseServer%" -d %DatabaseName% -i Script_ObjectsUpdates.sql -v var_db=%DatabaseName% >> "%LogFile%" IF %ERRORLEVEL% NEQ 0 ( ECHO %time% - ERROR: Unable to update table defintions [1002] >> "%LogFile%" ECHO %time% - ERROR: Unable to update table defintions [1002] GOTO end )

powershell.exe -executionpolicy remotesigned -File C:\temp\UpdateConfigurationFile.ps1 -DatabaseName "%DatabaseName%"


For Step 4, set the script as follows, note Initial Catalog has now been set to DatabaseName:

<Configuration ConfiguredType="Property" Path="\Package.Connections[ConnStaging].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=localhost;Initial Catalog=DatabaseName;Integrated Security=SSPI; Connection Timeout = 10</ConfiguredValue>
</Configuration>

Neilcse on Wed, 11 Mar 2015 20:15:00


Thanks Jm,

This is working perfectly. But, it is replacing the database name in xml file permanently.

e.g. 

....Catalog=DatabaseName;Integrated Security=SSPI;.....

After execution

.....Catalog=MySandBox;Integrated Security=SSPI;....

Now this is permanent can we do something to make it dynamic. because next time when I run I wont do anything as there is no databaseName in xml file, it is replaced by MySandBox


jmcmullen on Wed, 11 Mar 2015 21:07:21


Hey Neil

Great that it's working, powershell isn't so bad right? ;-)

The name remaining the same isn't a problem, the regex pattern ensure everything between "Catalog=" and ";" is replaced. So set a new database name in your batch file and it should travel right through to your xml file.

Give it a go.

Neilcse on Wed, 11 Mar 2015 21:21:12


OMG !!! this is working.. amazing, now I'll dig line by line to get to know more.

Thank you so much Jm, I am learning :)

Regards,

jmcmullen on Wed, 11 Mar 2015 21:24:19


No problem, glad to help!

Neilcse on Wed, 11 Mar 2015 21:26:08


Hi Jm,

Can you elaborate on this line please, how does it picks up database name only every time it checks, 

Foreach-Object {$_ -replace "Catalog=([A-Za-z0-9_]+)", $db} 

Neilcse on Wed, 11 Mar 2015 21:30:19


oh got it now I see it looks for "Catalog=" I was wondering how is this just picking up database name and renaming it to the current db :)

This is very powerful tool.   

one quick questions!

.bat files contents are nor power shell programming ?And .ps1 content is ?

jmcmullen on Wed, 11 Mar 2015 21:44:44


Yeah you got it. It looks for characters that match A-Za-z0-9_ after "Catalog=" and replaces them. It's known as a Regular Expression (Regex).

You're right, .bat files are not powershell programming. The contents of the .ps1 is powershell. That's why, when we call it, we do it via powershell.exe at the start of the command.

Hope that helps

Neilcse on Wed, 11 Mar 2015 21:46:39


Thanks yes I studied RegExp in SQL programming, what is the programming name given to the code written in batch file (.bat)?

jmcmullen on Wed, 11 Mar 2015 21:48:24


I'd just call it DOS :)

Neilcse on Thu, 12 Mar 2015 21:39:24


Thanks Jm,

Along with the database name I want to pass the Root variable also and use it in the script to build the complete path, I just want to add the file name in the script, path it should be passed from batch file, can you help me with this 2 modification

SET Root=%CD%\

powershell.exe -executionpolicy remotesigned -File %Root%config.ps1 -DatabaseName "%DatabaseName%" >> "%LogFile%"



Neil

PS. Just trying to avoid creating another thread, I'll mark this as answered soon.. advance apology for this :)


jmcmullen on Thu, 12 Mar 2015 21:55:48


hey Neil

Just to clarify - You want to pass the path and filename of the "xml.dtsconfig" into the powershell file yes? So there would be one extra variable in the .bat file for the xml.dtsconfig filename?

jmcmullen on Thu, 12 Mar 2015 22:20:26


OK I think I know what you need,

So there's 3 changes for this

1. Add a new variable for the dtsconfig filename to your batch file

SET DTSConfigFile=%RootFolder%xml.dtsconfig

2. Also in the batch file, change the powershell call to pass a new parameter (calling it DTSCfgFile) here

Powershell.exe -executionpolicy remotesigned -File C:\temp\UpdateConfigurationFile.ps1 -DatabaseName "%DatabaseName%" -DTSCfgFile %DTSConfigFile%

3. Add this new parameter in the .ps1 file and call it in place of the previous hard-coded reference 

param([String]$DatabaseName,[String]$DTSCfgFile)
[string]$db = 'Catalog='+ $DatabaseName
(Get-Content $DTSCfgFile) | 
Foreach-Object {$_ -replace "Catalog=([A-Za-z0-9_]+)", $db} | Set-Content $DTSCfgFile

That should do it!

Neilcse on Fri, 13 Mar 2015 03:02:25


Perfect with Jm Power, this power shell is giving the exact expected results.  You always read me perfectly :)

I am very thankful to you..

 

Neilcse on Fri, 13 Mar 2015 19:44:31


Hi Jm

Now I would like to add one more parameter, that is server name to be passed from batch file to .ps1 file to update the xml file with the server name passed from batch file

can you help me with this, I did the below but not working.

.Ps1 file

param([String]$DatabaseName,[String]$XML,[String]$DatabaseServer)
[string]$db = 'Catalog='+ $DatabaseName
[string]$dbs = 'Source='+ $DatabaseServer
(Get-Content $XML) | 
Foreach-Object {$_ -replace "Catalog=([A-Za-z0-9_]+)", $db} {$_ -replace "Source=([A-Za-z0-9_]+.)", $dbs}| Set-Content $XML

xml file

<Configuration ConfiguredType="Property" Path="\Package.Connections[ConnStaging].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=localhost;Initial Catalog=Stage;Integrated Security=SSPI; Connection Timeout = 10</ConfiguredValue>
</Configuration>

batch file update

SET DatabaseServer=localhost

powershell.exe -executionpolicy remotesigned -File %RootFolder%config_xml.ps1 -DatabaseName "%DatabaseName%" -XMLdtsConfigFile %XMLdtsConfigFile% -DatabaseServer %DatabaseServer% >> "%LogFile%"

NewThread4This

Prashanth Jayaram on Fri, 13 Mar 2015 20:06:11


Try this

param([String]$DatabaseName='ABC',[String]$XML='c:\web.config',[String]$DatabaseServer='SERVERNAME')
[string]$db = 'Catalog='+ $DatabaseName
[string]$dbs = 'Source='+ $DatabaseServer
(Get-Content $XML) | 
Foreach-Object {
$_ -replace "Catalog=([A-Za-z0-9_]+)", $db  -replace "Source=([A-Za-z0-9_]+)", $dbs } | Set-Content $XML

--Prashanth