使用SSIS，如何将Excel表中的新数据插入到现有的SQL Server表中

I have imported some data from Excel file into my SQL Server table using SSIS. As because it's a report of profit & loss statement it has a header named as Trading Name, address fiscal year etc.

我已经使用SSIS将一些数据从Excel文件导入到我的SQL Server表中。因为它是一份损益表的报告,所以它有一个标题为交易名称,地址会计年度等。

In SSIS, I've used OLE DB Source and used the query like

在SSIS中,我使用了OLE DB Source并使用了类似的查询

Select * from [Sheet1$A10:E]

so the data has been picked up. But U need to insert the trading Name and fiscal year information along with the others data like

所以数据已被提取。但是你需要插入交易名称和会计年度信息以及其他数据

Trader FYear AccNo AccName   July        August     September
 a      2014 4-0000 In          
 a      2014 4-0500 Ad      $4,514.06   $4,521.23   $4,296.62
 a      2014 4-1000 Me      $37,085.78  $35,676.20  $36,150.87
 a      2014 4-1300 Ro      $11,876.20  $10,956.70  $11,819.04
 a      2014 4-2000 Gr      $57,540.76  $52,978.63  $54,272.24

I can pick up the trader and FYear from that Excel sheet by using OLE DB Source and SQL is

我可以使用OLE DB Source和SQL从Excel表中选择交易者和FYear

select * from [Sheet1$a1:a8]

and using conditional split I can separate them. But I don't actually know how to insert that data to each column of that table like above.

并使用条件分割我可以分开它们。但我实际上并不知道如何将该数据插入该表的每一列,如上所述。

One more thing, if i write the query like

还有一件事,如果我写这样的查询

insert into tblA(Trader, FYear, AccNo, AccName, July, August, September)
   select 
      'a', '2014', tblB.AccNo, tblB.AccName, 
      tblB.July, tblB.August, tblB.September; 

then, it works in SQL Server table but could anyone help me to insert those two columns with that data like above table? Thanks anyway

然后,它可以在SQL Server表中工作,但任何人都可以帮助我插入这两个列与上表中的数据?不管怎么说,还是要谢谢你

![enter image description here][1]

![在此处输入图像说明] [1]

Trading Name(a)                                                 
Address1                                                    
Address2                                                    


Profit & Loss Statement                                                 
Fiscal Year(2014)                                                   


AccNo    AccName               July       August    September
4-0000  In          
4-0500  Ad                    $4,514.06     $4,521.23   $4,296.62
4-1000  Me                    $37,085.78    $35,676.20  $36,150.87
4-1300  Ro                    $11,876.20    $10,956.70  $11,819.04
4-2000  Gr                    $57,540.76    $52,978.63  $54,272.24
4-3500  B                     $0.00         $0.00       $0.00
4-4000  P                     $3,576.76     $4,110.66   $2,208.31
4-5000  Be                    $19,559.94    $17,926.14  $18,260.71
4-6000  Tu                    $13,471.97    $15,042.04  $13,150.06
4-8900  Re                    $3,500.00     $0.00       $0.00
    Total Income              $151,125.47   $141,211.60 $140,157.85

5-0000  Cost of Sales           

    Gross Profit            $151,125.47 $141,211.60 $140,157.85

6-0000  Expenses            
6-1000  General & Administrative Exp            
6-1050  Accounting Fees            $0.00    $0.00   $270.00
6-1200  Bank Charges              $11.20    $11.20  $14.99

1 个解决方案

#1

i can pick up the trader and FYear from that excel sheet by using OLE DB Source but i don't actually know how to insert that data to each column of that table like above

我可以通过使用OLE DB Source从该excel表中获取交易者和FYear但我实际上并不知道如何将该数据插入该表的每一列,如上所述

Use your existing method to populate two SSIS variables with the trader and FYYear at the start. Then subsequently in your data flow use a derived column transform and insert the variable value in as an additional column.

使用现有方法在交易者和FYYear的开头填充两个SSIS变量。然后在您的数据流中使用派生列转换并将变量值作为附加列插入。

Based on the image you sent:

根据您发送的图片:

1. Delete everything except the first ForEachLoop container and its contents

   删除除第一个ForEachLoop容器及其内容之外的所有内容

2. Inside this ForEachLoop container add an execute SQL task which runs select * from [Sheet1$a1:a1]. This should get the Fiscal Year (if it's in that cell) and you should be able to load that result into a single string variable (which you need to create beforehand). You need to set it up as ResultSet=SingleRow and you need to map the resultset area. You do not need to use the parameter mapping area

   在这个ForEachLoop容器中添加一个执行SQL任务,它从[Sheet1 $ a1:a1]运行select *。这应该是财政年度(如果它在该单元格中)并且您应该能够将该结果加载到单个字符串变量(您需要事先创建)。您需要将其设置为ResultSet = SingleRow,并且您需要映射结果集区域。您不需要使用参数映射区域

See here more more info on how to load a variable from a single row result: https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-execute-sql-task-to-generate-result-sets/

有关如何从单行结果加载变量的更多信息,请参阅此处:https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-execute-sql-task-to-generate -result套/

3. Now in your existing Excel Data Source data flow, add a derived column transformation and include these string variables as extra columns that will be inserted into the table

现在,在现有的Excel数据源数据流中,添加派生列转换并将这些字符串变量包含为将插入表中的额外列