July 6, 2009 at 7:41 am
Hello All,
I'm currently working on project in which I'm deploying 50+ SSIS packages as part of a DW solution. It's proved to be quite an effort to get the packages deployed in our test environment. I was able to get them to work, but that required me to go in and refresh most of the meta data on the packages. This is somewhat of a problem, as I will not have access to the Production environment. Just wondering if some of you SSIS deployment veterans have any advice on getting packages into new environments with minimal effort.
Also, another question that I have is connection strings for new environments. Is there an easy and dynamic way to control these connection strings?
Thanks in advance.
bozworth
July 6, 2009 at 7:54 am
SSIS has the ability to upload configuration information on start up. There are several you can do this. This would allow you to make your packages more dynamic and avoid the problems you just described.
Check Books Online for Package Configurations.
Also do a search on this site. There have been multiple posts and articles related to Package Configuration.
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]
July 6, 2009 at 7:55 am
We haven't moved to a production environment yet, but what I am curious about is your comment about modifying meta-data in your packages. What meta-data did you have to modify?
July 6, 2009 at 8:02 am
well, it was basically telling me that the meta data was out of sync. I only needed to open the package and close it, and the error was resolved. We didn't have this error in Dev.
July 6, 2009 at 8:07 am
ah, found this nifty link in relation to the dynamic connections in SSIS
http://qa.sqlservercentral.com/articles/Video/64262/
Just curious, does anyone use the SQL table for storing their configurations?
July 6, 2009 at 8:18 am
bozworth hollinz (7/6/2009)
ah, found this nifty link in relation to the dynamic connections in SSIShttp://qa.sqlservercentral.com/articles/Video/64262/
Just curious, does anyone use the SQL table for storing their configurations?
We use a combination file and database table for storing configuration values.
We use the file for storing configuration values that are common to all packages. We use the database table for storing configuration values that vary from package to package. The connection string for this table is one of the items that is in the common configuration file.
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]
July 6, 2009 at 9:10 am
bozworth hollinz (7/6/2009)
ah, found this nifty link in relation to the dynamic connections in SSIShttp://qa.sqlservercentral.com/articles/Video/64262/
Just curious, does anyone use the SQL table for storing their configurations?
We also use a configuration table in SQL Server to hold our SSIS package configuration information.
July 8, 2009 at 9:51 am
Metadata being out of date
I believe this happens your package looks at a table and retrieves a column that is a varchar(2), then close the package change the table definition and open the package.
This would make me sit up and say "is my production schema different to my test schema" followed by "has anyone changed table or view definitions recently"
Package configs are groovy. If done right it will be seemless
Ells.
😎
July 9, 2009 at 6:56 am
Package configurations are a godsend.
I actually made a pkg template so all my connections and pkg config, variables and logging settings will be consistent across all pkgs. So if a 2nd developer
comes along they wont make their own connections and so forth and
all will run smoothly. Has worked great in the past. I store my config setting in a sql table and no problems. Seems everyone has an opinion on sql table vs xml....but sql table works fine for me.
July 9, 2009 at 1:43 pm
I agree that package configs are really useful. However if you are using a setup similar to the ones described above - XML file to provide the connection string to your db and then a SQL Server table there after - you do need to make sure that as part of your deployment you ensure the XML file is in the expected location with the correct connection string across all the servers you will be deploying to.
I have recently been sorting out a one click deployment solution for a client of mine and I tend to use a combination of SQLCMD scripts and batch files with variables being passed between the two. For my SSIS deployment I pass 2 variables into a batch file (source location of packages and target location of packages). I then iterate through all the folders in the directory and deploy to the MSDB database or the file system using DTUTIL.exe. I also believe there is a validate switch as part of DTUTIL which attempt to validate the packages for you. You can then write to the log if any fail validation.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply