Skip to main content

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 an SSIS package by leveraging the power of Biml. This approach is fast, easy to refresh, and the technique is extensible. The original walkthrough was a summary pointing readers to a longer post on the BI for Breakfast blog by Jeff Miller; that post is no longer available, so the technique is sketched here only in outline.