Post reply

SSIS - create Connection Manager at runtime, conditionally

  • March 30, 2020 at 5:00 pm

    #3739037

    I want to create an SSIS pkg that

    • tests for existence of Excel file
    • Then

      • If file exists, do stuff, then delete file
      • If file doesn't exist, do nothing

    Problem is that if I have a connection manager that expects that file, the SSIS pkg throws an error at runtime, at startup.

    I will run this as scheduled task and have considered breaking out the file detect to a first step, but then would still need to be kludgy to make failure happen.

    Another option is detect file in a C# project and if exists, call the SSIS pkg from there, then delete the file.

    Any thoughts on best way to do this?

  • March 30, 2020 at 5:10 pm

    #3739044

    There's are a couple of tricks to get round these problems.

    • Put your dataflow inside a FOREACH container, with the enumerator is set to 'File' and the 'Enumerator Config' pointing to the file you wish to process.

    The dataflow will execute only if that file is found. If not, the package completes (successfully) without doing anything inside the container.

    • To prevent the validation error, set the Connection Manager's 'Delay Validation' property to True.

    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

  • March 30, 2020 at 7:40 pm

    #3739084

    Beautiful, thanks.

    Note: In addition to your directions, I also had to set "Delay Validation" to true for the pipeline object that led to the Destination object.

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

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