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