CODEWITHSUNDEEP
ssis
someguy198650
someguy198650

Reputation: 45

Execute SQL files from SSIS package

I have a lot of *.sql script files and i want to incorporate them to my SSIS package.

Is it possible to use these *.sql files as the source of Select/Insert/Update/Create/Alter/Drop statements on my package?

The goal is to dynamically alter the scripts without modifying the package.

Thanks

Upvotes: 4

Views: 20717

Answers (2)

cairnz
cairnz

Reputation: 3957

You can use Execute SQL Task where the statements come from these sql files. For example if you loop all your files of *.sql type in a foreach loop, then use Execute SQL task on the file connection you're looping on, you should be able to run all these SQL files without any problems. (Change SQLSourceType in the General pane of the Execute SQL Task editor)

Upvotes: 5

grapefruitmoon
grapefruitmoon

Reputation: 3008

Rather than using .sql files, you can set SQL Statements to come from Variables. You can set these variables from Package Configurations, which can be in the form of XML files. These XML files can store the SQL, and you can alter them without having to edit or redeploy your packages, which should give you the flexibility you're looking for. This link tells you how to set this up.

Upvotes: 0

Related Questions