Click here to Skip to main content
Rate this: bad
good
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 2:05am
Rate this: bad
good
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.
--Amit
  Permalink  
Rate this: bad
good
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.
 
PS:
When I try Expression Editor, it always show me error, Does somebody can describe how to do?
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 580
1 ashok rathod 159
2 CPallini 140
3 CHill60 130
4 BillWoodruff 125
0 Sergey Alexandrovich Kryukov 9,543
1 OriginalGriff 6,984
2 CPallini 5,820
3 George Jonsson 4,017
4 Gihan Liyanage 3,266


Advertise | Privacy | Mobile
Web01 | 2.8.140926.1 | Last Updated 17 Dec 2012
Copyright © CodeProject, 1999-2014
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