Click here to Skip to main content
12,760,996 members (27,246 online)
Rate this:
Please Sign up or sign in to vote.
See more: SQL-Server
SSIS how to set connection string dynamically from a config file
Posted 13-Sep-12 3:05am
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Create your variables in the Variable tab, mine are ProjectServer (String), DBName (String)

Create your DB Connection Object connecting directly to the actual Server and Database...Test your Connection

Right Click on the Connection Object and Select Properties
In the Properties Window Select Expressions by clicking the grey box with 3 dots ...
Select ConnectionString from the dropdown in the 1st column, then click the grey box with 3 dots.

In the Expression Editor that opens enter the following: "Data Source=" + @[User::ProjectServer] + ";Initial Catalog=FinancialProcessing;Integrated Security=True;"

Refer the links below:
Dynamic Connection Strings in SSIS[^]
Dynamic Database Connection using SSIS ForEach Loop Container [^]

Hope it helps.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

My solution is:
insert a [Script Task], and in script task editor you can get connection manager from Dts as follow:
ConnectionManager cm = Dts.Connections["YOUR_CONNECTION_NAME"] as ConnectionManager;
then you can change connection string dynamic as follow:
cm.ConnectionString="Provider=XXX;Data Source=XXX;User ID=XXX;Password=XXX;Initial Catalog=XXX";

Hope useful for you.

When I try Expression Editor, it always show me error, Does somebody can describe how to do?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170217.1 | Last Updated 17 Dec 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100