The case is about importing flat files (CSV’s) without the necessity of metadata. Because BIML always checks if the tables are accessible before creating the packages, the first step is to create the tables with BIML and the second step is to create the SSIS packages for transporting the data.
Because of the size of the solution I’ve created two separate solutions, one for creating the tables and a second for creating the SSSI packages. You can click on the link to go to the other solution (which I will deploy later this month).
- Creating tables in the database
- Packages to fill this database
Because of the size of the solution I’ve created two separate solutions, one for creating the tables and a second for creating the SSSI packages. You can click on the link to go to the other solution (which I will deploy later this month).
Solution - Creating tables with BIML
In this solution, we create a BIML that is going to create the tables and all the columns are defined as strings.
We have to create two BIML scripts, the first script for defining the table definition and the second for creating the actual package.
We have to create two BIML scripts, the first script for defining the table definition and the second for creating the actual package.
1) CSV files
For this example we are using two CSV files (age and sickleave) which are comma separated and have columnnames on the first row. These columnnames will be used in the create table statement
2) Tabledefinitions
The first biml is called “1_TableDefinitions.biml”
In this biml we define the path were the CSV files are located, an array with the names of the csv files and also some string which we going to use further in the code.
We use two “foreach loops”, the first one loops trough the array with files and the second one loops trough the actual file (to extract the column names).
Normally (without the loop) the code should look like this:
the drop map |
content csv file |
2) Tabledefinitions
The first biml is called “1_TableDefinitions.biml”
In this biml we define the path were the CSV files are located, an array with the names of the csv files and also some string which we going to use further in the code.
We use two “foreach loops”, the first one loops trough the array with files and the second one loops trough the actual file (to extract the column names).
Normally (without the loop) the code should look like this:
< tables> <columns> <column datatype="Int32" identityincrement="1" identityseed="1" name="AgeID"> <column datatype="Int32" name="AgeFrom"> <column name="AgeTo"> <column datatype="String" length="255" name="AgeCategoryEmployee"> </columns> </columns> </tables>
Default BIML uses INT as an default datatype, in this case we use a string.
Now we add the loop in place and the complete code looks like this
3) CreateTable
We’ve created 2 bimls, 1_TableDefinitions.biml and 2_CreateTables.biml. Now comes the important part (I’m using Biml Express) for generating the package. First we click on 1_TableDefinitions and secondly on and 2_CreateTables, if you have selected the 2 biml scripts you click with your right mouse on 1_TableDefinitions.biml and generate SSIS packages. If you do this otherwise, you will get an empty SSIS package. .
Below you can see the result of your BIML scripts: a package with an execute SQL Task for each table you need to create.
The actual create statement looks like this
Summary
<Biml xmlns="http://schemas.varigence.com/biml.xsd"> <# string Prefix="Man"; // the locatie of the csv's' string path = @"D:\Drop\Man"; // Put all the filenames with the extension csv in a string array string[] myFiles = Directory.GetFiles(path, "*.csv"); // string that will be filled with the filename string filename; // string array for columnnames extracted from CSV string[] myColumns; #> <Connections> <OleDbConnection Name="OLEDB_STG_<#=Prefix#>" ConnectionString="Data Source=APPL43;Initial Catalog=dummy_STG;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"> </OleDbConnection> </Connections> <Databases> <Database ConnectionName="OLEDB_STG_<#=Prefix#>" Name="dummy_STG"/> </Databases> <Schemas> <Schema Name="dbo" DatabaseName="dummy_STG" Owner="dbo"/> </Schemas> <Tables> <!-- loop trough the array of files--> <# foreach(string filePath in myFiles) { // extract the filename from the path to use as tablename fileName = Path.GetFileNameWithoutExtension(filePath); #> <Table Name="<#=Prefix#>_<#=fileName#>" SchemaName="dummy_STG.dbo"> <Columns> <!-- loop trough the file looking for the columnnames--> <# // read first row of csv to extract to columnnames // and split on comma to create an array StreamReader myFile = new StreamReader(filePath); myColumns = myFile.ReadLine().Split(','); // close file after reading first line myFile.Close(); // Loop through column array foreach(string myColumn in myColumns) { #> <Column Name="<#=myColumn#>" DataType="String" Length="255"></Column> <# } #> </Columns> </Table> <# }#> </Tables> </Biml> <#@ template language="C#" hostspecific="true"#> <#@ import namespace="System.IO"#>
3) CreateTable
Secondly we are going to create the biml, called 2_CreateTables.biml. which creates the actual package that contains the create statements to generate the tables.
BIML has an method to create SQL tables “RootNode.Tables.First().GetTableSql();”
We use this method to create ‘SQL create statement’ the of table
The code looks like this
BIML has an method to create SQL tables “RootNode.Tables.First().GetTableSql();”
We use this method to create ‘SQL create statement’ the of table
The code looks like this
<Biml xmlns="http://schemas.varigence.com/biml.xsd"> <Packages> <Package Name="CreateTables" AutoCreateConfigurationsType="None" ConstraintMode="Linear"> <Tasks> <# // Loop trough the table definition os the first biml foreach(var table in RootNode.Tables) {#> <ExecuteSQL Name="SQL - Drop_Create <#=table.Name#>" ConnectionName="<#=table.Connection.Name#>"> <DirectInput> <#=table.GetTableSql()#> </DirectInput> </ExecuteSQL> <# } #> </Tasks> </Package> </Packages> </Biml> <!--Includes/Imports for C#--> <#@ template language="C#" hostspecific="true"#> <#@ import namespace="System.Data"#> <#@ import namespace="System.Data.SqlClient"#>
We’ve created 2 bimls, 1_TableDefinitions.biml and 2_CreateTables.biml. Now comes the important part (I’m using Biml Express) for generating the package. First we click on 1_TableDefinitions and secondly on and 2_CreateTables, if you have selected the 2 biml scripts you click with your right mouse on 1_TableDefinitions.biml and generate SSIS packages. If you do this otherwise, you will get an empty SSIS package. .
Generate SSIS package |
Below you can see the result of your BIML scripts: a package with an execute SQL Task for each table you need to create.
Visual studio |
The actual create statement looks like this
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO ------------------------------------------------------------------- IF EXISTS (SELECT * from sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Man_Age]') AND type IN (N'U')) DROP TABLE [dbo].[Man_Age] GO CREATE TABLE [dbo].[Man_Age] ( -- Columns Definition [AgeID] nvarchar(255) NOT NULL , [AgeFrom] nvarchar(255) NOT NULL , [AgeTo] nvarchar(255) NOT NULL , [AgeCategoryEmployee] nvarchar(255) NOT NULL , [AgeCategoryClient] nvarchar(255) NOT NULL -- Constraints ) ON "default" WITH (DATA_COMPRESSION = NONE) GO -------------------------------------------------------------------
We created two biml scripts one for creating to table definition and one which creates the actuale packages.The result in Management Studio looks like this.
In the next blog I’m going to explain how to create SSIS packages that transport the data from the csv files
Management studio result |
In the next blog I’m going to explain how to create SSIS packages that transport the data from the csv files
Hi I had some problem in duplicating the packages with your code. In the 2nd biml, the compiler shows the package has empty task. When I generate packages by selecting both biml files, there is error message:
ReplyDeleteError 0 FlowFramework, Version=5.0.61915.0, Culture=neutral, PublicKeyToken=015f370a62f9a1a3. '<', hexadecimal value 0x3C, is an invalid attribute character. Line 1, position 14. Exception type: XmlException C:\CreateTables.biml 0 0
Where can I download your original code?
Thanks for your sharing your knowledge here.