Flat file and extra data column

  • Hi,

    i would like to import a flat file to a table but while importing data i would like to add extra column to the imported data (in staging area) which contains todays date or file creation date

    I started using Data flow task but couldnt move further.please suggest on how can i achieve this

    Thanks

  • Your 15 second solution is not SQL related at all:

    download microsoft logparser and run it on your data.

    if, for example, your data is in CSV file, you can run the following command:

    LogParser "SELECT *, TO_STRING( SYSTEM_TIMESTAMP(), 'yyyy-MM-dd hh:mm:ss.ll' ) AS ImportedOn INTO MyNewTable FROM c:\FlatFile.csv" -i:CSV -o:SQL -server:MyServer -database:MyDatabase -driver:"SQL Server" -username:TestSQLUser -password:TestSQLPassword -createTable:ON -clearTable:ON

    Regards

    Tal Ben Yosef

  • benyos (8/29/2009)


    Your 15 second solution is not SQL related at all:

    download microsoft logparser and run it on your data.

    if, for example, your data is in CSV file, you can run the following command:

    LogParser "SELECT *, TO_STRING( SYSTEM_TIMESTAMP(), 'yyyy-MM-dd hh:mm:ss.ll' ) AS ImportedOn INTO MyNewTable FROM c:\FlatFile.csv" -i:CSV -o:SQL -server:MyServer -database:MyDatabase -driver:"SQL Server" -username:TestSQLUser -password:TestSQLPassword -createTable:ON -clearTable:ON

    Regards

    Tal Ben Yosef

    Huh? What does that do? The five-minute solution is to add a suitable derived column transformation & set it to the required date using an expression. Then map this new column to the appropriate field in the staging area.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • I know the SQL way to achieve this.... however, this is a 10 seconds solution for people who know their way around data pumps, like me and you.

    I referred to the following line:

    "I started using Data flow task but couldnt move further.please suggest on how can i achieve this "

    🙂

    Tal Ben Yosef

  • OK, now that I have spent a bit more time on your solution, I can see what it's doing. Apologies for my previous post, but I haven't seen a solution like that before - good to know, thank you!

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • why not just put an extra column in your destination table and give it a default of getdate(). that way you wont need to change your current SSIS job because no new columns need to be mapped. I can see why the logparser approach will work as i have used it myself to import windows security logs into SQL, however i personally dont think its the best approach to the OP's question.

    Feel free to shoot me down tho 🙂

  • Hi there,

    I tried derived column and that worked,thanks very much.i would like to set the flatfile source which is to a dynamic file which has a value of foreach iteration variable

    Please suggest on this

    Thanks

  • Hi,

    Sorry for sequence of ?'s

    I can dynamically map the flat file source value to the variable value but i get metadata error when i run it

    [Flat File Source 1 [3441]] Error: The column data for column "Column 35" overflowed the disk I/O buffer.

    Thanks for the help

  • Do all of your flat files have the same schema?

    What is in column 35? This is a tough one to answer without knowing a bit more about your data ...

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hi,

    Thanks for the reply,every file has different schema, some of them straight way work and some them break

    current datatype of columns is set DT_STR with a length of 250

    I get a error of external metadata column collection is out of synchronization with the output columns

    please suggest on this

    thanks

  • SSIS is very metadata dependant. Each source, destination, and the transformations that you put in between them rely heavily on the metadata that SSIS gets from the source and destination components. Long story short, you can do what you are trying to do with the same source component. You need to set up a different data flow for each file type that you have to import.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • John meant can't, not 'can'!

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Good catch Phil. You are absolutely correct, I meant can't. Thanks for catching me!

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Viewing 13 posts - 1 through 12 (of 12 total)

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