August 2012 – The Data Queen

Populating a table with ISO Weeks using SSIS

I normally use dimDate tables in my projects, but for this client it made sense to have this at the week level. Rather than sticking with my usual dimDate stored procedure to build out the table and attributes, I thought I’d take the opportunity to migrate the logic over to an SSIS package. Here is a quick overview of how it works.

In my SSIS package I created 4 variables. Three of these variables will be populated by the package. Only the StartDate needs to be populated by the user. In this case the first date to be populated in the table is the first day of 2010.

The tasks are fairly straightforward and the finished package looks like this.

I wanted to blog certain parts of this, largely so I can remember them the next time I do this.

I will lay out the important SQL Statements used in these steps. For specifics on how to set up variables and tasks using variables you can check out this post How to Set and Use Variables in SSIS Execute SQL Task

Set @StartDate to first Sunday from @StartDate

This task is in place to ensure that the StartDate is set to the correct day of the week, as required by the business. This client has the start of their business week on Sunday. If the user has entered a date into the package variable which is not a Sunday, this task will set the @StartDate to the nearest Sunday after the date in the variable. The fact that the SQL Server DATEPART ISOWW starts on a Monday will be addressed later in this post. The CAST statement is added to allow the results to be inserted into the @StartDate string variable.

SELECT
CAST(
CASE WHEN DATENAME(dw,?) = ‘Sunday’ THEN ? –@StartDate
ELSE DATEADD(d,8-DATEPART(dw,?),?) END
as nvarchar) as NewStartDate

Set @EndDate to to last ISO Week in current year

The client wants the table to contain weeks up to the last week of the current ISO year. When we move into next year, the new year will automatically be added. You can edit this statement to meet your business requirements. The CAST statement is added to allow the results to be inserted into the @EndDate string variable.

SELECT
CAST(
–Check to see if current year is 52 or 53 weeks
CASE WHEN DATEPART(ISOWW,DATEADD(WEEK,53 – DATEPART(ISOWW,GETDATE()),GETDATE())) = 1
THEN DATEADD(WEEK,52 – DATEPART(ISOWW,GETDATE()),GETDATE())
ELSE DATEADD(WEEK,53 – DATEPART(ISOWW,GETDATE()),GETDATE())
END
as nvarchar)
as EndDate

Set @LoopNumberOfWeeks

This will find the number of weeks between the StartDate and the EndDate for use in the For Loop Container. I added 1, because I chose to start my Loop at 1 rather than 0.

SELECT DATEDIFF(wk,?,?) + 1 as NumberOfWeeks

For Loop Container

The container is set to look through the number of weeks calculated in the Set @LoopNumberOfWeeks task. Note that the InitExpression is 1 instead of 0. I am using the @LoopWeek variable to populate one of the fields in my table, which is why I wanted to start with 1.

Insert record into dimAccWeek_New

INSERT INTO [dimAccWeek_new]
([AccWeekSK],
[AccWeekStartDate])

SELECT
? as AccWeekSK,
? as AccWeekStartDate

Add 7 days to @StartDate

Instead of using the loop to move the date forward one week, I am updating the variable with an Execute SQL Task inside the loop. The CAST statement is added to allow the results to be inserted into the @StartDate string variable.

SELECT
CAST(
DATEADD(dd,7,?)
as nvarchar) as LoopWeek

Update attributes

Here is the meat of it. When using the DATEPART ISOWW on their SQL Server, the ISO Week starts on a Monday. Since business week starts on a Sunday I am adding 1 day when calculating where it falls within a Week, Quarter, Year

