Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Variable As OLE DB Connection. Expand / Collapse
Author
Message
Posted Friday, April 4, 2014 12:12 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:30 PM
Points: 216, Visits: 788
Hi Guys,

Need Help,I want to know what is the best way to deal this kinda Problem or approach.

I am creating almost 30 SSIS Packages, some of them or simple and some of them are really tough (SCD and Other Transformations),
First I am gonna run SSIS Packages on Test Server then Dev Server and then Finally Production.
Its to much for me to every time change OLE DB Connection, and recreate SCD mapping and everything.

I want to know If I can use OLEDB Connection as a VARIABLE, Instead of change all OLEDB Connection, just change one VARIABLE.
If I create Variable as a CONNECTIONString, I am having a error something like " It must consist of one or more components of the
form X = Y, separated by semicolons."

Any Help would be great appreciate or any other easy solution.

Thank You.
Post #1558609
Posted Tuesday, April 8, 2014 10:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:05 PM
Points: 300, Visits: 810
If I understand you correctly, Yes, we do this.

I have a local connection variable that resolves this way using expression

Data Source=YOURSERVER;Initial Catalog=YOURDATABASE;Provider=SQLNCLI.1;Integrated Security=SSPI;PacketSize=32767

Expression
"Data Source=" + @[User::cfgDWServerName] + ";Initial Catalog=" + @[User::cfgDWDatabaseName] + ";Provider=SQLNCLI.1;Integrated Security=SSPI;PacketSize=32767"


YOURSERVER and YOURDATABASE are the cfgDWServerName, cfgDWDatabaseName variables that I use configurations to set. So it's an easy move from dev to prod or sales or demo or whatever, I just use Set Variables tab in the job step to point to correct server/database.

There are many ways to do configurations and I only know this way so hope you can adapt for the method you use.

In dev, all I do is check variables to see that my connection is dev.

Hope this helps!
Post #1559568
Posted Tuesday, April 8, 2014 12:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:46 AM
Points: 13,622, Visits: 10,513
rocky_498 (4/4/2014)
Hi Guys,

Need Help,I want to know what is the best way to deal this kinda Problem or approach.

I am creating almost 30 SSIS Packages, some of them or simple and some of them are really tough (SCD and Other Transformations),
First I am gonna run SSIS Packages on Test Server then Dev Server and then Finally Production.
Its to much for me to every time change OLE DB Connection, and recreate SCD mapping and everything.

I want to know If I can use OLEDB Connection as a VARIABLE, Instead of change all OLEDB Connection, just change one VARIABLE.
If I create Variable as a CONNECTIONString, I am having a error something like " It must consist of one or more components of the
form X = Y, separated by semicolons."

Any Help would be great appreciate or any other easy solution.

Thank You.


The proper way of doing this is using configurations:

Understanding Integration Services Package Configurations




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1559646
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse