Post reply

SSIS C# Script Question

  • March 25, 2023 at 6:26 am

    #4164860

    Hi everyone

    I am working on a SSIS package where I am automating data collection and table updates.  My script is not performing as expected.  I am not sure how I can revise it in a way that doesn't make the script too complicated.  I am hoping there is an efficient way to fix it.

    Here is the background...

    The script runs two SP - one that imports a CSV into table Table1 and the other updates Table2 with the name of the CSV that was imported.  Table2 stores the names of the files that have previously been imported into Table1.  If a CSV was previously loaded then its name appears in Table2.  If the file name appears in Table2 then Table1 should not be updated with the new CSV import b/c it would be a duplicate record.

    Either both Table1 and Table2 get updated or neither of them get updated.  That is why I used the Begin Transaction Commit block.

    Once the file is downloaded it gets put in to the To_Be_Processed folder.  Once processing is done then the file gets moved to Processed folder.

    Problem case...

    Suppose Table2 says that a file File1 was never loaded yet File1 is found in the Processed folder.  This is an odd case but it could happen.  In this case neither Table1 nor Table2 should be updated.  This test case failed for me.

    Possible solution...

    1.  Add a piece of code to the if (File.Exists(filePathDestination + fileName)) block where it throws an exception and
    2.  Move the transaction.Commit() code either after the above if block or perhaps inside the if block

    Question...

    How would I force the script to throw an error in the if block if the file to be processed is already found in the destination folder?  I checked google and couldn't find an existing exception type that would work.  There are solutions re creating a new class etc but that seems complicated so I am hoping there is an efficient way to fix this issue.  I am open to all suggestions.

    Thank you

    • This topic was modified 1 year, 8 months ago by  water490.

  • March 25, 2023 at 6:02 pm

    #4165029

    Maybe throw a general exception and write your own error message.  Or the method returns void so maybe just "return;"?

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • March 25, 2023 at 6:09 pm

    #4165031

    Just scanning the code and there are nested Try/Catch and a transaction is declared but rollback only appears in 1 of the TRY code blocks.  That's concerning imo.  Could you not do the whole thing in SQL?  Why not pass the list of filenames to SQL?

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • March 26, 2023 at 11:28 am

    #4165143

    I think I'd switch the logic around a bit.

    1. Does filename exist in table2?

      1. Yes – throw an error
      2. Else continue

    2. Truncate staging table
    3. Import filename to staging table
    4. Execute a proc (put your BEGIN TRAN/ ROLLBACK / COMMIT etc in this proc)

      1. Transfer data from staging table to table1
      2. Update table2

    5. Were there any errors in (4)

      1. Yes – send alert / move file to 'problem' folder ...
      2. No – archive the file

    No need to worry about transactions until point (4)

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

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

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