Use FileConnection as SQLSourceType in Execute SQL Task in SSIS package

When using an Execute SQL Task in a SSIS package, usually we do:

1.  Create a new Integration Services Project in Microsoft Visual Studio;

2.  Drag the Execute SQL Task from Toolbox, then set up its Connection either through Data Sources, Data Source Views or directly create a new connection in the Connection Managers, simple.

Let's say this Execute SQL Task will run some TSQL queries, for example, to create a few tables with PKs and indexes for the next Control Flow task. If the query is relatively short, say  less than 20 lines, we usually just write the query right in the SQLStatement field, simple and quick. However, we if the query we need to run is long, although we can still copy/paste to the SQLStatement field, but it will take some time to search something by scroll up and down. Another thought is to use a stored procedure, however the better solution here is to use a File Connection.

To use a File Connection, just right-click on the Connection Managers area, then choose "New File Connection...".

then choose the DDL query file - CreateTables.sql.

The next step is to config the Execute SQL Task, double-click on it:

Notice that, for SQL SourceType, we choose "File connection", and for FileConncetion, we choose the query file.

Happy SSISing.