I need help for Connection string:
Requirement: When we create SSIS Pacakge using Businessinteligence studio.Each Source and Destination or whatever we using the Control required DB Connection.
we connect theDB server and Database Table through manaully .Instead of Manual i need dynamic Global varible for Connection String .How to achieve this connection string.
because suppose we create SSIS Package in Developement Server Latter We change the Server from Developement to Another Testing Server . at that time we dont requierd for changing manulay.any one pls reply me.
Same as in Dotnet we give configiration XML file .we gave the Connection strng. how to in SSIS we do?
Thanks & Regards
if you would like to use your defined connections in your package dynamically
- define for each connection parameter which should be dynamic (e.g. servername, login, pw) a variable which scope is valid for the package (or part of the package)
- define the connection string as you would do it normally (means fill in everything you need to connect. This is used during validation)
- define in the expressions all those parameters which are dynamically filled by the variables e.g. Servername, InitialCatalog
- use the TRIM function ,e.g. Servername should be defined as TRIM( @User::Servername] )
Maybe this helps you a little bit
Create 1 OLEDB connection manager
Create 4 variables (oledbservername, oledbinitialcatalog, oledbusername, oledbpassword)
Give those variables a value (manually or from a config file)
Create 1 script task. Put these variables as readonly input
Write the following code inside the task:
Dim conns As Connections = Dts.Connections
Dim cmOleDb As ConnectionManager = conns("OleDB Connection")
I got this to work and tested my connection from within the script and it worked just fine. However, when I try to use the connection in the next task (an Execute SQL Task), I get an error saying that the connection was not made. Any ideas? Do I need to do anything special when I create the OLE DB Connection Manager that I'm using?
P.S. I'm a newbie with this...