USING BI Studio how to create Dynamic Connection String in SSIS Package

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

M.Jeyakumar

Answer this question

USING BI Studio how to create Dynamic Connection String in SSIS Package

Lazureykis Pavel

How to give values to the variables (oledbservername, oledbinitialcatalog, oledbusername, oledbpassword) from config file.
pxcosis
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

Thanks!

Laurie

P.S. I'm a newbie with this...
Gabriel Rdz
Right click the main control flow and select Configurations menu, follow the wizard to select configuration of you choice (registry, XML file, environment, SQL server) and the properties to configure.
Mbccs
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

Norbert
Andres Orozco
hi michael,

I have created a xml configuration file using Package Configuration.

but here i have a small problem, how do i give connections using the generated file for the oledb source and destination

thanks in advance

regards,

Bharathvv
David d48701

Books Online - Creating Package Configurations
JessicaK
That was pretty general. Can you more specific This seems to be a hard task for many to complete, so the more specific you can be, the better.

Thanks!
mimarko

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")

cmOleDb.Properties("ServerName").SetValue(cmOleDb, Dts.Variables("OleDBServerName").Value.ToString())

cmOleDb.Properties("InitialCatalog").SetValue(cmOleDb, Dts.Variables("OleDBInitialCatalog").Value.ToString())

cmOleDb.Properties("UserName").SetValue(cmOleDb, Dts.Variables("OleDBUser_Name").Value.ToString())

cmOleDb.Properties("Password").SetValue(cmOleDb, Dts.Variables("OleDBPass_word").Value.ToString())
USING BI Studio how to create Dynamic Connection String in SSIS Package

Answer this question