UPDATE dimAccWeek_new
   SET AccWeekSK = CASE
    –When date is > the first week and date between Jan 1 and 3 then PrevYr + ISOWeek
    WHEN DATEPART(ISOWW,DATEADD(dd,1,AccWeekStartDate)) > 1
    AND DATEPART(ISOWW,DATEADD(dd,1,AccWeekStartDate)) BETWEEN CAST(DATEPART(Year,AccWeekStartDate) as varchar) + ‘0101’
        AND CAST(DATEPART(Year,AccWeekStartDate) as varchar) + ‘0103’
    THEN CAST(DATEPART(Year,AccWeekStartDate)-1 AS varchar) + RIGHT((’00’ + CAST(DATEPART(ISOWW,DATEADD(dd,1,AccWeekStartDate)) as varchar)),2)
    –When date = the first week and date between Dec 29 and Dec 31 then NextYr + ISOWeek
    WHEN DATEPART(ISOWW,DATEADD(dd,1,AccWeekStartDate)) = 1
    AND AccWeekStartDate
        BETWEEN CAST(DATEPART(Year,AccWeekStartDate) as varchar) + ‘1229’
        AND CAST(DATEPART(Year,AccWeekStartDate) as varchar) + ‘1231’
    THEN CAST(DATEPART(Year,AccWeekStartDate)+1 AS varchar) + RIGHT((’00’ + CAST(DATEPART(ISOWW,DATEADD(dd,1,AccWeekStartDate)) AS varchar)),2)
    –ELSE CurrentYr + ISOWeek
    ELSE CAST(DATEPART(Year,AccWeekStartDate) AS varchar) + RIGHT((’00’ + CAST(DATEPART(ISOWW,DATEADD(dd,1,AccWeekStartDate)) AS varchar)),2)END

      ,AccYear = CASE
    –When date is > the first week and date between Jan 1 and 3 then PrevYr
    WHEN DATEPART(ISOWW,DATEADD(dd,1,AccWeekStartDate)) > 1
    AND DATEPART(ISOWW,DATEADD(dd,1,AccWeekStartDate)) BETWEEN CAST(DATEPART(Year,AccWeekStartDate) as varchar) + ‘0101’
        AND CAST(DATEPART(Year,AccWeekStartDate) as varchar) + ‘0103’
    THEN CAST(DATEPART(Year,AccWeekStartDate)-1 AS varchar)
    –When date = the first week and date between Dec 29 and Dec 31 then NextYr
    WHEN DATEPART(ISOWW,DATEADD(dd,1,AccWeekStartDate)) = 1
    AND AccWeekStartDate
        BETWEEN CAST(DATEPART(Year,AccWeekStartDate) as varchar) + ‘1229’
        AND CAST(DATEPART(Year,AccWeekStartDate) as varchar) + ‘1231’
    THEN CAST(DATEPART(Year,AccWeekStartDate)+1 AS varchar)
    –ELSE CurrentYr
    ELSE CAST(DATEPART(Year,AccWeekStartDate) AS varchar) END

      ,AccQuarter = CASE
    WHEN DATEPART(mm,AccWeekStartDate) BETWEEN 1 AND 3 THEN 1
    WHEN DATEPART(mm,AccWeekStartDate) BETWEEN 4 AND 6 THEN 2
    WHEN DATEPART(mm,AccWeekStartDate) BETWEEN 7 AND 9 THEN 3
    ELSE 4 END

      ,AccMonth = DATEPART(mm,AccWeekStartDate)
      ,AccWeek = DATEPART(ISOWW,DATEADD(dd,1,AccWeekStartDate))

,AccWeekEndDate = DATEADD(dd,6,AccWeekStartDate)

You can add as many attributes as you require. This should get you started.

How To Set and Use Variables in SSIS Execute SQL Task

I couldn’t find any step-by-step resources on this so I thought I’d blog it. This is done in SQL Server 2008 R2. In this scenario I will use a SQL Statement to populate an SSIS variable, and then use that variable in another SQL Statement.

Create your variables

If the variables window isn’t visible already, from the menu select View –> Other Windows –> Variables.

This will bring up your variables window on the left hand side of your screen. You should be able to tab between it and the Toolbox. You may have to pin it to your screen first.

I have already created some variables in the screenshot above. To create a variable, first decide on the scope of the variable, whether it is the entire package, a container or a specific task. I normally scope all of my variables to the package, since you can’t see the variables in the window unless you have selected the scoped object. I find it easier to manage variables having them all in one place. I have not had any compelling reason not to do it this way, although I’m sure there are cases where you may want them scoped differently.

Before creating the variable you must select the item for it’s scope. In this case select the canvas of the Control Flow which will permit you to scope the variable to the entire package. Then create the variable by clicking on the Add Variable button at the top of the Variables window.

Give your variable a Name, a Data Type and a Value. In this case we are working with EndDate, and although you would want to set it as Date, there are some compatibility issues when using the Date data type for a variable and then pulling into a SQL parameter. I have found it is best to set the date as a string and then manipulate it from there. Set your EndDate attributes as shown, with the Scope being the name of your package.

Populate an SSIS variable using a SQL statement

From the Toolbox, drag an Execute SQL Task onto your Control Flow. Double click on it and set the following properties:

On the General tab:

Set the ResultSet to Single row
Select your Connection to the database where you will run your SQL Statement
Enter your SQL Statement

On the Result Set tab:

In the Result Name key in the name of the field being return in your SQL Statement. In this case I named the field EndDate
In the Variable Name select the user variable you created.

Use the Variable in a SQL Statement

From the Toolbox, drag an Execute SQL Task onto your Control Flow. Double click on it and set the following properties:

On the General tab:

Select your Connection to the database where you will run your SQL Statement
Enter your SQL Statement, placing a ? where each variable is to be used.

In this case I need to add two Parameters to the Parameter Mapping tab, since I have used two question marks in my SQL Statement. Even if you are using the same Variable multiple times, you still need to enter them separately on the Parameter Mapping tab, once for each time they appear in the SQL Statement. This is the only way to identify which parameter is being used for which question mark. Parameters are made much easier in 2012.

Here’s the tricky bit which isn’t at all self explanatory.

