April 24, 2008 at 11:37 am
Having a hard time trying to implement this......you guys helped me get going down the right road but now I'm getting stuck...
For our SSIS Packages, we have 3 database environments we'll be going through: Dev, QA and Prod.
We use a Cluster so the best option for making a package portable through each environment is to use the "SQL Server" option with Package Configurations.
What I am having a hard time with is how to implement this within my package.
Let's say I have a package with a connection to my Development database in it. I want to switch that being based off variables from the Package Configuration. This is the spot where I can't figure out what to do next nor can I find any good guide on how to do it via the web (all the ones I found seem to skip vital pieces of information that I think the author took for granted)
Some of most trying question at the moment.....
I created this package with my development connection....I went into Package Configuration, used the SQL Server option, used my Development connection, created the necessary table and exported all the variables available for my connection. So, I am assuming that no matter what information is entered in my Devl connection, as soon as the package loads, it is going to be overwritten with whatever information the package pulls from this SSIS Configuration table, right? If so, how does the Package know what database to connect to query that SSIS Configuration table and how does that become dynamic as the package migrates?
Thanks in advance
April 25, 2008 at 3:27 am
When you set up SSIS package configurations, you get an option to either specify the configuration settings directly or to store them in an enivironment variable.
Kind Regards, Will
April 25, 2008 at 4:03 am
Hi,
Chances are you'll want two connection managers - one to manage your connection to the source data, and the other to manage your connection to the configuration table/s.
It sounds like you're already doing the right thing... you have 2 choices when you configure your connection managers dynamically.
option 1 is to use an expression and SSIS variables to set the connection manager properties (you set the variable values by a configuration). This is the best practice for a number of reasons.
option 2 is to configure the connection managers directly.
It sounds like where you're coming up against an issue is how to set the configuration database in the first place. You can either use environment variables or quite popular is to use an xml file saved to the same relative path in all your environments with different values according to which environment it is in.
Kindest Regards,
Frank Bazan
April 25, 2008 at 5:47 am
I still have the concern about the configuration connection....
Is that a static connection that is always pointing to the same database or does it become dynamic somehow.
Let me explain my expectations getting into these Package Configurations and maybe that will help.
I was basically thinking that when I initially wrote the package in the Devl database, it'd just automatically connect to that same database, query the configurations table and get the information back out and use it. As I moved the package to the QA and Prod environments (by doing a Save a Copy As.... and selecting the appropriate servers) it'd begin querying those databases' Configurations tables.
Right now, I have a package with a connection manager that is, by default, hooked up to our Devl database and I have done testing with the Package Configurations and have proved that by changing the values in the database it does, in fact, change the properties of the connection so I really think that I've got that going now. I just need help wrapping my mind around how this package will "adapt" as I move it to other databases.
May 2, 2008 at 12:44 pm
anybody have an answer to my question about the connection used to pull Package Configurations.
Have researched everything I can find about using SQL tables for the Configuration but they all just say how to set them up.....not really much about migrating them and how that works.
Thanks
September 2, 2010 at 3:06 am
Hi,
I'm also facing the same problem .
Scenario :
i have created 50 packages in my Dev Server now i want to move the packages to Prod Server . usually i will use XML Configuration file to acheive this . but now my client requirement is they need to capture the connection information in the table . so they will change the server name in the table it should refer the package accordingly.
for this requirement i used 'Package configuration with Sql Server' option and stored the connection information in table.now i like to know how to proceed by connecting the Config table to the packages.
if you know the way to acheive this task . please help me to solve this .
Thanks in Advance!
Sabarinathan.C
September 2, 2010 at 8:11 am
Putts (5/2/2008)
anybody have an answer to my question about the connection used to pull Package Configurations.Have researched everything I can find about using SQL tables for the Configuration but they all just say how to set them up.....not really much about migrating them and how that works.
Thanks
The Package Configuration uses a connection manager that points to a server and database. That connection string is hard coded into the dtsx file. So if you move the package to a new server and change nothing, it will still point back to the original server for package config.
You could edit the connection manager used by the package configuration and change the server name to (local), so as long as you have copied the package config database to the new location, it will use that copy of it. The downside of this is that the package won't work on a development workstation which isn't running SQL Server.
I had a similar issue in setting up a DR environment. All of our production packages had the prod server name hardcoded in them. What I did was write a small SSIS Script Task that looped through each package in msdb on our DR server, and using the DTS object model, replaced the prod server name with the DR server name in the connection strings within the packages. This approach worked well in my case.
September 2, 2010 at 8:23 am
Instead of referening a servername why not reference "(local)" which will reference the local SQL server each time.
CEWII
September 2, 2010 at 8:48 am
Hi Ed,
Thanks For your quick reply ..
i like to ask can you send me any word document attaching the step by step process u followed to acheive the task. i know my request is Hard for you . but past 2-3 days i'm breaking my head to find out the solution but still i couldn't acheive this . my client is forcing me to finish up the task ASAP.
but i like to share the Process which i Followed .
Steps:
1. i created the package in the BIDS environment
2. i created the Package configuration using Sql Server options and selected the Server properties as Connection String and stored in the 2 seperate ConfigurationFilter for Source and Destination Server.
Eg: 2.1 SRC_Server - SISCHNAPP01
2.2 DEST_SERVER - SISCHNTST01
after creating the config File all my package information will be store in the [SSIS Config] table .
upto the above steps i'm very much clear.
after storing the package Information in table i like to know how this table connection string values can be refered to the package dynamically .
Eg: suppose if i change the connection string value in the [SSIS_Config] table from DEST_SERVER - SISCHNTST01 to SISCHNTST02 the package should automatically refer to the newly changed value(SISCHNTST02).
how can we acheive this. by understanding my requirement if you able to give solution is welcome or else send me your logic how to acheive this .
pls without mind send me step by step process how u acheived the task .
Regards,
Sabarinathan
September 2, 2010 at 8:52 am
Hi Cew,
but problem here is sometimes client may change the source and Destination server names in the table .so, that time how the Local Server will work for both the Connections .
because we we having more than 6 servers across all servers we replicated the Project. so for this purpose only the client requirement is like this .
Reply
Regards,
Sabarinathan
September 2, 2010 at 9:01 am
Hi Ed,
if anybody knows the solution for my requirement without hesitation please send me the step by step documents to the below mentioned mail ID.
Mail ID : sabarichandru@gmail.com
Regards,
Sabarinathan
September 2, 2010 at 9:01 am
Putts (4/24/2008)
... how does the Package know what database to connect to query that SSIS Configuration table and how does that become dynamic as the package migrates?
Been there, done that.
I would use a configuration file, just for that configuration information. All other configuration data would be in the database pointed to by the configuration file.
You could do the same thing with an environment variable. I don't like using them and some admins won't let you use them.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 2, 2010 at 9:03 am
I'm not sure I understand what you are asking....
If you have saved the connection string property of your connection managers in SSIS Configurations, and you edit that value in the SQL table, then the next time the package is executed, it will use the updated connection string. There is nothing additional needed.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply