Post reply

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

  • 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

  • February 2, 2009 at 5:07 am

    #936345

    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

  • February 2, 2009 at 5:35 am

    #936350

    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

  • February 2, 2009 at 6:44 am

    #936384

    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

  • February 3, 2009 at 6:10 am

    #937055

    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

  • February 3, 2009 at 7:21 am

    #937108

    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).

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

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