SSIS使用派生列动态添加源中不存在的列

Question

我正在尝试将两个源系统中的表拉到一个统一的目标服务器中。我们有多个业务单元，它们驻留在独立的源系统上，并且在大多数情况下具有相同的表结构，但是其中一个源系统升级到较新的版本，并且具有其他源没有的一些列。

我现在有数据流任务设置为在每个循环中运行，该循环查找并循环通过我的每个源。我需要根据源系统更改查询，以包括缺失的列。如果有问题的列不存在，我希望我可能能够动态地通过使用派生列来添加列，如果它存在，我希望包忽略派生列并继续前进。

我也试过写我的查询变量，但我不能完全弄清楚如何让DFT选择基于源连接的特定查询变量。

编辑:下面的表比较示例:

--Updated Source
SELECT [ProductID]
,[ProductNumber]
,[ReorderPoint]
,[ListPrice]
,[SizeUnitMeasureCode]
,[WeightUnitMeasureCode]
,[Class]
,[Style]  
,[ProductSubcategoryID]
,[ProductModelID]
,[ModifiedDate]
FROM [Sales].[Product];
--Outdated Source
SELECT [ProductID]
,[ProductNumber]
,[ReorderPoint]
,[ListPrice]
,[SizeUnitMeasureCode]
,[WeightUnitMeasureCode]
,[Class]
,[Style]  
,NULL AS [ProductSubcategoryID]
,NULL AS [ProductModelID]
,[ModifiedDate]
FROM [Sales].[Product];

我希望能够拉出所有列，包括那些从过时的源中缺失的列。用NULL AS标记的列就是问题中缺少的列。

Answer 1

我不喜欢这个解决方案，但它可能工作。正如我所提到的，您可能能够构造一个动态语句，然后从中构建查询。这很难看，但是，就像我说的，SSIS期望一致的定义，所以如果你不能给它，你必须跳过一些障碍。

这也是未经测试的，但希望能给你一个想法。

DECLARE @ColumnList table (OrdinalPosition int IDENTITY(1,1),
ColumnName sysname,
ColumnDatatype sysname);

--The following datatypes are completely guessed
INSERT INTO @ColumnList
VALUES(N'ProductID',N'int'),
(N'ProductNumber',N'int'),
(N'ReorderPoint',N'int'),
(N'ListPrice',N'decimal'),
(N'SizeUnitMeasureCode',N'decimal'); --You get the idea
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
DECLARE @Delimiter nvarchar(20) = N',' + @CRLF + N'       ';
SELECT @SQL = N'SELECT ' + 
STRING_AGG(ISNULL(QUOTENAME(c.[name]),N'CONVERT(' + QUOTENAME(CL.ColumnDatatype) + N',NULL') + N') AS ' + QUOTENAME(CL.ColumnName),@Delimiter) WITHIN GROUP (ORDER BY CL.OrdinalPosition) + @CRLF +
N'FROM Sales.Product;'
FROM @ColumnList CL
LEFT JOIN sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
AND t.[name] = N'Product'
JOIN sys.schemas s ON t.schema_id = s.schema_id
AND s.[name] = N'dbo'
ON CL.ColumnName = c.[name];
--PRINT @SQL; --Your best friend
EXEC sys.sp_executesql @SQL;

你应该能够使用这个作为SSIS的源的定义，并且它应该创建一个包含你想要的所有列的数据集，即使表没有它。

Answer 2

在SSIS中半动态地完成此操作的唯一方法是在数据流源查询中使用Expression，当连接到缺少某些列的系统时，该表达式将列名替换为文字null。或者在数据流中为您提供脚本源，并替换代码中缺失的列。