如何在SSIS中动态映射输入和输出列? [英] How to Map Input and Output Columns dynamically in SSIS?
问题描述
我必须通过SSIS从.dbf文件上传SQL Server中的数据. 我的输出列是固定的，但输入列不是固定的，因为文件来自客户端，并且客户端可能会按照自己的样式更新数据.可能还会有一些未使用的列，或者输入列的名称可能与输出列的名称不同.
I Have to Upload Data in SQL Server from .dbf Files through SSIS. My Output Column is fixed but the input column is not fixed because the files come from client and client may have updated data by his own style. there may be some unused column too or input column name can be different from output column.
我想到的一个主意是将SQL数据库表中的文件输入列与输出列进行映射，并仅使用文件ID行中存在的那些列.
One idea I had in my mind was to map files input column with output column in SQL Database table and use only those column which is present in the row for file id.
但是我不知道该怎么做. 你能建议我做同样的事情吗，否则你有什么主意吗?
But I am not getting how to do that. can you suggest me for doing the same or else you have any idea?
表示例.
+--------+---------------+--------------+--------+
| FileID | InputColumn | OutputColumn | Active |
+--------+---------------+--------------+--------+
| 1 | CustCd | CustCode | 1 |
+--------+---------------+--------------+--------+
| 1 | CName | CustName | 1 |
+--------+---------------+--------------+--------+
| 1 | Address | CustAdd | 1 |
+--------+---------------+--------------+--------+
| 2 | Cust_Code | CustCode | 1 |
+--------+---------------+--------------+--------+
| 2 | Customer Name | CustName | 1 |
+--------+---------------+--------------+--------+
| 2 | Location | CustAdd | 1 |
+--------+---------------+--------------+--------+
+--------+---------------+--------------+--------+
| FileID | InputColumn | OutputColumn | Active |
+--------+---------------+--------------+--------+
| 1 | CustCd | CustCode | 1 |
+--------+---------------+--------------+--------+
| 1 | CName | CustName | 1 |
+--------+---------------+--------------+--------+
| 1 | Address | CustAdd | 1 |
+--------+---------------+--------------+--------+
| 2 | Cust_Code | CustCode | 1 |
+--------+---------------+--------------+--------+
| 2 | Customer Name | CustName | 1 |
+--------+---------------+--------------+--------+
| 2 | Location | CustAdd | 1 |
+--------+---------------+--------------+--------+
推荐答案
如果创建类似的表，则可以通过2种方法使用它来动态映射SSIS包内的列，或者必须以编程方式构建整个包.在此答案中，我将尝试为您提供一些有关如何执行此操作的见解.
注意:仅当所有.dbf文件的列数相同但名称不同时，此方法才有效
使用这种方法，您将基于创建的FileID和Mapping表生成将用作源的SQL命令.您必须知道FileID和.dbf文件路径存储在变量中.例如:
In this approach you will generate the SQL command that will be used as source based on the FileID and the Mapping table you created. You must know is the FileID and the .dbf File Path stored inside a Variable. as example:
假定表名称为inputoutputMapping
使用以下命令添加执行SQL任务:
Add an Execute SQL Task with the following command:
DECLARE @strQuery as VARCHAR(4000)
SET @strQuery = 'SELECT '
SELECT @strQuery = @strQuery + '[' + InputColumn + '] as [' + OutputColumn + '],'
FROM inputoutputMapping
WHERE FileID = ?
SET @strQuery = SUBSTRING(@strQuery,1,LEN(@strQuery) - 1) + ' FROM ' + CAST(? as Varchar(500))
SELECT @strQuery
然后在参数映射"选项卡中，选择包含要映射到参数0的FileID的变量和包含.dbf文件名(替代表名)的变量到参数1
And in the Parameter Mapping Tab select the variable that contains the FileID to be Mapped to the parameter 0 and the variable that contains the .dbf file name (alternative to table name) to the parameter 1
将ResultSet类型设置为Single Row并将结果集0存储在字符串类型的变量中，例如@[User::SourceQuery]
Set the ResultSet type to Single Row and store the ResultSet 0 inside a variable of type string as example @[User::SourceQuery]
ResultSet值如下:
The ResultSet value will be as following:
SELECT [CustCd] as [CustCode],[CNAME] as [CustName],[Address] as [CustAdd] FROM database1
在OLEDB Source中，从变量"中选择"SQL命令的表访问模式"，然后使用@[User::SourceQuery]变量作为源.
In the OLEDB Source select the Table Access Mode to SQL Command from Variable and use @[User::SourceQuery] variable as source.
在这种方法中，您必须在数据流任务中使用脚本组件作为源:
In this approach you have to use a Script Component as Source inside the Data Flow Task:
首先，如果您不想对其进行硬编码，则需要通过变量将.dbf文件路径和SQL Server连接传递给脚本组件.
在脚本编辑器中，您必须为在目标表中找到的每个列添加一个输出列，并将它们映射到目标.
Inside the script editor, you must add an output column for each column found in the destination table and map them to the destination.
在脚本中，您必须将.dbf文件读入数据表:
Inside the Script, you must read the .dbf file into a datatable:
- C# Read from .DBF files into a datatable
- Load a DBF into a DataTable
将数据加载到数据表中后，还用在SQL Server中创建的MappingTable中找到的数据填充另一个数据表.
After loading the data into a datatable, also fill another datatable with the data found in the MappingTable you created in SQL Server.
之后，循环遍历datatable列，并将.ColumnName更改为相关的输出列，例如:
After that loop over the datatable columns and change the .ColumnName to the relevant output column, as example:
foreach (DataColumn col in myTable.Columns)
{
col.ColumnName = MappingTable.AsEnumerable().Where(x => x.FileID = 1 && x.InputColumn = col.ColumnName).Select(y => y.OutputColumn).First();
}
循环遍历数据表中的每一行并创建脚本输出行.
After loop over each row in the datatable and create a script output row.
此外，请注意，在分配输出行时，必须检查该列是否存在，您可以首先将所有列的名称添加到字符串列表，然后使用它进行检查，例如:
In addition, note that in while assigning output rows, you must check if the column exists, you can first add all columns names to list of string, then use it to check, as example:
var columnNames = myTable.Columns.Cast<DataColumn>()
.Select(x => x.ColumnName)
.ToList();
foreach (DataColumn row in myTable.Rows){
if(columnNames.contains("CustCode"){
OutputBuffer0.CustCode = row("CustCode");
}else{
OutputBuffer0.CustCode_IsNull = True
}
//continue checking all other columns
}
如果您需要有关使用脚本组件作为源的更多详细信息，请检查以下链接之一:
If you need more details about using a Script Component as a source, then check one of the following links:
- SSIS Script Component as Source
- Creating a Source with the Script Component
- Script Component as Source – SSIS
- SSIS – USING A SCRIPT COMPONENT AS A SOURCE
我不认为可以使用其他方法来实现此目标，除非您可以选择动态构建程序包，然后选择:
I don't think there are other methods that you can use to achieve this goal except you has the choice to build the package dynamically, then you should go with:
- BIML
- Integration Services managed object model
- EzApi library
最近，我在Git-Hub上启动了一个新项目，该项目是使用C#开发的类库.您可以使用它来使用架构映射方法将Excel，Word，PowerPoint，文本，CSV，HTML，JSON和xml中的表格数据导入具有不同架构定义的SQL Server表中.在以下位置查看:
Recently i started a new project on Git-Hub, which is a class library developed using C#. You can use it to import tabular data from excel, word , powerpoint, text, csv, html, json and xml into SQL server table with a different schema definition using schema mapping approach. check it out at:
您可以按照以下Wiki页面获取逐步指南:
You can follow this Wiki page for a step-by-step guide:
这篇关于如何在SSIS中动态映射输入和输出列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