On the Parameter Mapping tab:

Select the user variable in the Variable Name drop down which corresponded to the first variable (?) being used in your SQL Statement.
Set the Data Type to NVARCHAR, since we are using a string variable. (This is the spot where the data type compatibility didn’t mesh, which is why we opted for string).
Set the ParameterName to 0. This indicates the order in which the parameter is appearing in the SQL statement. You can enter them in the pane in any order you want, but the ParameterName must contain numbers starting from 0 to however many parameters you have minus one. So if you have two parameters you must have one ParameterName = 0 and one ParameterName = 1. You can reverse the order, but they must both exist or the task will fail.

Configure SSIS Logs

As a continuation of Creating an SSIS Package Template with Predefined Package Configurations I wanted to share with you another good habit for your package template. I like to add logging to my packages for the purposes of troubleshooting, especially when there are complex processes with multiple packages running at once. It helps me track how long packages take to run and gives me the ability to see if there are changes over time, as well as the obvious error logging.

It is very simple to turn on the logging. Simply right click on your Control Flow canvas and select Logging.

This is another place where having a sequence containers within your package are a good idea. I generally have the entire package within one master sequence container, and then divide up the tasks within other sequence containers as necessary. You can set the logging for the main Sequence Container within your package and you will notice that the other items within that container will inherit the logging configuration. You can deselect certain tasks or containers if required.

I like to log errors in SQL Server. To do this, change the Provider Type to SSIS log provider for SQL Server.

Click the Add button. In the Configuration column use the dropdown to select the connection to the SQL database where you would like to store the logs.

Check the box beside the Package_Template container and then check the box beside the log you have just created.

Select the Details tab. Specify which events you would like to capture.

Keep in mind that each event will take up space in your database. Depending on how many packages you are logging and how often, you may with to only track important events, or you may wish to delete some or all of the events on a scheduled basis.

Here are a few views I like to create for use when checking the logs.

CURRENT ERRORS

CREATE VIEW [dbo].[ssislog_ErrorsCurrent]
AS
SELECT     TOP (100) PERCENT PKG.PackageName, PKG.starttime AS PackageStartTime, LG.source AS TaskName, LG.starttime AS StartTime, LG.endtime AS EndTime,
                      LG.message
FROM         dbo.sysssislog AS LG INNER JOIN
                          (SELECT     LG1.executionid, LG1.source AS PackageName, LG1.starttime
                            FROM          dbo.sysssislog AS LG1 INNER JOIN
                                                       (SELECT     source, MAX(starttime) AS starttime
                                                         FROM          dbo.sysssislog
                                                         WHERE      (event = ‘PackageStart’)
                                                         GROUP BY source
                                                         HAVING      (MAX(starttime) > DATEADD(dd, – 1, GETDATE()))) AS CUR ON CUR.source = LG1.source AND CUR.starttime = LG1.starttime
                            WHERE      (LG1.event = ‘PackageStart’)) AS PKG ON LG.executionid = PKG.executionid
WHERE     (LG.event IN (‘OnError’))
ORDER BY PackageStartTime DESC, StartTime

CURRENT LOG ENTRIES

CREATE VIEW [dbo].[ssislog_LogEntriesCurrent]
AS
SELECT     TOP (100) PERCENT PKG.PackageName, PKG.starttime AS PackageStartTime, LG.source AS TaskName, LG.starttime, LG.endtime, LG.message
FROM         dbo.sysssislog AS LG INNER JOIN
                          (SELECT     LG1.executionid, LG1.source AS PackageName, LG1.starttime
                            FROM          dbo.sysssislog AS LG1 INNER JOIN
                                                       (SELECT     source, MAX(starttime) AS starttime
                                                         FROM          dbo.sysssislog
                                                         WHERE      (event = ‘PackageStart’)
                                                         GROUP BY source
                                                         HAVING      (MAX(starttime) > DATEADD(dd, – 1, GETDATE()))) AS CUR ON CUR.source = LG1.source AND CUR.starttime = LG1.starttime
                            WHERE      (LG1.event = ‘PackageStart’)) AS PKG ON LG.executionid = PKG.executionid
ORDER BY LG.endtime DESC

PACKAGE DURATION

CREATE VIEW [dbo].[ssislog_PackageDurationCurrent]
AS
SELECT     TOP (100) PERCENT source AS PackageName, MIN(starttime) AS StartTime, MAX(starttime) AS EndTime, DATEDIFF(MI, MIN(starttime), MAX(starttime))
                      AS DurationInRoundedMinutes, DATEDIFF(ss, MIN(starttime), MAX(starttime)) AS DurationInTotalSeconds
FROM         dbo.sysssislog
WHERE     (event IN (‘PackageEnd’, ‘PackageStart’)) AND (starttime > DATEADD(dd, – 1, GETDATE()))
GROUP BY executionid, source
ORDER BY starttime DESC

I hope you find this useful.