Creating SSIS Packages from a Stored Procedure - via BIML

Did you ever want to perform a quick ETL task, such as moving the contents of a table between SQL servers? The options for completing this task include:

• Using Linked Servers

• Generating a SQL script with INSERT statements

• Running the Import/Export Wizard

• Using ‘copy and paste’ with the table objects in Management Studio

• Manually building an SSIS package

Since each of these approaches have their own drawbacks, here is another option to consider: Use a stored procedure to dynamically build a SSIS package by leveraging the power of BIML. This approach is fast, easy to refresh, and the technique extensible (more on this later):

Step 1 – Execute a stored procedure providing server, database, and table names (the stored procedure code can be found at the end of this post):

Step 2 – Click on the result set (link) in SSMS:

(The BIML file will open in a new window in SSMS)

Step 3 – Open Visual Studio, right click on the ‘SSIS Packages’ container, and choose ‘Add New BIML File’ (Note: BIDS Helper must be installed for this option to appear in SSIS). If you are new to BIML you may want to click here for details on this powerful feature in BIDS Helper.

Step 4 – Copy and paste the above BIML (XML) script into the new BIML file created in Visual Studio:

Step 5 – Save the BIML file, right click on it, and choose ‘Generate SSIS Packages’:

A ready-to-run package will then be created under the ‘SSIS Packages’ folder in Visual Studio, with the BIML defined Control Flow, Data Flow, and Connection Managers:

Now someone might ask, of all the initial options presented at the start of this post, why do you like the BIML approach best? So here you go…

1. It’s very fast (perhaps 30 seconds start to finish)

2. If the columns or data types in in the target table changes, you only need to re-run from ‘Step 5’ above, which will fully rebuild the SSIS package

3. The logic is driven by a stored procedure, and powered by BIML. This provides reusability, as well as extensibility by just adding BIML (SSIS) tags. Tip: If you want some help authoring ETL packages with BIML tags, download a free trial of Mist from Varigence, use the SSIS package import feature, and review the resulting BIML mark-up.

To run the above walkthrough, you’ll need SQL Server Management Studio, BIDS or SSDT (with BIDS Helper installed), and the stored procedure which follows