How to create dynamic Connection String in SSIS Package???

Question

Hi

I created OLEDB Source Connnection String for that Package,

i need Create OLEDB Destination Connection String Dynamically,

Same server name,but i need to add DW at the end of the Database

ex:

Source database name is Demo,

Destination database should come DemoDW.

i need to create Dynamic Destination Connection String..

any possible to Create 

Thanks in advance

Pandiyan

---

pandiyan

Answer 1

Its possible by adding a configuration inside the package for connectionstring property of the Destination database. At runtime it will automatically get the name of the database from the config file/table where you set it.

One thing you need to keep in mind that at time of designing package you've to point it to a existing local test database where you need to create the tables which will have the required columns to set the mapping inside SSIS data flow etc.  

---

Please Mark This As Answer if it solved your issue
Please Mark This As Helpful if it helps to solve your issue
Visakh
----------------------------
My MSDN Page
My Personal Blog
My Facebook Page

Answer 2

Hello Pandiyan:

You can use a Execute SQL Task with this command (SELECT DB_NAME() +'DW'+ '.[dbo].[table]' AS Expr1)

--Where "Table" is the name of your destination Table. (for example result: demoDW.[dbo].[DimDate])

You can save this value into a variable @[User::DB]

You can use this variable in the OLEDB Destination Editor using Table Name or view name variable.

You can work with the variable for transform the DB and Table Destination.

Best Regards

Claudio

Answer 3

Hi Pandiyanpvp,

According to your description, the OLEDB Destination Connection String should be dynamically based on the OLEDB Source Connection String. They are all the same, except adding “DW” at the end of the Database of OLEDB Source Connection String in OLEDB Destination Connection.

To achieve this requirement, we can create a variable with the Database name of OLEDB Source Connection String, then use expression to control the OLEDB Source and OLEDB Destination Connection Strings. For more details, please refer to the following steps:

1. Create a variable named Source with the Database name of OLEDB Source Connection String as value.
   
2. Click the OLEDB Source Connection Manager to navigate to the Properties window.
   
3. Click “…” next to Expressions to add a ConnectionString Property with the expression like below:
   "Data Source=.;Initial Catalog="+@[User::Source] + ";Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"
   
4. Click the OLEDB Destination Connection Manager to navigate to the Properties window.
   
5. Click “…” next to Expressions to add a ConnectionString Property with the expression like below:
   "Data Source=.;Initial Catalog="+ @[User::Source] + "DB"+";Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"

If there are any other questions, please feel free to ask.

Thanks,
Katherine Xiong

---

Katherine Xiong
TechNet Community Support