SQL Tip: Using MEDITECH Date Mnemonics in SSIS

One of many nice things about the MEDITECH system is the availability of date mnemonics that you can use, either when running reports interactively, or when scheduling them. But this functionality is not available in SQL Server Reporting Services (SSRS) or in SQL Server Integration Services (SSIS) by default.

Fortunately, you can add that functionality by adding a custom function or stored procedure into your zcus database catalog, and then invoking the code from your SSRS report.

IatricDateInterpreterFN.sql

IatricDateInterspreterSp.sql

That covers reports – even scheduled/subscribed reports in SSRS – but what about SSIS?

In the integration services environment, if you want an extract to drop on a daily basis for yesterday’s data (for example, a daily file to a vendor), you have two general approaches you can take:

Approach One

You can set up two package variables that calculate the beginning and end of yesterday from expressions:

dRunFromDate = (DT_DATE) (DT_DBDATE)DATEADD("dd",-1,GETDATE())

dRunThruDate = DATEADD("s",-1, (DT_DATE) (DT_DBDATE)GETDATE())

The two calculated dates can then be passed to stored procedures or queries to get your data, and if the job is scheduled daily, you’ll always get yesterday’s data out.

Unfortunately, this isn’t terribly flexible. For example, if you want an extract to run for all of last month, you’d have to go into the package, edit the expressions on the calculated variables, and then run, or save-and-run, the package:

dRunFromDate =(DT_DATE)((DT_WSTR, 4)YEAR(DATEADD("mm", -1, GETDATE())) + "-" +RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("mm", -1, GETDATE())),2)+"-01")

dRunThruDate = DATEADD("mi",-1,(DT_DATE)(DT_DBDATE) DATEADD("dd",0,DATEADD("dd",-1 * (DAY(GETDATE())-1),DATEADD("month",-1,GETDATE()))))

We need something better than that!

Approach Two

To get more flexibility with scheduling or running the package ad-hoc, we bring in our date mnemonic code.

We keep our dRunStartDate and dRunEndDate variables, but we populate them using the date mnemonic code:

1. Add two new string variables to your package: cFromDateMnemonic and cThruDateMnemonic.
2. These variables are NOT expressions. They have values you set directly, like “MB-2” and “ME-2”.
3. We use those variables, in conjunction with the function version of our date translator, to generate FROM_DATE and THRU_DATE values, which we then store into the dRunFromDate and dRunThruDate variables.
4. To combine our new string variables into a query that executes our function code to generate the dates we want, we create another variable controlled by an expression: cDateMnemonicsQuery:
   
   

   "DECLARE @cFromDatePattern  VARCHAR(255) ;
   DECLARE @cThruDatePattern    VARCHAR(255) ;
     
   SET @cFromDatePattern = '"+ User::cFromDateMnemonic] +"' ;
   SET @cThruDatePattern   = '"+ @[User::cThruDateMnemonic] +"' ;
   
   SELECT  CONVERT(DATE,zcus.dbo.IatricDateInterpreterFn( @cFromDatePattern )) AS FROM_DATE
           ,CONVERT(DATE,zcus.dbo. IatricDateInterpreterFn ( @cThruDatePattern )) AS THRU_DATE
   ;"
    

5. Then, we set up an Execute SQL task that runs our function twice, using cFromDateMnemonic and cThruDateMnemonic as parameters. This is what the Execute SQL task could look like, after we create it as the first task on the Control Flow of the SSIS package:
   
   
   On the result-set pane, we assign the results of the SQL to our variables:
   
   
   
   
6. Then, we use those variables as parameters for our queries or stored procedures. 

But...

This leaves us a bit short, because we still have to open the package and edit the values in cFromDateMnemonic and cThruDateMnemonic to get an ad-hoc range for our package.

This brings us to SQL Server Integration Services 2012 (or better) and Package Parameters. If you happen to be on SQL 2012, you can get past this last little challenge by creating two Package Parameters – cFromDateMnemonic and cThruDateMnemonic – and then using the values in those parameters to drive your calculation of dFromDate and dThrudate.

First create the Parameters in the package:

Then, update your variable that builds the query to turn the mnemonics into DATETIMEs:

"DECLARE @cFromDatePattern  VARCHAR(255) ;
DECLARE @cThruDatePattern    VARCHAR(255) ;   

SET @cFromDatePattern = '"+ @[$Package::cFromDateMnemonic] +"' ;
SET @cThruDatePattern   = '"+ @[$Package::cThruDateMnemonic] +"' ;

SELECT  CONVERT(DATE,zcus.dbo.IatricDateInterpreterFn( @cFromDatePattern )) AS FROM_DATE
        ,CONVERT(DATE,zcus.dbo. IatricDateInterpreterFn ( @cThruDatePattern )) AS THRU_DATE
;"

The result is:

1. Parameters are set at the package level.
2. Parameters are passed into the package, and converted by an Execute SQL Task into variables.
3. The variables are then used in the queries to set the date range.

Once You’ve Scheduled the SSIS package…

One of the fantastic things about Package Parameters is that when you schedule them in a SQL Agent Job, you can set them at the job level to override what is defined in the package.

That lets you manipulate the Package Parameters in DataTools 2012+ for testing, then schedule the job, and set it to whatever regular pattern you want (T-1 to T or MB-1 to ME-1 or whatever).

Once an override is set in the SQL Agent job, it will “stick,” even if you update the parameter values in the SSIS package and redeploy it.

If you need more help…

Our Report Writing team can help you fix reports, create new ones, make old ones faster, and much more. Simply reach out to your Iatric Systems Account Executive or our NPR report writing team at reportwriting@iatric.com to discuss how we can help support your team!