<#@ 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
Comments
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 ?