April 24, 2008 at 9:56 am
I am new to ssis and ran into an issue that I was hoping to get some help.
Currently a ssis package exists where it loads multiple access databases (1 db per company) into a data warehouse. Everything is working fine until the version of the access database changes (a new field added for example) and I have to make slight changes to the datawarehouse. I edit the package and map the new field. I now have a situation where some of the access dbs are the old version and some are the new version. So now when the old db's are processed the package blows up because it can't find the field. Is there a good way to handle this?
Thanks for your help
April 24, 2008 at 1:02 pm
Not really, SSIS is very sensitive to schema changes because components store schema in inputs and outputs.
If you can identify the access database version somehow, you could store multiple versions of the ssis package and through some creative use of one package calling a sub package, call the approprate sub package for the access database you are working with at the time.
April 24, 2008 at 1:17 pm
Thanks for your response.
So for past db versions I will have to do a little manual work but for the future I will do the following:
-add a field in the access db to contain a db version number
-develop a ssis package for each db version
-create a table in the datawarehouse to hold access db version number and corresponding package version
-create a parent package that will query the data warehouse to determine the correct package to run
Thanks again. I will let you know how it turns out
April 24, 2008 at 1:29 pm
Make sure you put the field into a table that will not have any schema changes - the plan sounds good to me.
If you make that work, it would make a good article.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply