Updating a number of records with SSIS

Question

Updating a number of records with SSIS

ByronOne

SSCarpal Tunnel

Points: 4656
More actions
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

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

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

robert.gerald.taylor SSChampion Points: 13133 More actions · Answer 1

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

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.

Ian C0ckcroft Hall of Fame Points: 3373 More actions · Answer 3