SSIS Excel fields into variables and populate them into one row in database

I have a Excel file which I need to load each field into a single row in a database table. the fields are located like C3:C3, E2:E2, F7:F7 etc. you get the idea. How do I save the values in each field into variables and insert them into a single row? Please help.

Thanks

asked Apr 27 '11 at 03:29 PM in Default

ryanwei88
10 ● 1 ● 1 ● 1

(comments are locked)

3 answers:

thank you very much for helping me out. I also feel Script Component will do the trick. But how do I get value from Excel into the variable? a sample code will be helpful.

Thanks

answered Apr 29 '11 at 01:58 PM

ryanwei88
10 ● 1 ● 1 ● 1

Never mind. I figured it out. Here is a very useful link: http://www.bimonkey.com/2009/09/the-script-transformation-part-1-a-simple-transformation/

Apr 29 '11 at 02:28 PM ryanwei88

(comments are locked)

DFT task which has:

Excel Source:To read the excel Script Component: assign values to the varaiables Oledb Destination: Dynamically form the sql query using the variable value.

answered Apr 28 '11 at 12:01 AM

aRookieBIdev
2.1k ● 25 ● 43 ● 48

(comments are locked)

One possible solution that I see is by using VSTO (Visual Studio Tools for Office) from a Script Component in the Data Flow. That of course means that you'll need to write some .NET code to retrieve the values from Excel, then output them on the output fields that you need to define in the Script Component.

answered Apr 27 '11 at 11:49 PM