ssis replace variables in sql code

Using an SSIS package, is it possible to load sql code that contains variables in it, replace those variables and run the code without saving it(similarly to DTS 2000 packages)?

For example, I want to run Code.sql, which contains the following:

SELECT *
INTO XXDBXX.dbo.Table2    
FROM XXREADDBXX.dbo.Table1

I was planning on creating a script to replace the “XX” strings with variables I set up using SSIS.

So, here are the steps I want to do:

Run script to replace sql file variables.
Run SQL code
SQL code finishes running, and code still retains its variables.

Please let me know if anything needs clarification.

Answer

create a variable in SSIS @SQL (User::SQL)

Script task to load template sql from file & replace variables with required values. Use C# String.Replace();

Execute task to use @SQL (SQLSourceType = Variable)

Advertisement

Answer