April 14, 2011 at 2:45 pm
Hi - I'm trying to deploy an SSIS package from dev to production. I've set up a configuration file to a SQL Store in dev, and I've chosen the initial catalog (aka database name) and servername as the variables to be stored. I've moved the resulting SSIS Configuration table to our production server and changed the values to the production values. However, when my DBA deploys the package in production and runs it, it seems to be looking for its configuration values in the dev version of the SSISConfig database. I've found this inscrutable (to me) sentence on Microsoft's site (http://msdn.microsoft.com/en-us/library/cc895212.aspx):
"A drawback of this configuration type is that it relies on an SSIS connection manager to get access to the configuration table, but does not have built-in support for updating its connection string. In other words, the connection string that points to the configuration table is hard-coded inside of the package, and you have to take extra steps in order to update it (for example, through an extra package configuration placed at the top of the Package Configurations Organizer, or in SQL Server 2008, by using the /CONN option of DTExec). "
Is this my problem? And if it is, what are they talking about? How do I resolve this?
Thanks!
April 14, 2011 at 3:02 pm
I would not say that it is your problem per se, but it is a problem.
Basically, say you are making the connection to the dev configuration table using a connection called SSISConfigurations in the Connection Managers. That is the connection you need to point towards the prod version of the database.table that stores the production configurations.
I supposed that could be considered a drawback for this type of configuration deployment. It certainly is an extra step. The benefit is of course that you are not having to manage config files external to the database and the configurations are backed up with your database. Hope that helps.
April 14, 2011 at 3:11 pm
So can I just include the servername of the production server where SSISConfig as another variable in my configuration? Is that what they mean?
Thanks!
April 14, 2011 at 3:19 pm
No, before the package even gets to the point of reading the config values from the configuration database, it has to know on what server.database that information resides. So adding another row in your configuration table for the production server will not help.
When you established your package configurations using the SQL storage method, you had to choose a connection with which to configure that. It is that connection that must be changed to point to the production server.database. By doing that, you have told to the package where to go to find its configuration information when it executes. Hope that helps. Thanks.
April 14, 2011 at 3:25 pm
OK, my apologies if I'm being dense. This is my first experience with SSIS deployments. So - do I change the connection in BIDS to point to our production server, then create a deployment manifest and give it to my DBA?
Thank you!
April 14, 2011 at 3:28 pm
No apology necessary, these configurations can be confusing the first few times. Ask me how I know.
But yes, you can either change the connection and hand it off to your DBA or instruct him/her to do that prior to deployment to the prod server. Thanks.
April 15, 2011 at 8:28 am
It works! Thank you SO much! 😀
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply