locked
Update table from CSV file RRS feed

  • Question

  • Hello,

    I am new to ssis and I wanted to use ssis for this process.

    I want to update 2 fields in a table based off of an email address from a csv file and then create a log file that has the email addresses of the ones updated.

    The fields values are 1 or 0 if ether one is 1 I need to update the record to 0 by email address in the file.

    Again I am a beginner and I do want to learn and use ssis for this so baby steps are welcome.

    Thanks,

    Keith.


    keith

    • Moved by Katherine Xiong Thursday, November 13, 2014 2:39 AM it is related to SSIS
    Tuesday, November 11, 2014 8:04 PM

Answers

  • Hi keith113,

    Based on your description, you want to update 2 fields in a table with data from a CSV file and log the 2 fields modification by using Microsoft SQL Server Integration Services(SSIS), right?

    Since you didn’t give any detail about your table and CSV, according to my blurring understanding, I make the below assumptions.

    1. There’s a table in your database which contains an email field and two fields that need to be updated according to the data in your CSV.
    2. The CSV contains an email field. The data in the CSV and the table is related by the email field.
    3. The two fields values are 1 or 0. If ether one is 1, update the 2 fields to 0 by email address in the file.


    Please see the snapshot in my scenario based on the assumptions.
    Scenario:     

    Finally the data flow task in SSIS looks like below. I will show you what each step to do.

    Steps:

    1. Flat File Source.
      A Flat File Source is used to load the data from the CSV. Double click on it and follow the below picture.
    2. OLE DB Source
      A OLE DB Source is used to load data from the table which needs to update.
    3. Merge Join
      A Merge join is used to join the data from CSV and table. You can choose how the data joins based your own requirement. Click Join for more details. In my scenario, left outer join is selected.
    4. Conditional Split
      A Conditional Split is used to filter the join result from 3. Merge join. In my case, I filter out the records with null email address after join.
    5. OLE DB Command
      A OLE DB Command here is used to run the update statement to update the 2 fields in the table. In my case, the update statement is like below and update my table.
      update TestTb set field1=0,field2=0 where emailaddress=? and (field1 !=0 or  field2!=0)

    6. Flat file Destination
      A Flat file Destination here is to save the records that have got updated, namely log the data modification. You could customize the data to be saved.

    Above are the steps in my case, for more detailed and complicated scenario, you could see the below link:
    http://www.codeproject.com/Tips/526833/SSIS-Load-Data-in-Dimension-Table-by-Merge-Join

    If you have any question, feel free to let me know.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support


    Thursday, November 13, 2014 2:36 AM

All replies

  • Dear Keith:

    Can you put an example of your data table and your CSV.

    Thanks 

    Claudio

    Tuesday, November 11, 2014 8:53 PM
  • You need to load the data first into a staging table and use SQL's to do an update.

    Please refer the below link

    http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

    http://stackoverflow.com/questions/23026501/bulk-insert-with-csv-file-in-sql-server

    --Prashanth

    Tuesday, November 11, 2014 9:25 PM
  • Hi keith113,

    Based on your description, you want to update 2 fields in a table with data from a CSV file and log the 2 fields modification by using Microsoft SQL Server Integration Services(SSIS), right?

    Since you didn’t give any detail about your table and CSV, according to my blurring understanding, I make the below assumptions.

    1. There’s a table in your database which contains an email field and two fields that need to be updated according to the data in your CSV.
    2. The CSV contains an email field. The data in the CSV and the table is related by the email field.
    3. The two fields values are 1 or 0. If ether one is 1, update the 2 fields to 0 by email address in the file.


    Please see the snapshot in my scenario based on the assumptions.
    Scenario:     

    Finally the data flow task in SSIS looks like below. I will show you what each step to do.

    Steps:

    1. Flat File Source.
      A Flat File Source is used to load the data from the CSV. Double click on it and follow the below picture.
    2. OLE DB Source
      A OLE DB Source is used to load data from the table which needs to update.
    3. Merge Join
      A Merge join is used to join the data from CSV and table. You can choose how the data joins based your own requirement. Click Join for more details. In my scenario, left outer join is selected.
    4. Conditional Split
      A Conditional Split is used to filter the join result from 3. Merge join. In my case, I filter out the records with null email address after join.
    5. OLE DB Command
      A OLE DB Command here is used to run the update statement to update the 2 fields in the table. In my case, the update statement is like below and update my table.
      update TestTb set field1=0,field2=0 where emailaddress=? and (field1 !=0 or  field2!=0)

    6. Flat file Destination
      A Flat file Destination here is to save the records that have got updated, namely log the data modification. You could customize the data to be saved.

    Above are the steps in my case, for more detailed and complicated scenario, you could see the below link:
    http://www.codeproject.com/Tips/526833/SSIS-Load-Data-in-Dimension-Table-by-Merge-Join

    If you have any question, feel free to let me know.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support


    Thursday, November 13, 2014 2:36 AM
  • If its a simple update you may be better off using distributed queries like OPENROWSET

    https://sqlmate.wordpress.com/2012/08/09/use-your-text-csv-files-in-your-queries-via-openrowset/


    Please Mark This As Answer if it solved your issue
    Please Mark This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, November 13, 2014 7:46 AM