Daily Extracts… Monthly Reports

Have you ever had to run a daily task that feeds a report which is generated at the end of every month?  If your answer is yes, you probably know that this can be either a mundane task to do manually or a logistic nightmare in planning, execution & data contention.  In SSIS, I’ll demonstrate a simple way to accomplish this task while minimizing space requirements and maintenance.  The package will determine the day of the month and make a decision on which portions of the package to execute based on that day.

The first thing we will need to do is determine how SSIS is going to generate the daily extracts.  In this example, I want to extract everything from “yesterday” and store it into a database.  To do this, we will create a variable called “Extract Date”. 

 

We will set this variable to ‘EvaluateAsExpression’ = True and make our expression:

(DT_WSTR,4) YEAR (DATEADD(“DD”,-1,GETDATE())) + “-” +  RIGHT(“0” +(DT_WSTR,2) MONTH( DATEADD(“DD”,-1,GETDATE())), 2) + “-” +RIGHT(“0” + (DT_WSTR,2) DAY( DATEADD(“DD”,-1,GETDATE())), 2)

Once that is done, we will need to create our extract statement.  For this example, I am going to extract the number of orders during the extract date. To use the newly created extract date in a SQL statement, you will need to create another variable called “SalesSQL”.

Again, we will set this variable to ‘EvaluateAsExpression’ = True and make our expression:

“select Orders = Count(*) from dbo.OrderHeader where orderDate between convert(datetime,convert(char(10),'” + @[User::ExtractDate] + “‘,101)) and dateadd(ss, -1, convert(datetime,convert(char(10), dateadd(dd,+1,'” + @[User::ExtractDate] + “‘),101)))”

Now we have an extract date that will be populated based on the computers date and T-SQL code to handle the extract from an OLEBD source.  Once that is complete, you can create a data flow task to extract your data.  Drag and drop an OLE DB Source into your data flow, set up the connection manager to point to the source database and choose “SQL command from variable” as your data access mode.  Under the variable name drop down select “User::SalesSQL” as your variable.  To check that your command is working as intended, select the columns tab or preview to ensure that all fields are being populated properly.

 

Next, configure your extract destination as desired.

 

Once you have configured your data flow, place the data flow (and any other tasks you need to run daily into a sequence container called “daily”.  This sequence container will handle all of our daily extracts.

 

Next create another sequence container called monthly.  This will handle the monthly reports derived from the daily extracts.  Like I wrote about in my post ‘Connecting to an XLSX using SSIS’,  I’ve created a data flow task to extract the daily data into the monthly report in the form of an XLSX file.  Once that is complete, I connect it to a simple Execute SQL task that truncates the table once the data has been extracted to the XLSX.  With that configured, I connect it to a Send Mail task that emails the attachment.

Now, we will have to create another variable called “EvalSQL”.  Again, we will set this variable to ‘EvaluateAsExpression’ = True and make our expression:

“select day( ‘” + @[User::ExtractDate] + “‘ )”

When executed this SQL will return the ‘day number’ of the variable ExtractDate.  For this example, it would return an integer of 13.

With that complete, create another variable called “DayOfMonth” with a data type of Int32.  This will be used in our next step.

Next, drag and drop an Execute SQL task onto the control flow pane.  Change the ‘ResultSet’ equal to ‘Single row’.  Configure your connection to point to the same database that is holding the daily extracts, set your ‘SQLSourceType’ equal to Variable and set your ‘SourceVariable’ equal to User:EvalSQL.

Click on the ‘Result Set’ tab and click Add.  Set your Result Name equal to 0 and set the Variable Name to User::DayOfMonth.

Once you have properly configured the execute SQL task, create a connector to the ‘Monthly’ sequence container.  Double click the connector and chose Expression and Constraint as the Evaluation operation, Success as the Value and @[User::DayOfMonth] == 1 as the Expression.  Make sure that the ‘Logical AND’ ratio button is checked.

With that connector configured, create another connection to the ‘Daily’ sequence container.  Like before, double click the connector and chose Expression and Constraint as the Evaluation operation, Success as the Value and @[User::DayOfMonth] != 1 as the Expression.  This time make sure that the ‘Logical OR’ ratio button is checked.

Finally, create a connector to go from your ‘Monthly’ sequence container to your  ‘Daily’ sequence container.    Your package should look like the package below.

This package will first evaluate the day of the month in the form of a number.   If it is the first day of the month, the package will step into the monthly sequence container and execute the steps there before stepping into the daily sequence container to execute the daily extract.  Otherwise, the package will only execute the daily extract.  Enjoy!

Advertisements

Tags: , , , ,

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


%d bloggers like this: