How to use SSIS Config file with multiple connectionstring and how to pick the connectiong string depeding upon input value
-
Hi,
I have requirement in SSIS packages. I need to store multiple connection string in one config file with different names. Let SQLDev, SQLStg and SQLProd. User will pass a value to my ssis package like SQLDev or SQLStg or SQLProd.
How do i pick connection string from config file based on user input? If user passes SQLDev then i should pick the respective connection string.
Please help me....
-
You could store each connection string in its own variable (SQLDev, SQLProd, etc). Have a variable to hold the user's choice of environment (SetEnv, maybe). Then, have a separate variable to hold the connection string you wish to use.
So, call the new variable something generic like SQLMain, set this to be evaluated as expression and use the expression builder to set the connection string based on the value of the SetEnv variable.
Alternatively, hold the connection strings in a central config database along with an environment identifier. So, have a column for variable name (SQLConn) and a column for environment (Dev, Prod, etc). Hold the connection to this config database in one config file.
Then, on runtime, pass in the environment code, pull out the necessary variables for that environment from the db, and use a script task to populate the variables with their new values. This solution allows greater flexibility and removes hardcoded business logic from the package.
Hope this helps,
Tom
Life: it twists and turns like a twisty turny thing
-
Thanks a lot for the immediate response. I am not sure how to implement this logic. I am new to SSIS Packages. Please see my code below and correct me
1. I have declared 5 variables SQLMain, SQLDev, SQLStg, SQLProd and SetEnv in my package
2. i have variables in my config file
3. i am executing my Package like this.
Declare @cmd varchar(1000)
Select @cmd = 'dtexec.exe /f "C:\SSISPackages\Package.dtsx" /Set \Package.Variables[User::SetEnv].Value;SQLDev'
exec master..xp_cmdshell @cmd
What do you mean by "set this to be evaluated as expression and use the expression builder to set the connection string based on the value of the SetEnv variable."? How do we do this? I am not sure how to do this.
-
If you look at the properties of the SQLMain variable, you will see a property called EvaluateAsExpression. Set this to True.
Then, right below this, there is a property called Expression. Click in here, and then click on the ellipses button to open up the expression builder. Within here you can specify the logic to set the connection string you wish to use.
Something like:
UPPER(@[User::SetEnv]) == "PROD" ? @[User::SQLProd] : UPPER(@[User::SetEnv]) == "STG" ? @[User::SQLStg] : @[User::SQLDev]
(This will default to the DEV connection string).
This will solve your current problem in the short term, but you may want to consider a central config solution to manage variable values for the longer term.
Tom
Life: it twists and turns like a twisty turny thing
-
Thanks a lot. It helped me a lot. I am accessing SQLMain variable inside Script task and updating connection string value of connection manager.
How do we use same config file across packages?
Do we need to alwasy specify config file path while executing package(using dtexec.exe)?
-
Unfortunately, the location of the config file has to be hard coded somewhere. Either you specify it when you run the package, or you save the location of the file in each package, and have all packages pointing to the same config file.
Life: it twists and turns like a twisty turny thing
-
Hi,
I am getting another error while executing SSIS Package in SQL Server.
Declare @cmd varchar(1000)
set @cmd = 'dtexec.exe /f "E:\SSISPackages\SSISDeploy\ExportData.dtsx" /Set \Package.Variables[SourceDB].Value;AS400DEV /Set \Package.Variables[DestinationDB].Value;SQLDEV /CONF "C:\SSISDeploy\Environment.dtsConfig"'
exec master..xp_cmdshell @cmd
The error is below
The configuration file name "... development system path where config file is available .." is not valid. Check the configuration file name.
My package is not picking config file which i specified in command prompt. It was looking in my local system.
Surya
-
the config file needs to be where the packages can see it, either locally in the same location on each box that runs the package (remember that the package runs on the machine that calls it, not on the machine on which it is stored), or on a network share to which the package has access.
Life: it twists and turns like a twisty turny thing
-
I am getting the below error while executing my package from SQL Server 2005. Actually i am importing data from AS400 to SQL. Do i need to configure anything in SQL before i executed the script?
When i execute the same package from File system, it was working fine.
In Config file, I have specified connection string as mentioned below
Execution script:
Declare @cmd varchar(1000)
set @cmd = 'dtexec.exe /f "E:\SSISPackages\SSISDeploy\ExportData.dtsx" /Set \Package.Variables[SourceDB].Value;AS400DEV /Set \Package.Variables[DestinationDB].Value;SQLDev /CONF "E:\SSISPackages\SSISDeploy\Environment.dtsConfig"'
exec master..xp_cmdshell @cmd
Error is ...
Description: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.Odbc.OdbcConnection.Open()
at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)
Please help me
-
i think i missed the connection string for as400
Driver={Client Access ODBC Driver (32-bit)};System=AS400DEV;Uid=myUserID;Pwd=myPassword;
-
Hi,
I am trying to run SSIS package using DTEXEC command. While executing the config file is not picked up. Could you please let me know what was the issue here. I am getting the below warning message.
Cannot load the XML configuration file. The XML configuration file may be malformed or not valid.
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /DTS \MSDB_IMIDEV\Hiearchy_Master /SER Server /CONFIGFILE "\\100.100.xxx.xxx\imi-lz-prd\DNTs\HMSTR_Config.dtsconfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /CONSOLELOG NCOSGXMT
-
Hi,
I am trying to run SSIS package using DTEXEC command. While executing the config file is not picked up. Could you please let me know what was the issue here. I am getting the below warning message.
Cannot load the XML configuration file. The XML configuration file may be malformed or not valid.
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /DTS \MSDB_IMIDEV\Hiearchy_Master /SER Server /CONFIGFILE "\\100.100.xxx.xxx\imi-lz-prd\DNTs\HMSTR_Config.dtsconfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /CONSOLELOG NCOSGXMT
-
Hi,
I am trying to run SSIS package using DTEXEC command. While executing the config file is not picked up. Could you please let me know what was the issue here. I am getting the below warning message.
Cannot load the XML configuration file. The XML configuration file may be malformed or not valid.
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /DTS \MSDB_IMIDEV\Hiearchy_Master /SER Server /CONFIGFILE "\\100.100.xxx.xxx\imi-lz-prd\DNTs\HMSTR_Config.dtsconfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /CONSOLELOG NCOSGXMT
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply