question

Mason Ghafoori avatar image
Mason Ghafoori asked

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.
sqlssisexcelvariableexecute
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Daniel Ross avatar image
Daniel Ross answered
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,
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
+1 - especially for the last section (set based = win)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.