USE TEMPORARY
GO
IF OBJECT_ID('tempdb..#tempCol') IS NOT NULL
DROP TABLE #tempCol
IF OBJECT_ID('tempdb..#tempDiabloCol') IS NOT NULL
DROP TABLE #tempDiabloCol
IF OBJECT_ID('tempdb..#ColumnMapping') IS NOT NULL
DROP TABLE #ColumnMapping
IF OBJECT_ID('tempdb..#ALL') IS NOT NULL
DROP TABLE #ALL
GO
SELECT DISTINCT
CAST(REPLACE(MainframeField, '-', '_') AS VARCHAR(256)) AS MainframeField,
CAST(REPLACE(ColumnName, '-', '_') AS VARCHAR(256)) AS ColumnName
INTO #tempCol
FROM (SELECT [MainframeField],
[ColumnName]
FROM [MasagaDataModel].[dbo].[MappingTaxRollDiablo3]
UNION ALL
SELECT [MainframeField],
[ColumnName]
FROM [MasagaDataModel].[dbo].[MappingTransDiablo]) k
GO
RETURN
SELECT [MainframeField],
[ColumnName]
FROM #tempCol
WHERE MainframeField LIKE '%BROK%ADDR%'
ORDER BY [MainframeField]
GO
SELECT DISTINCT
ColumnName,
columndef
INTO #tempDiabloCol
FROM Diablo.Metadata.Columns
WHERE SchemaName IN ('tTax', 'tTrans')
AND TYPE = 'u'
GO
;
WITH step1
AS (SELECT a.MainframeField,
a.ColumnName,
b.ColumnDef
FROM #tempCol a
LEFT OUTER JOIN #tempDiabloCol b ON a.ColumnName = b.ColumnName),
step2
AS (SELECT MainframeField,
COUNT(*) AS COUNTER
FROM step1
GROUP BY MainframeField
HAVING COUNT(*) = 1)
SELECT a.*
INTO #ColumnMapping
FROM step1 a
INNER JOIN step2 b ON a.MainframeField = b.MainframeField
ORDER BY b.COUNTER DESC,
A.MainframeField
GO
SELECT PackageName,
ConnectionName,
a.ColumnName,
ColumnWidth,
SQLCompatibleDataType,
TextQualifier,
ColumnDelimiter,
ColumnId,
Format,
t.ColumnName AS DiabloColumnName,
t.ColumnDef AS DiabloColumnDef
INTO #ALL
FROM DBA.dbo.SSISPackageFileConnections a
LEFT OUTER JOIN #ColumnMapping t ON a.ColumnName = REPLACE(t.MainframeField, '-', '_')
--WHERE FORMAT = 'FixedWidth'
-- AND NOT EXISTS ( SELECT *
-- FROM dba.dbo.SSISPackageFileConnections b
-- WHERE FORMAT = 'FixedWidth'
-- AND b.ColumnWidth <> b.MaximumWidth
-- AND b.PackageName = a.PackageName
-- AND b.ConnectionName = a.ConnectionName )
ORDER BY PackageName,
ConnectionName,
ColumnId
GO
DECLARE @PackageName SYSNAME = 'TransactionLoad',
@ConnectionName VARCHAR(100) = 'TRN01'
DECLARE @SQL VARCHAR(MAX)
SET ANSI_WARNINGS OFF
SET NOCOUNT ON
;
WITH step0
AS (SELECT *,
COUNT(*) OVER (PARTITION BY PackageName, ConnectionName, DiabloColumnName) AS DCNT
FROM #ALL
WHERE PackageName = @PackageName
AND ConnectionName LIKE ISNULL(@ConnectionName, '%')),
step1
AS (SELECT TOP 999999
SSISFunction,
SQLFunction,
ColumnDelimiter,
'(''' + InputColumnName + ''', NULL,1 , ' + TextQualifierStr + ', ''' + SQLCompatibleDataType + ''')' AS SSISColumns,
'(''' + InputColumnName + ''', ''' + OutputColumnName + ''',1, ' + TextQualifierStr + ', ''' + OutputDataType + ''')' AS SQLColumns
--ddata (InputColumnName, OutputColumnName, Included, TextQualifier, DataType)
FROM step0
CROSS APPLY (SELECT '[Util].[' + PackageName + '].[' + REPLACE(ConnectionName, ' ', '_') + '_SSIS]' AS SSISFunction,
'[Util].[' + PackageName + '].[' + REPLACE(ConnectionName, ' ', '_') + '_SQL]' AS SQLFunction,
REPLACE(ColumnName, '''', '''''') AS InputColumnName,
ISNULL(CASE WHEN DCNT = 1 THEN DiabloColumnName
END, REPLACE(ColumnName, '''', '''''')) AS OutputColumnName,
ISNULL('''' + REPLACE(TextQualifier, '''', '''''') + '''', 'NULL') AS TextQualifierStr,
ISNULL(DiabloColumnDef, SQLCompatibleDataType) AS OutputDataType) b
ORDER BY SSISFunction,
SQLFunction,
ColumnId),
step2
AS (SELECT 'DECLARE @ColumnDefDelimited dbo.ColumnDefDelimited
INSERT @ColumnDefDelimited
(InputColumnName,
OutputColumnName,
Included,
TextQualifier,
DataType)
SELECT InputColumnName,
OutputColumnName,
Included,
TextQualifier,
DataType
FROM ( VALUES' + Util.dbo.StringConcat(SSISColumns, ',
') + ') ddata (InputColumnName, OutputColumnName, Included, TextQualifier, DataType)
EXEC dbo.LineFunctionGeneratorDelimited
@FunctionName = ''' + SSISFunction + ''',
@ColumnDefDelimited = @ColumnDefDelimited,
@Cast = 1, -- Will convert to the given datatype
@ShowInput = 1, -- Will show output and input both
@ListErrors = 1, -- Will have an additional colum for errors
@SortInputAlpha = 0, -- Sorts columns alphabetically by input names
@SortOutputAlpha = 0, -- Sorts columns alphabetically by output names
@Delimiter = ''' + MAX(ColumnDelimiter) + ''', -- Delimiter
@PrintSQL = 1, -- Prints results
@PrintSample = 0 -- Prints sample select
GO
DECLARE @ColumnDefDelimited dbo.ColumnDefDelimited
INSERT @ColumnDefDelimited
(InputColumnName,
OutputColumnName,
Included,
TextQualifier,
DataType)
SELECT InputColumnName,
OutputColumnName,
Included,
TextQualifier,
DataType
FROM ( VALUES' + Util.dbo.StringConcat(SQLColumns, ',
') + ') ddata (InputColumnName, OutputColumnName, Included, TextQualifier, DataType)
EXEC dbo.LineFunctionGeneratorDelimited
@FunctionName = ''' + SQLFunction + ''',
@ColumnDefDelimited = @ColumnDefDelimited,
@Cast = 1, -- Will convert to the given datatype
@ShowInput = 1, -- Will show output and input both
@ListErrors = 1, -- Will have an additional colum for errors
@SortInputAlpha = 0, -- Sorts columns alphabetically by input names
@SortOutputAlpha = 0, -- Sorts columns alphabetically by output names
@Delimiter = ''' + MAX(ColumnDelimiter) + ''', -- Delimiter
@PrintSQL = 1, -- Prints results
@PrintSample = 0 -- Prints sample select
GO
' AS SQL
FROM STEP1
GROUP BY SSISFunction,
SQLFunction)
SELECT @SQL = 'SET NOCOUNT ON
GO
USE Util
GO
' /*+ (SELECT Util.dbo.StringConcat(DISTINCT 'CREATE SCHEMA ' + PackageName + '
GO
', '')
FROM #ALL
WHERE FORMAT = 'Delimited'
AND PackageName NOT LIKE '%[_]Old')*/ + (SELECT Util.dbo.StringConcat (SQL, '') FROM step2)
EXEC Util.dbo.PrintLargeText
@SQL
GO