Passing database names to SSIS stored procedures
In the rare cases when we use dynamic SQL and want to use a database name in our code, we are better off avoiding hard-coding them. Unfortunately, I could not find an easy way to access a connection manager’s database name and on my current project the catalog name is not in the SSIS configurations XML file. Therefore, I had to resort to a little trick to pull the database name out and pass it to a stored procedure. In brief we can do the following:
1. Create a user variable database_name
2. Create an Execute SQL Task using the connection manager we want to get the database name from, which does:
SELECT db_name() AS database_name
3. Map the Single Row result set to our database_name variable
4. Place the task created in the previous step before any components which would be using the variable.
5. Pass the variable to our dynamic SQL stored procedure
There we go – a stored procedure configured in the SSIS package configurations – a bit better than just hard-coding the name.