How to update SQL table with CSV data?

Question

I am trying to update one of my SQL tables with new columns in my source CSV file. These CSV records in this file are already in my SQL table, but they are lacking some of the columns from this CSV file.

I already added the new columns to my SQL table. But now I just need to import the data from the new columns. How can I do this best?

I am trying to use SSIS 2008 to do this. I created a Flat File CSV Data Source. Then I added an OLE DB command. And I set the SQL command as follows:

update berkeley.dbo.j5c_ALL_DATA 
set disabilitycode=[Stu Pri Dis],
IEPID=SpecED
,[MT_AR_CR]=[Met At-Rsk Crit]
      ,[ECP]=[Early Childhood Program]
      ,[REF_CFC]=[Ref by CFC]
      ,[IEP_COM_DT]=[IEP Cmp Date]
      ,[E1_NUM]=[EI Nbr]
      ,[RDIT]=[Reason for Delay in Transition]
      ,[MT_PFA_HI]=[Met PFA Inc Crt]
      ,[FS]=[Family Structure]
      ,[Hm_Sch_RCDTS]=[RCDTS Home]
      ,[Srv-Sch_RCDTS]=[RCDTS Serve]
from berkeley.dbo.j5c_ALL_DATA A join Berkeley_CSV2 b ON
A.studentid = B.[SAP ID] and A.BegEnrollDate = B.[Enroll Date]

where Berkeley_CSV2 is the name of my CSV flat file connection manager. But on this I get an error:

The connection manager "Berkeley_CSV2" is an incorrect type.  The type required is "OLEDB".  The type available to the component is "FLATFILE".

Can u either recommend to me an alternative way of adding this data or else a different SQL command?

Answer 1

The best way to handle this is in multiple steps. Don't throw away your current work; it will fit in as part of a different solution.

First, create a staging table in your database. This will be a intermediate, and temporary, location for the data from the flat file. To simplify subsequent steps, it may be best to create a column for each field in the flat file, even though some of the data will not be needed. Create the table before you get back to the SSIS design.

In SSIS, create a OLE DB connection manager to your database and another to your flat file. You probably already have this in place, given the information in your question.

Create a Data Flow task. In the Data Flow task, create a Flat File Source and link it to your flat file. Create an OLE DB Destination and link it to your staging table. Map a data flow path from the Source to the Destination.

The next step is to adapt your OLE DB Command to work with data in the new table. In the Control Flow workspace create a Precedence Constraint (line between two tasks) from the new Data Flow task to your existing OLE DB Command. This will make the Data Flow run first, followed by the OLE DB Command.

Modify your SQL script in the OLE DB Command, so that it references the new table and associated column names. The script should not reference the file. I don't think there are any other changes needed in the SQL script.