SSIS Get List of Missing Files

usertXBH 0
Viewed 20

I get a monthly submission of files from various companies that get loaded into SQL Server via an SSIS job. When the SSIS job runs I want to get a list of companies that did not submit a file. The files will have the date appended to the end so I'm assuming it will need to do some sort of wild card search through a list of names. If I'm expecting:

AlphaCO_File_yyyymmdd

BetaCO_File_yyyymmdd

DeltaCO_File_yyyymmdd

ZetaCO_File_yyyymmdd

and the file from ZetacO is missing I want to write ZetaCO to a table, or save it in a variable I can use in an email task. I am using Visual Studio 2019 and SQL Server 2019. I have the Task Factory add-on for SSIS.

ssis c#
1 Answers

Note this answers uses psuedo code that needs to be tuned for your specific values.

My guess is that you already have a foreach loop set up where you are reading the file name to a parameter.

What you need is a table in SQL Server to compare against.

CompanyName, FileSubmitted (bit)
AlphaCO
BetaCo
DeltaCo
ZetaCO

First step is SQL command: udpate table set FileSubmitted = 0.

Then in each for loop have one path to update that table based on file name:

Use token or use C# task. c# would be company = fileName.Split('_')[0];

And then update the table:

update table 
set FileSubmitted = 1 
where CompanyName = company

Now you can use that table for emails.

KeithL0
Related