x

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
more ▼

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

ryanwei88 gravatar image

ryanwei88
10 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort oldest
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.
more ▼

answered Apr 27 '11 at 11:49 PM

Valentino Vranken gravatar image

Valentino Vranken
1.5k 1 2 7

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Apr 28 '11 at 12:01 AM

aRookieBIdev gravatar image

aRookieBIdev
2.3k 42 54 58

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Apr 29 '11 at 01:58 PM

ryanwei88 gravatar image

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x900
x109

asked: Apr 27 '11 at 03:29 PM

Seen: 2020 times

Last Updated: Apr 27 '11 at 03:29 PM