Post reply

Updating a number of records with SSIS

  • October 12, 2012 at 4:10 am

    #283014

    Hi everyone

    I have a table in sql called dbo.Contracts that contains a number of columns but in respect to this query the following: LicenceNumber, StartDate, EndDate, LengthWeeks

    I have recently run a report to show which of our Contracts have lasted longer than 26 weeks (using the LengthWeeks column). We discovered that both the StartDates and the EndDates in many cases were wrong. We asked our Contract Managers to enter the correct StartDate and EndDate in an Excel file. I've now been asked to alter the dbo.Contracts table with the revised information. I know how to do individual records using the update command in T-SQL but there are over 150 records to be amended and I wondered whether I'd be able to do this through SSIS? The LicenceNumber is unique in each case which presumably would be useful? Essentially what I need is a job which could change the StartDate, EndDate and LengthWeeks columns with the new information from the Excel file (ContractsUpdate.xls),

    Thanks in advance.

    BO

  • October 12, 2012 at 6:07 am

    #1548578

    ByronOne (10/12/2012)

    Hi everyone

    I have a table in sql called dbo.Contracts that contains a number of columns but in respect to this query the following: LicenceNumber, StartDate, EndDate, LengthWeeks

    I have recently run a report to show which of our Contracts have lasted longer than 26 weeks (using the LengthWeeks column). We discovered that both the StartDates and the EndDates in many cases were wrong. We asked our Contract Managers to enter the correct StartDate and EndDate in an Excel file. I've now been asked to alter the dbo.Contracts table with the revised information. I know how to do individual records using the update command in T-SQL but there are over 150 records to be amended and I wondered whether I'd be able to do this through SSIS? The LicenceNumber is unique in each case which presumably would be useful? Essentially what I need is a job which could change the StartDate, EndDate and LengthWeeks columns with the new information from the Excel file (ContractsUpdate.xls),

    At a high level, in your data flow task you are going to have two data sources -- one OLE Source to pull your dbo.Contracts data from SQL Server and the other an Excel source. Bring these two source together in a Merge Join component using the unique LicenseNumber to link the two sources. You may have to do some data type conversion on the fields originally from Excel. Then send this to an OLE_CMD component that updates the fields you are interested in.

    HTH,

    Rob

  • October 12, 2012 at 6:21 am

    #1548585

    Is this a one-off, or are you going to have to do it regularly? If you're only doing it once, I don't think I'd bother with SSIS. Here's what I'd do:

    (1) Create a temp table based on the columns in your spreadsheet

    (2) Use Excel to generate INSERT statements for the temp table

    (3) Run the insert statements

    (4) Do the update by joining the Contracts table to the temp table

    John

    Edit: corrected typo.

  • October 12, 2012 at 7:01 am

    #1548609

    similar to Johns solution.

    I'd pull the data from excel into a table(SSIS) and then run the merge or update.

    Ian Cockcroft
    MCITP BI Specialist

  • October 12, 2012 at 7:24 am

    #1548629

    Cheers guys.

    I will need to run this going forward so the SSIS suggestions look like they'll do the job.

    Thanks again.

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

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