Search code examples
sqlsql-serverssisssis-2012

How to UPDATE all columns in a Table from FLAT FILE Source

My DB Table does not Update columns from an OLE DB Destination, I need to UPDATE all columns in an OLE DB Destination table that I get from a FLAT FILE Source (.csv).

The .csv file data inside changes everyday, so when i execute the DATA FLOW, the FLAT FILE Source updates fine, but the data in the OLE DB Destination does not update, so the DB Table never updates data.

Should I configure an OLE DB Command ?

So this is what I have and how I have set it up.

  1. I'm using SQL Server 2012 STD

  2. I have a SSIS Project in Visual Studio.

  3. DATA FLOW has been set up.

  4. Inside DATA FLOW I have FLAT FILE Source that connects to an OLE DB Destination. (works fine)

  5. All connection managers setup and working fine.

  6. Tables has been created in Management Studio already.

If i need to use an OLE DB Command, should it be something like this:

UPDATE table
SET column1=?, column2=?, column3=?
WHERE column1=?, column2=?, column3=?

Here is my actual query i need to use, will this work ?


set 
candidateName=?, 
candidateFirstname=?,
candidateLogin=?,
candidateEmail=?, 
candidateRefNumber=?, 
candidateGuid=?,
candidateEntityName=?, 
'M-Sure Client Engagement Specialist'=?,
'Area Sales Manager'=?, 
Region=?, 
'Group Name'=?, 
'FSB License Holder'=?,
'Agent Name'=?, 
trainingId=?,
trainingTitle=?,
trainingGuid=?, 
sessionId=?,
sessionTitle=?,
sessionGuid=?,
sessionStartDate=?, 
sessionEndDate=?,
contentGuid=?, 
contentRefNumber=?,
contentTitle=?,
firstLaunchDate=?,
completionTime=?, 
score=?, 
timeGlobal=?, 
progression=?, 
status=?

WHERE

candidateName=?, 
candidateFirstname=?,
candidateLogin=?,
candidateEmail=?, 
candidateRefNumber=?, 
candidateGuid=?,
candidateEntityName=?, 
'M-Sure Client Engagement Specialist'=?,
'Area Sales Manager'=?, 
Region=?, 
'Group Name'=?, 
'FSB License Holder'=?,
'Agent Name'=?, 
trainingId=?,
trainingTitle=?,
trainingGuid=?, 
sessionId=?,
sessionTitle=?,
sessionGuid=?,
sessionStartDate=?, 
sessionEndDate=?,
contentGuid=?, 
contentRefNumber=?,
contentTitle=?,
firstLaunchDate=?,
completionTime=?, 
score=?, 
timeGlobal=?, 
progression=?, 
status=?```
Solution

  • Yes, in order to UPDATE existing rows in a table, you have to use an OLEDB Command, not an OLEDB Destination, and yes, the way you propose to do it is correct.