Share

BIML Export to FlatFile

by John Minkjan

published 09.25.12
last updated 09.25.12

Summary

Demo BIML on creating an export to flat file package based on the SQL-Server metadata views

            


<#@ template language="C#" hostspecific="true"#>
<#@ import namespace="System.Data"#>
<#@ import namespace="System.Data.SqlClient"#>
<#@ import namespace="System.IO"#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Annotations>
		<Annotation>
			File: Export.DataBase.Schema.biml
			Demo make a export to file package
			Enviroment:
			DB: MS-SQL2012
			BIML: 1.6 VS2010 BIDS Helper
			(c) John Minkjan biml101.blogspot.com
		</Annotation>
	</Annotations>
<#
	string CnSrcDB = "Server=.\\MSSQL2012;Initial Catalog=AdventureWorks2012;Integrated Security=SSPI;Provider=SQLNCLI11.1";
	string SrcTableCatalog = "AdventureWorks2012";
	string SrcTableSchema ="Sales";
	string TableCatalog="";
	string TableSchema ="";
	string TableName ="";
	string ColumnName ="";
	string DataType ="";
	string CharacterMaximumLength ="";
	string FlatFileFormatName ="";
	string MaxOrdinalPosition ="";
	string ColumnDelimeter =";";
	
	DataTable tFFFCol;
