这是我要使用SSIS和条件拆分转换来解决的难题.我有一个.csv文件,该文件在一行中为每个唯一用户包含属性数据,在另一列中包含每个属性的值.即:
This is a bit of a conundrum I am trying to solve using SSIS and a conditional-split transformation. I have a .csv file that contains attribute data in one row for each unique user and the values for each attribute in another column. i.e.:
Attribute, Attribute Type ID, 0000000001 Birthdate, 09/02/1976 Role, Manager或类似的东西.我需要将属性拆分为包含属性类型数据"的列.因此,理想的结果将是:
Or something of the sort. I need to split the attributes into columns that include the Attribute Type Data. So the desired outcome would be:
ID, Birthdate, Role, 0000000001, 09/02/1976, Manager,然后我需要将它们插入带有新列的一个SQL表中.
I then need to insert them into one SQL table with the new columns.
我能够通过对一列进行条件拆分转换(例如使用表达式Attribute =="ID",然后将.csv源代码中的整个Attribute列映射到SQL中的ID列)来出色地完成此操作目标表),但问题在于其他列.我似乎无法实现Union All转换来完成我想做的事情.
I was able to accomplish this beautifully with a conditional-split transformation for one column (using the expression Attribute == "ID" for example and then mapping the entire Attribute column in the .csv source onto the ID column in the SQL destination table) but the problem is doing so for the other columns. I can't seem to get a Union All transformation to do what I want it to do.
有什么建议吗?
推荐答案您可以使用脚本组件来实现:
string ID = ""; string BirthDate = ""; string Role = ""; public override void Input0_ProcessInputRow(Input0Buffer Row) { if(!Row.Attribute_IsNull && !String.IsNullOrWhiteSpace(Row.Attribute)) { switch (Row.Attribute) { case "ID": ID = Row.AttributeType; break; case "BirthDate": BirthDate = Row.AttributeType; break; case "Role": Role = Row.AttributeType; Output0Buffer.AddRow(); Output0Buffer.ID = ID; Output0Buffer.Role = Role; Output0Buffer.BirthDate = BirthDate; break; default: break; } } }
更多推荐
在另一列的基础上过滤一列中的数据值,然后将值插入同一SQL表中的不同列中
发布评论