SQL Server Performance

Read Last row in a flat file and row count in SSIS

Discussion in 'SQL Server 2005 Integration Services' started by madduri, Jan 24, 2008.

  1. madduri New Member

    Hi,
    I need to load data into a table from a flat file. The last row in the flat file contains information about the total number of rows in the file. Is there a way to read the last row in the flat file and match the number of rows that are loaded into the file. I want to make sure that all the rows have been inserted and want to raise an error when the number doesn't match.
    In this package that i created i truncate the table first and then load the data. If the load fails the table will remain empty. I would like to retain the old data if the load fails. How can I do this? I am new to SSIS...any help with this is highly appreciated.
    Thanks in Advance...Madduri
  2. dineshasanka Moderator

    First let me answer your second question,
    Better way or doing this is , rather than truncating log your data to the some other table and if no errors truncate the tabel if errors just copy those data the your table.
    for ur first question, it seems that you have different formatted test files. http://204.9.76.233/articles/dba/import_text_files_ssis_p1.aspx article tells you how to deal with such issues.
    Hope you got some light into your problem
  3. madduri New Member

    HI,
    Thank you for the reply. I am actually trying to insert the data into a temp table before truncating and insert back this data into the table from the temp table and drop temp table if the load fails and drop the temp table on success. but the package fails when executed saying 'invalid object name "#temptable"'. Even delaying validation is not working. any work around??
    Thanks, Madduri
  4. madduri New Member

  5. dineshasanka Moderator

    Set DelayValidation=TRUE on the data-flow task.

Share This Page