I am developing a rather large ETL ( 39 distinct Flat File or Excel Connection Managers in current build & about as many to go ) through SSIS ( Visual Studio and MSSQL Server versions both 2008-R2 ). I'm trying to set full deployment for the first full "vertical slice" and am currently prod testing.

Each of the files comes in from its respective data vendor with its respective unique name suffixed with some string variation of the day's date.

Because I'm using Expressions for the Connection Managers ( variously for ServerName/ExcelFilePath - .xlsx, ConnectionString = .csv, and Disable for all Connections in case the file is missing ), I need to set robust defaults to configure & save the solution. The problem is this:

I am receiving false positives when I prod test the Server Job

I have a Script Task to assign Variable names for each necessary Expression prior to a Sequence Container with all of the Data Flow Tasks for the solution. A sample of this Script Task ( actually a few in sequence ) follows. The Variable format [User::StringDate] is assigned from an Execute SQL task prior to this step.

Dts.Variables["User::aFile"].Value = String.Empty;
Dts.Variables["User::bFile"].Value = String.Empty;


String DateFormat = String.Empty;
DateFormat = Dts.Variables["User::StringDate"].Value.ToString();

String DirLoc, FileName; 
DirLoc, FileName = String.Empty;

DirLoc = @"\\SharedDrive\e\Files\"
aFileName = "ExcelFileA_" + DateFormat + ".xls";
bFileName = "ExcelFileB_" + DateFormat + ".xls";

Dts.Variables["User::aFile"].Value = aFileName;
Dts.Variables["User::bFile"].Value = bFileName;


As I add Connection Managers for each new family of files, I have found myself occasionally forgetting to add the variable declaration into the script. Obviously, the job then continues to reference the default location that was set up to configure the connection manager and prod testing shows false positives when the daily file for that instance does not come in or has a DDL change I did not anticipate.

To ameliorate this, I've set a "Debug" script task prior to all other Precedence Constraints in the Package. This task manually set the ["User::*"] vars to the "incorrect" state ( String.Empty, false depending... ). However, this still requires that I hard-code the set empty step.

Is there a way ( perhaps in a Foreach Loop Container ) to set all User vars empty / null?

This would also assist in documentation of the job for my colleagues... Currently I'm just setting all Vars as ReadOnlyVariables in any given Script Task each day when I commit the day's versioning & copy-pasting the variable names into a text file, manipulating the syntax I need, and then copy pasting back into the Script Task.

I just feel like there's got to be a better way!

  • Ensuring I understand your logic: file name changes on a daily basis. Foo_20150923 for today's file, tomorrow's will be Foo_20150924 (or it lags a day, whatever) and so you have these script tasks built out to compute what the value should be? – billinkc Sep 24 '15 at 4:09
  • @billinkc correct – Peter Vandivier Sep 24 '15 at 4:19
  • If you only ever want yesterday's date, you can compute that using Expressions. Yesterday, datetime, expression is dateadd("dd", -1, @[System::StartTime]) YesterdayYMD, string, expression = (DT_WSTR, 4)YEAR(@[User::Yesterday]) + RIGHT("0" + (DT_WSTR, 2)MONTH(@[User::Yesterday]), 2) + RIGHT("0" + (DT_WSTR, 2) DAY(@[User::Yesterday]), 2) You would then use YesterdayYMD to build your file name variables, also via expression, aFile = "\\\\SharedDrive\\e\\Files\\" + "ExcelFileA_" + @[User::YesterdayYMD] + ".xls" – billinkc Sep 24 '15 at 14:01
  • I have found when I do the above, invariably the day comes where the vendor didn't supply the file in time or we have to reprocess multiple days worth of data. Then you're in a pickle because you think about things like changing the system clock just to get it to pick up the file. Or you try to rename the file but forget you have logic in place to ensure you only process one file per day. Something like that. – billinkc Sep 24 '15 at 14:03
  • 1
    So, instead what I do when I have a variable file name is use the Foreach enumerator. It allows you pick up the files in a folder based on a pattern. For example, ExcelFileA_*.xls If there's only ever one file out there, great! If there's multiples, it handles it. What I really like is that going this route allows my packages to not fail if the file does not have to exist. Maybe we only the file on the days the stock market's open. Instead of using a script task to test whether the file exists and then enable/disabling a task, put it in the ForEach File enumerator & DFT doesn't blow ! found – billinkc Sep 24 '15 at 14:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.