Recently I had to stage about 150 tables from a source database. I like creating SSIS packages, but not 150 times the same boring stage package. Is there an alternative?
 |
| Simplified version of my staging package (times 150) |
Solution
You can use BIML to create an SSIS package and when you combine that with some .Net code, you can easily repeat that for all you tables. For this example I want to copy the data from all database tables on my source server to my staging server. The tables are already created on my staging server and they have the exact same definition as my source server.
1) Install BIDS Helper
First install BIDS Helper which is an add-on for BIDS/SSDT. Then start BIDS/SSDT and create/open an SSIS project. Now you can right click the project and choose Add New Biml File. This will add a .biml file in the Miscellaneous folder.
 |
| Add New Biml File |
2) BIML Script
This is the basic BIML Script that creates one staging package for the color table. It has a truncate table command in an Execute SQL Task and a Data Flow Task to fill the table. See this for more examples.
Truncate table Color SELECT Code, Name FROM Color
Now you can right click the BIML Script and generate the SSIS color staging package. It will automatically appear in the SSIS project.
 |
| Right Click and choose Generate SSIS packages |
3) Adding .Net code
By adding some .Net code to your BIML code, you can create a more dynamic script. For this example I will use C# code, but you can translate it to VB.Net if you prefer that language. You can add .Net code between <# and #>, but note that adding that to BIML code could mess up the formatting within Visual Studio. It's even worse to show it on a webpage. So see screenshot and then download the code.
 |
| Screenshot, because the mixed BIML and C# code isn't readable in HTML |
Download Biml Script here.
Now you can right click the BIML Script and generate the SSIS staging packages for all source tables.
4) Master package
Now you need a master package for all the new staging packages. You can use a Foreach Loop in your master package to loop through all child packages. Or you can use BIML to create the master package:
 |
| Master package example 1: loop through SSISDB |
Download Biml Script here
 |
| Master package 2: loop through project folder on filesystem |
Download Biml Script here
Also see: An introduction to BIML
Hello,
ReplyDeleteI've got a few questions:
1. In your second part, is it possible that you forgot to close the input (line 28)?
2. When I fixed it, I'm getting a warning regarding to the first line:
Someting like the global element 'http://schemas.varigence.com/biml.xsd:Biml' wasn't declared.
When I'm using the BIML.xsd in the zip file which is posted here http://bidshelper.codeplex.com/wikipage?title=Manually%20Configuring%20Biml%20Package%20Generator
I'm getting more than 100 warnings e.g. that the global element 'http://schemas.varigence.com/biml.xsd:Biml' has been declared.
Generating the package is not possible with these warnings. Do you have an idea how I can fix my problem?
Regards, Chris
It is there, but the javascript viewer messed up the output:
Delete<input outputpathname="SQL - Truncate Color.Output" />
Will try to fix it or make a download available.
Which version of BIDSHELPER are you using and for which SSIS version? I'm using BIDSHELPER 1.6.3.0 and SSIS 2012.
This comment has been removed by a blog administrator.
ReplyDeleteYou don't need specify <# on each c# line
ReplyDeleteYou can make blocks like
<#
string ..="";
foreach (file in files)
{ #>
Cleans up the code
Thanks. You're correct. Will change that soon.
Delete