April 4, 2014 at 12:12 pm
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.
April 8, 2014 at 10:20 am
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!
April 8, 2014 at 12:31 pm
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
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply