DECLARE @SrcQuery nvarchar(4000);

DECLARE @FeedName nvarchar(25);

SELECT @SrcQuery = 'SELECT TOP(100) FROM sys.objects;', @FeedName = 'Objects';

IF OBJECT_ID('tempdb..#FileFormatMetadata') IS NOT NULL DROP TABLE #FileFormatMetadata;

t.[Column Name]

,t.[SQL DT]

,CASE

WHEN t.[SQL DT] in ('char', 'varchar','text') THEN (t.max_length)

WHEN t.[SQL DT] in ('nchar', 'nvarchar','ntext') THEN (t.max_length/2)

ELSE 0 END [Length]

,CASE

WHEN t.[SQL DT] = 'smallint' THEN 'Int16'

WHEN t.[SQL DT] = 'tinyint' THEN 'Byte'

WHEN t.[SQL DT] = 'int' THEN 'Int32'

WHEN t.[SQL DT] = 'bigint' THEN 'Int64'

WHEN t.[SQL DT] = 'char' THEN 'AnsiStringFixedLength'

WHEN t.[SQL DT] = 'varchar' THEN 'AnsiString'

WHEN t.[SQL DT] = 'nchar' THEN 'StringFixedLength'

WHEN t.[SQL DT] = 'nvarchar' THEN 'String'

WHEN t.[SQL DT] = 'date' THEN 'Date'

WHEN t.[SQL DT] = 'datetime' THEN 'DateTime'

WHEN t.[SQL DT] = 'datetime2' THEN 'DateTime2'

WHEN t.[SQL DT] = 'datetimeoffset' THEN 'DateTimeOffset'

WHEN t.[SQL DT] = 'smalldatetime' THEN 'DateTime'

WHEN t.[SQL DT] = 'time' THEN 'Time'

WHEN t.[SQL DT] = 'decimal' THEN 'Decimal'

WHEN t.[SQL DT] = 'numeric' THEN 'Decimal'

WHEN t.[SQL DT] = 'bit' THEN 'Boolean'

WHEN t.[SQL DT] = 'float' THEN 'Double'

WHEN t.[SQL DT] = 'real' THEN 'Single'

WHEN t.[SQL DT] = 'money' THEN 'Currency'

WHEN t.[SQL DT] = 'smallmoney' THEN 'Currency'

WHEN t.[SQL DT] = 'text' THEN 'AnsiString'

WHEN t.[SQL DT] = 'ntext' THEN 'String'

ELSE NULL END [Biml Data Type]

,t.ColumnOrdinal

,@FeedName FeedName

INTO #FileFormatMetadata

FROM (SELECT name [Column Name]

,CASE WHEN CHARINDEX('(',system_type_name) = 0 THEN system_type_name

ELSE left(system_type_name, CHARINDEX('(',system_type_name) - 1)

END [SQL DT]

,max_length

,column_ordinal ColumnOrdinal

FROM sys.dm_exec_describe_first_result_set(@SrcQuery, NULL, 1) where [name] is not NULL and is_hidden = 0) t

SELECT CAST(REPLACE(CAST( (

SELECT

(SELECT 'conn_ff_' + @FeedName "@Name"

,'C:\FileExport\' + @FeedName + '.csv' "@FilePath"

,'fff_' + @FeedName "@FileFormat"

FOR XML PATH('FlatFileConnection'), type, root('Connections'))

,(SELECT 'Export ' + @FeedName "@Name"

, 'Linear' "@ConstraintMode"

,'DontSaveSensitive' "@ProtectionLevel"

,(SELECT

'DFT Load File' "@Name"

,'OLE_SRC ' + @FeedName "Transformations/OleDbSourceName/@Name"

,'connNorthwind' "Transformations/OleDbSourceName/@ConnectionName"

,@SrcQuery "Transformations/OleDbSourceName/DirectInput"

,'FF_DEST ' + @FeedName "Transformations/FlatFileDestination/@Name"

,'conn_ff_ ' + @FeedName "Transformations/FlatFileDestination/@ConnectionName"

,'true' "Transformations/FlatFileDestination/@Overwrite"

,'false' "Transformations/FlatFileDestination/@ValidateExternalMetadata"

FOR XML PATH('Dataflow'),TYPE, root('Tasks'))

FOR XML PATH('Package'), TYPE,ROOT('Packages'))

,(SELECT 'fff_' + @FeedName "@Name"

, 'Delmited' "@FlatFileType"

,'true' "@ColumnNamesInFirstDataRow"

,'_x0022_' "@TextQualifier"

,(SELECT [Column Name] "@Name"

,[Biml Data Type] "@DataType"

,Length "@Length"

,CASE WHEN ColumnOrdinal = (SELECT MAX(ColumnOrdinal) FROM #FileFormatMetadata) THEN 'CRLF'

ELSE 'Comma' END "@Delimiter"

from #FileFormatMetadata

FOR XML PATH('Column'), type, root('Columns'))

FOR XML PATH('FlatFileFormat'), type, root('FileFormats'))

FOR XML PATH ('Biml')

) AS nvarchar(MAX)), '<Biml>', '<Biml xmlns="http://schemas.varigence.com/biml.xsd">') AS XML);