Read from Excel and update to SQL 2005 DB using Script Task

Question

Read from Excel and update to SQL 2005 DB using Script Task

Suresh Arumugam

Hall of Fame

Points: 3370
More actions
February 2, 2009 at 4:25 am

#134306

Hi,
I am new to SSIS and I would like to know how to achieve my below requirement.
I have to read data from one Excel file and by using that data I have to update a particular table in Sql 2005 DB. How can I do this is SSIS?
1. Should i have to use the Data Flow task and try for Excel source and OLEDB destination? I guess this way i can just read and insert directly (may be column mapping) to the table. May not be able to use update queries.
2. or should I have to use Script task and by using the connection to read from excel file? If so, would i require Variables? Could you please provide some sample codes for this?
Thanks in advance,
Suresh
Regards,
Suresh Arumugam

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

There are mainly two basic ways you can do it...

1. Create 1 Data flow task, 1 Excel Source Component & 1 OLE DB Command (Transformation) Component (that contain the update query with parameters) to do a row-by-row action on the target.

2. Create 1 Data flow task, 1 Excel Source Component, 1 OLE DB Destination Component & 1 Execute SQL Task to update all rows in a single query

Personally, I prefer doing a single update instead of multiple row-by-row updates.

--Ramesh

Suresh Arumugam Hall of Fame Points: 3370 More actions · Answer 2

Thanks for the reply Ramesh..

As per my understanding, If I use Excel source and OLEDB destination, all rows from Excel will get inserted into my destination table which I should avoid.

For example, I have an excel file which has a list of countries and their conversion rates. I need to read that data and update Conversion values in DB table by matching the Countrynames (with Excel value and DB value) and also should extend the where condition like Org_TYPe = "Country"

Ex query:-

Update Orgtable set conversionrate = and Org_Type = "Country"

How ever, Your suggestion would look like,

Excel Source -> OLEDB Destination -> Execute Sql task.

Could you please explain how the above step will solve my requirement?

Thanks,

Suresh

Regards,
Suresh Arumugam

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

Actually, you didn't get my point. Let me put this way....

Suresh Kumar (2/2/2009)
As per my understanding, If I use Excel source and OLEDB destination, all rows from Excel will get inserted into my destination table which I should avoid.

You can dump the data into a staging temporary user table, and then use a single update in Execute SQL Task (as said in Point(2) of previous post)

Excel Source -> OLEDB Destination -> Execute Sql task.

It should read as Excel Source > OLEDB Command Component (and not OLEDB Destination, its available in Transformations). This is same as point 1 of previous post..

--Ramesh

Suresh Arumugam Hall of Fame Points: 3370 More actions · Answer 4

Thanks a lot Ramesh..

I have done it using Excel Source and OLEDB Command. I chose OLEDB command since I usually have a less no. of records in my Excel source file.

Suresh

Regards,
Suresh Arumugam

vishal.gamji SSCertifiable Points: 6126 More actions · Answer 5

You could have used a merge join to join the Excel data source and add another data source (sql srvr) where you have your countries source. Join on the CountryKey, add a conditional split with expression. then based on a conditional split you could achieve what you want do (insert or update).