x

SSIS excel field to variable

How can i read one row at a time from an excel and pass the value from a specific column into Execute SQL task, without loading the excel file into destinaiton.
more ▼

asked Sep 17 '10 at 04:30 PM in Default

Mason Ghafoori gravatar image

Mason Ghafoori
34 4 4 5

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

1 answer: sort newest

Hi Mason,

Your data flow does not have to have a destination to run, it can terminate at a OLEDB command.

Add a data flow component to your control flow. Then in the data flow, add an Excel Source component.

Open the Excel Source, then create a new excel connection. Browse to the file path, set the excel version and specify if the first row has columns (This is for ssis 2005, may be different for 2008).

Click OK to create the connection, then click on the "Name of the Excel Sheet" to select the sheet from the spreadsheet. Click on Columns from the left pane to select the columns to output.

Now create an OLEDB connection to your database you want to run the command on. Then add a OLEDB command control to the data flow, connect the data pipe from the excel source and open the OLEDB command.

On the Connection Managers tab and select the connection you just created. Then click on Component Properties. In the SQLCommand type your command like;

Update table set value=? where ID=?

Then click on Column Mappings, and you should have input and destination columns, the input columns are from the excel spreadsheet and the destination columns are from the query above and they might be in the format Param_0 and Param-1 etc. Match the excel columns to the destination columns and click OK.

If the OLEDB command component has an error like "Cannot convert between Unicode and non-unicode string data types" then you will have to add a Data Conversion Component between the excel and OLEDB command to convert the Unicode string [DT_WSTR] to a string [DT-STR] data type. (If your destination is VARCHAR you will have to convert it, if it is NVARCHAR you will not)

That should be it, of course, test this. Just one point though, keep in mind that doing a row by row update on a database can put load on a server. I always import the full data into a temp table, then do a seperate set based command on the data,
more ▼

answered Sep 19 '10 at 07:04 PM

Daniel Ross gravatar image

Daniel Ross
2.9k 10 12 13

+1 - especially for the last section (set based = win)
Sep 20 '10 at 12:19 AM WilliamD
(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:

x899
x672
x108
x26
x16

asked: Sep 17 '10 at 04:30 PM

Seen: 2846 times

Last Updated: Sep 17 '10 at 04:30 PM