none
Need to comapre date and update the table? RRS feed

  • Question

  • Hi,

    I have requirement , which reads data from a CSV file, and checks if the same data exist on the table it needs to upadate a flag.

    please let me Know how i can go abt it

    Cheers,

    Pradeep

    Wednesday, November 3, 2010 6:32 AM

Answers

  • UPDATE       dbo.Conformed_Dim_Vendor
    SET                IsSourceableSupplier = 'Y'
    WHERE        (dbo.Conformed_Dim_Vendor.Enriched_Supplier_Name = non_supplier);


    you should put parameter markers in the update query, parameter markers are question marks ( ? )

    for example look at this sample sql statement:

    update myTable set field1=? where id=?

    when you write question marks in sql statements, ssis will translate it as parameters, and then you can map input column with these parameters.


    http://www.rad.pasfu.com
    • Marked as answer by Pradeep M H Wednesday, November 3, 2010 10:43 AM
    Wednesday, November 3, 2010 9:22 AM
    Moderator

All replies

  • add a data flow task

    add a flat file source pointing to the csv file

    add a lookup transform and configure it to the sql server table

    then get output of lookup transform to an OLEDB Command, and write the update command there, like:

    update myTable set field1=? where id=?

    you can use parameter mappings tab to map input column to parameters of this command


    http://www.rad.pasfu.com
    Wednesday, November 3, 2010 6:40 AM
    Moderator
  • Not sure if I got the question but you want to compare the data in the file and table and based upon that you want to update a flag in the table. If yes then you can use file as source and table as lookup table and make the flag column as lookup column in lookup editor. Use an OLEDB Command to update the flag based on some key column. You can also use set based update by taking all the appropriate columns in a staging table and do a set based update using execute sql task.

    PS: Where is the date as you have mentioned in the thread header?


    Nitesh Rai- Please mark the post as answered if it answers your question

    Wednesday, November 3, 2010 6:42 AM
  • Reza,

    I tried as you have mentioned above.

    Unable to complete column mapping and input and output properies while using OLE DB command.

    have written the update statment as well but in the where condition I amunable to get the input from lookuptransform

    Wednesday, November 3, 2010 7:42 AM
  • Perhaps it makes sense to move all data into SQL Server staging table and  then using MERGE command update the flag, what do yo think?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, November 3, 2010 8:03 AM
  • what is your update command ? could you paste it here?
    http://www.rad.pasfu.com
    Wednesday, November 3, 2010 8:03 AM
    Moderator
  • UPDATE       dbo.Conformed_Dim_Vendor
    SET                IsSourceableSupplier = 'Y'
    WHERE        (dbo.Conformed_Dim_Vendor.Enriched_Supplier_Name = non_supplier);
    Wednesday, November 3, 2010 8:31 AM
  • Hi. where is your variables, the identifier to get a single row from updatable table?


    Víctor M. Sánchez García (ES) (BI) Hope this help. Please vote if you find this posting was helpful. if this is an answer to your question, please mark it.
    Wednesday, November 3, 2010 8:58 AM
  • UPDATE       dbo.Conformed_Dim_Vendor
    SET                IsSourceableSupplier = 'Y'
    WHERE        (dbo.Conformed_Dim_Vendor.Enriched_Supplier_Name = non_supplier);


    you should put parameter markers in the update query, parameter markers are question marks ( ? )

    for example look at this sample sql statement:

    update myTable set field1=? where id=?

    when you write question marks in sql statements, ssis will translate it as parameters, and then you can map input column with these parameters.


    http://www.rad.pasfu.com
    • Marked as answer by Pradeep M H Wednesday, November 3, 2010 10:43 AM
    Wednesday, November 3, 2010 9:22 AM
    Moderator