Home > ETL Best Practices > A SSIS Package Design Strategy – makes use of stored procedures as much as possible and also guarantees portability

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.

image

Categories: ETL Best Practices
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: