Post reply

Best practice to import from flat file and manually update a field at the same time?

  • December 29, 2008 at 6:14 am

    #128861

    Hi!

    I am importing from a flat file to database. What is the best way to update a field in this table at the same time?

    The table has a column "IMPORTED". I want automatically set this field for each imported dataset from the flatfile to "YES".

    Any tipps please?

    /gü

  • December 29, 2008 at 7:47 pm

    #918722

    Sorry, Guenther... I don't use SSIS so I haven't a clue. But my response will bring this back to the front of the class so folks can try to help again. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 29, 2008 at 11:59 pm

    #918783

    A few things you might try:

    If you will be doing a lot of transformations in the data flow pipeline in addition to adding the Imported column you could use a derived column transformation in your data flow. Just add a new column called Imported and set the value to YES, then it can be mapped to the target table along with all of the columns from the flat file.

    You could add a default constraint to the Imported column on your target table.

    If you just want to get the file into a table and you don’t need to do a lot of transformations you might want to use bulk insert or BCP. SSIS has a bulk insert task or you could bulk insert via an execute SQL task. That way you still have the logging and control flow options of the SSIS package but can load the file without building a data flow task.

    http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/ssisperfstrat.mspx#EKF

    You could use openrowset (bulk ) and add the Imported column to the select statement. Create that as a stored procedure and call it from an execute SQL task in the package, or use it as a data source in a data flow task.

    http://msdn.microsoft.com/en-us/library/ms175915.aspx

    Hope those give you some ideas….

  • December 30, 2008 at 1:06 am

    #918827

    Thank you very much for you help!

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

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