How To Set and Use Variables in SSIS Execute SQL Task

Needed this today…

How To Set and Use Variables in SSIS Execute SQL Task

We are doing some ETL work into a data warehouse for a client. One of the dimensions has a “smart key” value for a date dimension. So, we figured we could forego looking up the date key to populate an associated fact with and instead transform the incoming date to the smart key value. This worked great…except when it didn’t! Specifically, when we ran across a date that exceeded the data in the date dimension table. Yuck!

The solution was to do an initial lookup of the max date in the date dimension and capture that into a variable. Then, in the derived column where we were doing the smart key transformation, we would also check to see if the date exceeded the max in the dimension. If it did, we would set it to the default key value.

Happy coding!


Leave a Reply

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

You are commenting using your 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 )

Connecting to %s