USE TEMPORARY
GO
ALTER TABLE AppSSISPackages ADD CONSTRAINT AppSSISPackages_PKC PRIMARY KEY CLUSTERED (PackageId)
go
CREATE PRIMARY XML INDEX [PK_XML_Data_AppSSISPackages] ON AppSSISPackages (pkgXML)
go
DROP TABLE SSISColumns
go
;
WITH XMLNAMESPACES (
'www.microsoft.com/SqlServer/Dts' AS DTS
),
package AS (
SELECT name,
pkgXML AS PackageDataXML
FROM AppSSISPackages
)
,step1 AS (SELECT
name AS PackageName,
x.c.value ('(DTS:Property[@DTS:Name="ObjectName"])[1]','nvarchar(100)') AS ConnectionName,
x.c.value ('(DTS:ObjectData/DTS:ConnectionManager/DTS:Property[@DTS:Name="Format"])[1]', 'nvarchar(100)') AS Format,
x.c.value ('(DTS:Property[@DTS:Name="CreationName"])[1]','nvarchar(100)') AS CreationName,
x.c.value ('(DTS:PropertyExpression[@DTS:Name="ConnectionString"])[1]','nvarchar(1000)') AS ConnectionString,
x.c.value ('(DTS:ObjectData/DTS:ConnectionManager/DTS:Property[@DTS:Name="HeaderRowsToSkip"])[1]', 'int') AS HeaderRowsToSkip,
x.c.value ('(DTS:ObjectData/DTS:ConnectionManager/DTS:Property[@DTS:Name="HeaderRowDelimiter"])[1]', 'nvarchar(100)') AS HeaderRowDelimiter,
x.c.value ('(DTS:ObjectData/DTS:ConnectionManager/DTS:Property[@DTS:Name="ColumnNamesInFirstDataRow"])[1]', 'int') AS ColumnNamesInFirstDataRow,
x.c.value ('(DTS:ObjectData/DTS:ConnectionManager/DTS:Property[@DTS:Name="RowDelimiter"])[1]', 'nvarchar(100)') AS RowDelimiter,
x.c.value ('(DTS:ObjectData/DTS:ConnectionManager/DTS:Property[@DTS:Name="DataRowsToSkip"])[1]', 'int') AS DataRowsToSkip,
x.c.value ('(DTS:ObjectData/DTS:ConnectionManager/DTS:Property[@DTS:Name="TextQualifier"])[1]', 'nvarchar(100)') AS TextQualifier,
ldi.c.value ('(DTS:Property[@DTS:Name="ObjectName"])[1]','nvarchar(100)') AS ColumnName,
ldi.c.value ('(DTS:Property[@DTS:Name="ColumnType"])[1]','nvarchar(100)') AS ColumnType,
ldi.c.value ('(DTS:Property[@DTS:Name="ColumnDelimiter"])[1]','nvarchar(100)') AS ColumnDelimiter,
ldi.c.value ('(DTS:Property[@DTS:Name="ColumnWidth"])[1]','int') AS ColumnWidth,
ldi.c.value ('(DTS:Property[@DTS:Name="MaximumWidth"])[1]','int') AS MaximumWidth,
ldi.c.value ('(DTS:Property[@DTS:Name="DataType"])[1]','int') AS DataType,
ldi.c.value ('(DTS:Property[@DTS:Name="DataPrecision"])[1]','int') AS DataPrecision,
ldi.c.value ('(DTS:Property[@DTS:Name="DataScale"])[1]','int') AS DataScale,
ldi.c.value ('(DTS:Property[@DTS:Name="TextQualified"])[1]','int') AS TextQualified
FROM package
CROSS APPLY PackageDataXML.nodes ('/DTS:Executable/DTS:ConnectionManager') AS x(c)
CROSS APPLY x.c.nodes('DTS:ObjectData/DTS:ConnectionManager/DTS:FlatFileColumn') ldi(c)
)
SELECT PackageName, ConnectionName, Format, CreationName, ConnectionString, HeaderRowsToSkip, HeaderRowDelimiter, ColumnNamesInFirstDataRow, RowDelimiter, DataRowsToSkip, TextQualifier,
COUNT(*) OVER (PARTITION BY PackageName, ConnectionName ) AS ColumnCnt,
ROW_NUMBER() OVER (PARTITION BY PackageName, ConnectionName ORDER BY (SELECT 0)) AS ColumnId,
ColumnName, ColumnType, ColumnDelimiter, ColumnWidth, MaximumWidth, DataType, DataPrecision, DataScale, TextQualified
INTO TEMPORARY..SSISColumns
FROM step1
GO
CREATE UNIQUE CLUSTERED INDEX SSISColumns ON SSISColumns(PackageName, ConnectionName, ColumnId)
GO
CREATE UNIQUE NONCLUSTERED INDEX SSISColumns2 ON SSISColumns(PackageName, ConnectionName, ColumnName)
GO
SELECT PackageName,
ConnectionName,
Format,
HeaderRowsToSkip,
CASE WHEN TextQualifier NOT LIKE '%none%' THEN TextQualifier
END AS TextQualifier,
ColumnCnt,
ColumnId,
ColumnName,
ColumnTYpe,
b.ColumnDelimiter,
ColumnWidth,
MaximumWidth,
sd.SSISDataType,
sd.DataTypeName,
DataPrecision,
DataScale,
TextQualified
FROM TEMPORARY..SSISColumns a
CROSS APPLY (SELECT CASE WHEN ColumnCnt <> ColumnId
AND ColumnDelimiter <> '' THEN CHAR(CONVERT(VARBINARY(2), '0' + SUBSTRING(ColumnDelimiter, 2, LEN(ColumnDelimiter) - 2), 1))
END AS ColumnDelimiter) b
LEFT OUTER JOIN Util.dbo.SSISDataTypes sd ON sd.DataTypeId = a.DataType
WHERE CreationName = 'FLATFILE'
AND connectionname = 'ff_pcl_cf_grp_info_01'
GO