0

I have a SSIS 2019 Package which imports an Excel as soon as the file is placed to a folder and then moves the Excel file to another folder (agent will run it every 30 minutes). At the beginning I have a Script Task which tests if the file is in the expected folder, if true then the control flow goes to the Data Flow task, if not then it goes nowhere (process ends there). In testing the package it works fine so long as the file is in the expected folder when I open or do anything with the package. In testing when I run the package a second time, after the file has been moved, then the Control Flow doesn't go past the Script Task but I get an error from the Data Flow because in the folder a new empty file with the expected filename but incorrect sheet has been automatically created. I have no idea what task caused this or if the excel file connection manager did it at the moment the package was executed. It will even do this as soon as I open the package if the file is not in the folder. How do I stop this? I don't want it to create an empty excel file at all. I want it to just do nothing if that file is not in the designated folder.

Here's a complete sequence of events:

  1. Script Task checks if the file is in the folder – No: does nothing
  2. Yes – Executes SQL Task deletes records in Target Table One
  3. Then goes into Data Flow a.Excel Data Source goes into Data Conversion Task b.Then data goes into OLE DB source (Target Table One)
  4. Exists Data Flow – On Error sends email
  5. Then Executes SQL Task which – On Error sends email a. Deletes records in Target Table Two b. Inserts records into Target Table Two which do not have a null part number c.Inserts a record into a Log History Table
  6. Then Executes a File System Task which moves the Excel file from IMBOX folder to OUTBOX folder – On Error sends email – On Success sends email
Mike Myers
  • 33
  • 1
  • 7

1 Answers1

0

The solution that worked was to move the Script Task (if file in folder query) to a seperate solution and add to it an Execute Package Task, setup as an external reference from file system, the original package .dtsx file. It runs perfectly from within Visual Studio as well as from the Agent in management studio. The Agent will be scheduled to run ever 5 minutes and most of the time there will be no Excel file to import, and no error will be generated. Then the user places the file into the folder then within 5 minutes it will be processed into the ETL database, Excel file will be moved to an outbox folder and user will receive an email indicating the process completed or failed.

I am sure there has to be another solution for preventing the Data Flow Task from creating an Excel data source when it isn't there when the package is opened. But the above solution definitely works.

Mike Myers
  • 33
  • 1
  • 7