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...