将Excel XLS文件导入SSIS - 转换标题行并将其添加到基础记录

I have an Excel XLS file which we would like to import to a database using SSIS.

我有一个Excel XLS文件，我们希望使用SSIS将其导入数据库。

The format of the file is as below

文件格式如下

This is the Input File

这是输入文件

I would like to convert it to insert into a SQL table in the following format

我想将它转换为以下格式插入到SQL表中

SQL table layout

SQL表格布局

Any ideas on the best way to achieve this? I have a second question on the way to split the Investor Name/Address cell into multiple columns, but will put that in a separate question.

有关实现这一目标的最佳方法的任何想法？关于将投资者名称/地址单元格拆分为多个列的方法，我有第二个问题，但是会将其放在一个单独的问题中。

Thanks in Advance Steve

先谢谢史蒂夫

2 个解决方案

#1

The only way to do a transformation as complex as this is with a script task.

执行转换的唯一方法就是使用脚本任务。

#2

I'm on phone now.

我现在正打电话。

Here's what i suggest

这是我的建议

Import rows a$5:g

导入行$ 5：g

Import excel. Conditional split out null accounts.

导入excel。有条件地拆分空账户。

It seems like all investor account hrs are numbers

似乎所有投资者账户都是数字

Add script component as transform

添加脚本组件作为转换

On output add your 3 columns you want.

在输出中添加您想要的3列。

Outside row transform add 3 variables to meet your requirement ( ie string adv; et al )

外行变换添加3个变量以满足您的要求（即字符串adv;等）

Inside row If(!int.tryparse(row.investorid) {Adv= row.investmentid ... } Else { Row.advisor=adv ... }

内行If（！int.tryparse（row.investorid）{Adv = row.investmentid ...} Else {Row.advisor = adv ...}

Done w script

完成w脚本

Add conditional split to ignore null advisors.

添加条件拆分以忽略空顾问程序。