#>
<FileFormats>
	<# DataTable tFFF = ExternalDataAccess.GetDataTable(CnSrcDB,
	"SELECT t1.TABLE_CATALOG, t1.TABLE_SCHEMA, t1.TABLE_NAME,  max(t1.ORDINAL_POSITION) as MAX_ORDINAL_POSITION FROM  INFORMATION_SCHEMA.COLUMNS AS t1 INNER JOIN INFORMATION_SCHEMA.TABLES  AS t2 ON t1.TABLE_CATALOG = t2.TABLE_CATALOG  AND t1.TABLE_SCHEMA = t2.TABLE_SCHEMA AND t1.TABLE_NAME = t2.TABLE_NAME  WHERE        (t1.TABLE_CATALOG = '" +SrcTableCatalog + "') AND (t2.TABLE_TYPE = 'BASE TABLE')  and t2.TABLE_SCHEMA = '" + SrcTableSchema +"' group by t1.TABLE_CATALOG, t1.TABLE_SCHEMA, t1.TABLE_NAME");
	foreach (DataRow tFFFr in tFFF.Rows){
	TableCatalog =tFFFr[0].ToString();
	TableSchema = tFFFr[1].ToString();
	TableName = tFFFr[2].ToString();
	MaxOrdinalPosition = tFFFr[3].ToString();
	#>
	<FlatFileFormat FlatFileType="Delimited" 	ColumnNamesInFirstDataRow="true" 	RowDelimiter="CRLF"  Name="FFF <#=TableCatalog#> <#=TableSchema#> <#=TableName#>">
		<Columns>
			<# tFFFCol = ExternalDataAccess.GetDataTable(CnSrcDB,
			"SELECT t1.COLUMN_NAME, t1.DATA_TYPE, t1.CHARACTER_MAXIMUM_LENGTH, T1.ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS AS t1 where t1.TABLE_CATALOG = '"+TableCatalog+"' and t1.TABLE_SCHEMA = '"+TableSchema+"' and t1.TABLE_NAME = '"+TableName+"'");
			foreach (DataRow tFFFRow in tFFFCol.Rows){
			ColumnName =tFFFRow[0].ToString();
			DataType = tFFFRow[1].ToString();
			CharacterMaximumLength = tFFFRow[2].ToString();	

				ColumnDelimeter = ";";
				if (MaxOrdinalPosition == tFFFRow[3].ToString()){ ColumnDelimeter = "CRLF"; } ;
				DataType = "String";								
			#>
				<Column ColumnType="Delimited" DataType= "<#=DataType#>" Delimiter="<#=ColumnDelimeter#>" Length="4000" Name="<#=ColumnName#>"></Column>
			<# } #>
		</Columns>
	</FlatFileFormat>
	<#}  #>
</FileFormats>
	<Connections>
		<OleDbConnection
			Name="CnOleDBAdventureWorks2012"
			ConnectionString="Data Source=.\MSSQL2012;Initial Catalog=AdventureWorks2012;Provider=SQLNCLI10.1;Integrated Security=SSPI;Connect Timeout=30;"
			RetainSameConnection="true">
		</OleDbConnection>
		<# DataTable tFF = ExternalDataAccess.GetDataTable(CnSrcDB,
		"SELECT t1.TABLE_CATALOG, t1.TABLE_SCHEMA, t1.TABLE_NAME FROM  INFORMATION_SCHEMA.COLUMNS AS t1 INNER JOIN INFORMATION_SCHEMA.TABLES  AS t2 ON t1.TABLE_CATALOG = t2.TABLE_CATALOG  AND t1.TABLE_SCHEMA = t2.TABLE_SCHEMA AND t1.TABLE_NAME = t2.TABLE_NAME  WHERE        (t1.TABLE_CATALOG = '"
		+SrcTableCatalog+"') AND (t2.TABLE_TYPE = 'BASE TABLE') and t2.TABLE_SCHEMA = '"
		+SrcTableSchema+"' group by t1.TABLE_CATALOG, t1.TABLE_SCHEMA, t1.TABLE_NAME");
		foreach (DataRow tFFFr in tFFF.Rows){
		TableCatalog =tFFFr[0].ToString();
		TableSchema = tFFFr[1].ToString();
		TableName = tFFFr[2].ToString(); #>
			<FlatFileConnection Name="FF <#=TableCatalog#> <#=TableSchema#> <#=TableName#>" 
				FileFormat="FFF <#=TableCatalog#> <#=TableSchema#> <#=TableName#>" 
				FilePath="C:\\<#=TableCatalog#>.<#=TableSchema#>.<#=TableName#>.dat"></FlatFileConnection>			
			<# }#>
	</Connections>
	<Packages>
		<Package Name="Extract.All" ConstraintMode="Linear" FailParentOnFailure="false">
			<Tasks>
				<# 	
DataTable t1 = ExternalDataAccess.GetDataTable(CnSrcDB,
"SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES AS t2 WHERE (TABLE_CATALOG = '"
+SrcTableCatalog +"') AND (TABLE_TYPE = 'BASE TABLE') and t2.TABLE_SCHEMA = '"+SrcTableSchema+"' ");
foreach (DataRow row1 in t1.Rows){
	TableCatalog =row1[0].ToString();
	TableSchema = row1[1].ToString();
	TableName = row1[2].ToString(); #>
				<Dataflow  Name="DFT <#=TableCatalog#> <#=TableSchema#> <#=TableName#>">
					<Transformations>
						<OleDbSource
							Name="ODS <#=TableCatalog#> <#=TableSchema#> <#=TableName#>"
							ConnectionName="CnOleDBAdventureWorks2012">
							<ExternalTableInput Table ="[<#=TableCatalog#>].[<#=TableSchema#>].[<#=TableName#>]">
							</ExternalTableInput>
						</OleDbSource>
						<FlatFileDestination Name="DFF <#=TableCatalog#> <#=TableSchema#> <#=TableName#>" 
							ConnectionName="FF <#=TableCatalog#> <#=TableSchema#> <#=TableName#>"></FlatFileDestination>						
					</Transformations>
				</Dataflow>
				<# } #>
			</Tasks>
		</Package>
	</Packages>
</Biml>
        

File: Export.DataBase.Schema.biml; Demo make a export to file package ; Enviroment: DB: MS-SQL2012 BIML: 1.6 VS2010 BIDS Helper;(c) John Minkjan biml101.blogspot.com Also pubished on http://biml101.blogspot.nl/2012/09/biml-export-to-flatfile.html

You are not authorized to comment. A verification email has been sent to your email address. Please verify your account.

Comments

gravatar

dream

8:26pm 05.26.15

Exporting all columns in data flow task without specifying column names

I have a data flow task and in case of an error I export all the rows that were in error, I need to export all columns present in the data flow plus the error column and error code that were added to the data flow.

Using BIML is it possible to export every column in the data flow to a flat file in an automated manner without specifying each column name ?