A SSIS Package Design Strategy – makes use of stored procedures as much as possible and also guarantees portability
I decided to design a SSIS package using Execute SQL Task for the final baseline data loading. Both of the target database and the “transformed” source data are on the same SQL server.
Since I cannot create stored procedures on the target database, AND I don’t want to hardcode the target database location in any of my scripts or in any of my stored procedures, I have to do:
1) Wherever I need to retrieve from or write to the target database, I use direct SQL scripts with EAMTarget as the data source. The actual target database will not be hardcoded anywhere, except in the EAMTarget Connection Manager.
2) wherever I only need to manipulate the data without “touching” the target database, I call stored procedures that reside in the ETL database. The ETL database is also where the “transformed” data and the final staging tables are.
This strategy makes use of stored procedures as much as possible and also guarantees that my SSIS package is portable among different environments.