ssis replace variables in sql code

Question

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.

john McTighe · Accepted Answer · 2015-10-06 14:07:07Z

1

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)

answered Oct 6, 2015 at 14:07

john McTighe

1,2817 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brenner · Accepted Answer · 2020-06-10 18:11:36Z

0

Create a variable in SSIS

https://www.red-gate.com/simple-talk/sql/ssis/ssis-basics-introducing-variables/

You can also run SQL code and then replace parameters such as:

EXEC PROC XXDBX ?,?

The question marks being your parameter or variable created above.

answered Jun 10, 2020 at 18:11

Brenner

6551 gold badge6 silver badges20 bronze badges

Collectives™ on Stack Overflow

ssis replace variables in sql code

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related