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
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 (
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
I just feel like there's got to be a better way!