BI consultants the hard way structuring ssis to use var expression easily

One of the most frustrating things is to write load of expression when defining the SSIS Vars; some tools can help the input of them, but in this article we can use a technique to automatize that; this is very useful when using some sql code in your package; likely to happen …

Let’s suppose we have some simple sql snippet, we want use in our pkg (likely called by Execute sql tasks):

 <pre>

USE MYDB GO 
--*****Start of Synonym Creation 
--***** CREATE SYNONYM vSourceTable003 FOR FRDB.dbo.DimCalendarDate; 
--*****End of Synonym Creation***** 
--*****Start of Spec Code***** 
--Declare Job Parameters 
DECLARE @TargetMonthEnd int = 20130930; 
--Value to come from job parameter 
DECLARE @EffectiveDate datetime; 
SELECT @EffectiveDate = CalendarDate 
FROM vSourceTable003 
WHERE CalendarDateKey = @TargetMonthEnd; 
--Return and Set Job Parameters 
SELECT @EffectiveDate AS EffectiveDate; 
--*****End of Spec Code***** 
--*****Start of Synonym Drop 
--***** DROP SYNONYM vSourceTable003; 
--*****End of Synonym Drop***** 
</pre>

As the sql logics depends from an external value (Hp it’s calculated somewhere else and stored in User::TargetMonthEnd, you can think of a sql that use an expression like this:

 
<pre>

"
----*****Start of Synonym Creation*****
--CREATE SYNONYM vSourceTable003 FOR FRDB.dbo.DimCalendarDate;
----*****End of Synonym Creation*****
 
--*****Start of Spec Code*****
--Declare Job Parameters
DECLARE @TargetMonthEnd int = " + (DT_WSTR, 8) @[User::TargetMonthEnd] + "; --Value to come from job parameter
DECLARE @EffectiveDate datetime;
 
SELECT @EffectiveDate = CalendarDate
FROM "+ @[User::vSourceTable003]+"
WHERE CalendarDateKey = @TargetMonthEnd;
 
--Return and Set Job Parameters
SELECT @EffectiveDate AS EffectiveDate;
 
--*****End of Spec Code*****
 
----*****Start of Synonym Drop*****
--DROP SYNONYM vSourceTable003;
----*****End of Synonym Drop*****
 
"

</pre>

Populating the var expression by hand can be cumbersome, and don’t forget that the sql spec might change to reach new spec requirements. A way is to define a way to place the meta-sql to as from that to get the var expression value to put in the vars itself.
Let’s use this simple substitution as convention:

DECLARE @TargetMonthEnd int = 20130930; –Value to come from job parameter
DECLARE @TargetMonthEnd int = User::TargetMonthEnd; –Value to come

So that the parameter value becomes dynamically; a meta-sql that we can think of to represent this logical substitution can be:

 <pre>

--REM dictKey = "veSQLForSQLIdentifyEffectiveDate";
--REM dictMetaSQLValue = @"
 
----*****Start of Synonym Creation*****
--CREATE SYNONYM vSourceTable003 FOR FRDB.dbo.DimCalendarDate;
----*****End of Synonym Creation*****
 
--*****Start of Spec Code*****
--Declare Job Parameters
DECLARE @TargetMonthEnd int = User::TargetMonthEnd; --Value to come from job parameter
DECLARE @EffectiveDate datetime;
 
SELECT @EffectiveDate = CalendarDate
FROM User::vSourceTable003
WHERE CalendarDateKey = @TargetMonthEnd;
 
--Return and Set Job Parameters
SELECT @EffectiveDate AS EffectiveDate;
 
--*****End of Spec Code*****
 
----*****Start of Synonym Drop*****
--DROP SYNONYM vSourceTable003;
----*****End of Synonym Drop*****
 
--REM            ";
--REM dictMetaSQL.Add(dictKey, dictMetaSQLValue);

</pre>

There is no rule I just came up to this type of substitution as it simplify the checks in sql mng studio when converting the spec sql to this new type of meta-sql that will be sue by the SCR task I’ll show you shortly.
Note there the catch of the eye in WinMerge

image005
This simplifies a lot when keeping the code in sync, when a spec change is requested you can use WinMerge to update the meta-sql version.

This meta-sql can be mapped to an SSIS var (User::veSQLForSQLIdentifyEffectiveDate)

Hp we have bunch of veSQLForXYZ in our sql mng solution, now it’s time to use them to ealiy get the var expression for the sql vars; as we want to minimize the var expression rewriting we can use a SCR that does that for you:

image0091 

 <pre>		

const string prefixToRemove = "User::";
        const int maxLenghtSSISExpression = 4000;
        public void Main()
        {
 
            //N&gt; this are contained initially in the lveSQLForxyz
             List<string> lVarsToReplaceInMetaSQL = new List<string> { 
    
"User::vSourceTable003",
"User::TargetMonthEnd"
 
};
 
            //TODO
 
            List<string> lVeSQLForVars = new List<string> { 
 
 "veSQLForSQLIdentifyEffectiveDate"
 
};
 
 
            //dict that will contain the lveSQLForxyz with proper SSIS exprseesion coming for the MetaSQL
            Dictionary<string string ,> dictMetaSQL = new Dictionary<string string ,>();
 
            //N&gt; Add MetaSQL form Mng SQL
            AddMetaSQL(dictMetaSQL);
 
            //N&gt; update ExpressifyVar()
            ParseMetaSQL(dictMetaSQL, lVarsToReplaceInMetaSQL);
 
            PersistInVars(dictMetaSQL);
 
            CheckLenghtNoUnreplaced(dictMetaSQL);
 
            PrintSQL(lVeSQLForVars);
 
 
            Dts.TaskResult = (int)ScriptResults.Success;
        }

</pre>

Where the contents of AddMeta() are a copy and pasted from the various _DV.sql meta-sql in the sql solution

 <pre>	

/// <summary>
        /// Add all the SQL
        /// N&gt; this is mapped to the sql project where all the SQL SPEC are translated in META SQL
        /// </summary>
        /// <param name="lveSQLFor"></param>
        private void AddMetaSQL(Dictionary<string string ,> dictMetaSQL)
        {
 
            string dictKey = string.Empty;
            string dictMetaSQLValue = string.Empty;
 
            /*
             * Start of DEV.TemplateForSQLDump.txt
             */
 
            dictKey = "veSQLForSQLIdentifyEffectiveDate";
            dictMetaSQLValue = @"
 
----*****Start of Synonym Creation*****
--CREATE SYNONYM vSourceTable003 FOR FRDB.dbo.DimCalendarDate;
----*****End of Synonym Creation*****
 
--*****Start of Spec Code*****
--Declare Job Parameters
DECLARE @TargetMonthEnd int = User::TargetMonthEnd; --Value to come from job parameter
DECLARE @EffectiveDate datetime;
 
SELECT @EffectiveDate = CalendarDate
FROM User::vSourceTable003
WHERE CalendarDateKey = @TargetMonthEnd;
 
--Return and Set Job Parameters
SELECT @EffectiveDate AS EffectiveDate;
 
--*****End of Spec Code*****
 
----*****Start of Synonym Drop*****
--DROP SYNONYM vSourceTable003;
----*****End of Synonym Drop*****
 
";

dictMetaSQL.Add(dictKey, dictMetaSQLValue);


/*
* End of  DEV.TemplateForSQLDump.txt
*/
}

</pre>

Note that we have to select the R/W vars (we could use the VarDispenser but better to explicitly them here so we can have a visual reference without opening the SCR code)

image010

And the result is

 
<pre>

Information: 0x40016040 at FRDB_rFrame Template v1 5: The package is attempting to configure from SQL Server using the configuration string ""Config_Conn";"[ETL].[SSISConfigurations]";"FRDB_rFrame Template";".
SSIS package "FRDB_rFrame Template v1.5.dtsx" starting.
Information: 0x40016040 at FRDB_rFrame Template v1 5: The package is attempting to configure from SQL Server using the configuration string ""Config_Conn";"[ETL].[SSISConfigurations]";"FRDB_rFrame Template";".
Warning: 0x0 at Parse Meta SQL veSQLFor, PrintSQL: *** ASExpression [veSQLForSQLIdentifyEffectiveDate] &gt;702&lt;
|"
 
----*****Start of Synonym Creation*****
--CREATE SYNONYM vSourceTable003 FOR FRDB.dbo.DimCalendarDate;
----*****End of Synonym Creation*****
 
--*****Start of Spec Code*****
--Declare Job Parameters
DECLARE @TargetMonthEnd int = " + (DT_WSTR, 8) @[User::TargetMonthEnd] + "; --Value to come from job parameter
DECLARE @EffectiveDate datetime;
 
SELECT @EffectiveDate = CalendarDate
FROM "+ @[User::vSourceTable003]+"
WHERE CalendarDateKey = @TargetMonthEnd;
 
--Return and Set Job Parameters
SELECT @EffectiveDate AS EffectiveDate;
 
--*****End of Spec Code*****
 
----*****Start of Synonym Drop*****
--DROP SYNONYM vSourceTable003;
----*****End of Synonym Drop*****
 
           "|
Warning: 0x0 at Parse Meta SQL veSQLFor, PrintSQL: *** ASValue [veSQLForSQLIdentifyEffectiveDate] &gt;632&lt;
|
 
----*****Start of Synonym Creation*****
--CREATE SYNONYM vSourceTable003 FOR FRDB.dbo.DimCalendarDate;
----*****End of Synonym Creation*****
 
--*****Start of Spec Code*****
--Declare Job Parameters
DECLARE @TargetMonthEnd int = 0; --Value to come from job parameter
DECLARE @EffectiveDate datetime;
 
SELECT @EffectiveDate = CalendarDate
FROM TODO
WHERE CalendarDateKey = @TargetMonthEnd;
 
--Return and Set Job Parameters
SELECT @EffectiveDate AS EffectiveDate;
 
--*****End of Spec Code*****
 
----*****Start of Synonym Drop*****
--DROP SYNONYM vSourceTable003;
----*****End of Synonym Drop*****
 
           |
Task failed: Parse Meta SQL veSQLFor
SSIS package "FRDB_rFrame Template v1.5.dtsx" finished: Failure.

</pre>

Note the package fails as the usage is intended just to build the content of the sql var expression…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s