SSIS - Creating SQL server Agent Proxy Account for SSIS Package Execution

       When you are trying to perform some file system task in SSIS or while attempting to read/write some files from the remote locations, the job which uses SSIS package will require the permission to access the network location.
       If your windows NT account has all the rights to access the particular loaction then the package will work fine while runnig the package from BIDS. But the same will not work if you run the package from SQL Server Agent Job. Since the SQL Server Agent will be running on the 'sa' account by default. And this SQL admin will not have permission to access the file sytems.
      So, to make it run from the SQL Server Agent, we have to create new credential (of Windows NT account) which will have all the access to the specific file server or network location.
       And we have to create Proxy for the SQL Server Agent which uses the newly created credential. And the Proxy should be associated with the Subsystem SSIS.

 Please find the detailed steps written in the following blogs :
http://www.vincepergolizzi.com/2012/03/setting-up-sql-server-agent-proxies-for-ssis-packagese/
http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/
Thanks for the authors of those blogs. :-)

SSIS- Pivot transformation

The following article neatly explains how to use Pivot Transformation in SSIS

This tutorial provides a complete pivot sample on AdventureWorks sample SQL Server database with SSIS 2008...

Assume you want to find order quantities for each product by year.

you may use this query to get the result:

 select
    Product.ProductID,
    Product.Name,
    year(SalesOrderHeader.OrderDate) as OrderYear,
    sum(SalesOrderDetail.OrderQty) as OrderQuantity
from Sales.SalesOrderDetail
inner join Production.Product
on SalesOrderDetail.ProductID=Product.ProductID
inner join Sales.SalesOrderHeader
on SalesOrderDetail.SalesOrderID=SalesOrderHeader.SalesOrderID
group by Product.ProductID,Product.Name,year(SalesOrderHeader.OrderDate)
order by Product.Name,year(SalesOrderHeader.OrderDate)

and this is result of query above:

Now , create a new SSIS package

Add a Data Flow Task

in dataflow tab :

Add a OLE DB Source and connect it to AdventureWorks database in your SQL Server

write the query in SQL command Text of oledb data source,

then add a PIVOT Transformation after OLE DB Data Source

in Pivot transformation advanced editor , go to input columns tab and select all input columns

then go to input and output properties tab, select pivot default input, under input columns you will find all columns which you selected in previous tab.

the only property you must set for each input column is the PivotUsage.

this describe the values you can use in PivotUsage property:

So , in this example , PivotUsage property for each input column will be as below:

ProductID           0
Name                 1
OrderYear           2
OrderQuantity     3

Notice that you must have at least one input column with PivotUsage 2, one input column with PivotUsage 3,one input column with PivotUsage 0 OR 1 at least in your pivot transformation.

After setting input columns, go to Pivot Default output , and under output columns add these columns:

ID, Name , 2001 ,2002 , 2003 , 2004

ID and Name columns will show the exact values form ProductID and Name input columns.

So, set SourceColumn property of ID output column to LineAgeID of ProductID input column.

Note: this is critical to use the lineageID value of input columns not the ID value. if you use ID value you will face error.

but how can you find lineageID of input columns?

simply select ProductID under input columns and see the lineageID value there.

Note: the lineageID is different on each machine, so you can not use exact lineage numbers used in this example.you must use your lineageID values.

this is lineageID of ProductID input column:

and this is the SourceColumn property in ID output column:

leave the PivotKeyValue of ID and Name output column as empty.

Do above steps for Name output column with Name input column too

Now it's Pivot columns turn, select 2001 under output columns

this columns will show the results for the year 2001, so enter 2001 in PivotKeyValue property.

and set SourceColumn with lineageID of OrderQuantity input column.

In fact you must set SourceColumn property with the lineageID of the input column which has the PivotUsage 3 value.

in this example , lineageID of OrderQuantity is 69 (Remember you must use your own lineageIDs)

So, we set SourceColumn with 69 in 2001 output column:

Now , do the same for 2002,2003,2004 columns.the only difference is that you must set PivotKeyValue to 2002,2003,2004 in these columns. butlineageID will be the same in all 2001 - 2004 columns.

Pivot configuration finished now.

Just add a destination, and fill the result of Pivot in destination.

I used a RecodeSet Destination with a Grid Data Viewer to show the result,

you can add Grid dataviewer by right click on green arrow between pivot transformation and  your destination, then select data viewers, then add Grid.

this is the full desing of my DataFlow Task:

and this is the final Result:

 Courtesy : Reza Rad (www.rad.pasfu.com)

Creating Time Dimension table & Data population

Script to create a simple Time Dimension Table using SQL script and populate data :

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'TimeDimension' AND type = 'U')
DROP TABLE TimeDimension
GO
CREATE TABLE TimeDimension
(
[DateID] numeric(8,0) PRIMARY KEY CLUSTERED,
[Date] SMALLDATETIME, [Day] VARCHAR(10),
week varchar(10), [Month] VARCHAR(10),
[Quarter] varchar(10),
[Year] SMALLINT,
DayOfMonth SMALLINT,
DayOfYear smallint,
WeekOfYear SMALLINT,
MonthOfYear SMALLINT,
QuarterOfYear smallint,
[FinYear] nvarchar(10),
[FinMonthOfYear] smallint,
[FinWeekOfYear] smallint,
[FinWeek] varchar(10),
[FinQuarterOfYear] Smallint
)
GO DECLARE @CurrDate SMALLDATETIME

SET @CurrDate = '01/01/2008'
WHILE @CurrDate < getdate()+360
BEGIN
INSERT TimeDimension
([DateID],[Date], [Day], [Month], [Year], DayOfMonth, WeekOfYear, MonthOfYear, [Quarter],

DayOfYear,week,Quarterofyear)
VALUES
(
CONVERT(Varchar(10),@CurrDate,112), --Date-As-ID @CurrDate,-- theDate
DATENAME(dw, @CurrDate), -- theDay
DATENAME(month, @CurrDate), -- theMonth
YEAR(@CurrDate), -- theYear
DAY(@CurrDate), -- DayOfMonth
DATENAME(wk, @CurrDate), -- WeekOfYear
MONTH(@CurrDate), -- MonthOfYear
'Quarter' + DATENAME(quarter,@CurrDate), -- Quarter
datename(dy,@CurrDate), --Day of Year
'Week ' + Datename(wk,@CurrDate), --Week
DATENAME(quarter,@CurrDate)) --Quarterof year
SET @CurrDate = DATEADD(day, 1, @CurrDate)
END

The above script will create a TimeDimension table and it will populate dimensional data from 01/01/2008 upto 360 days from run date.

SSIS - Check the availability of Data Source

When the tables in the DW are truncated and populated on regular basis, most of the time the truncate script will run first and then the data population from Source to destination will happen. What if the Data Source is not available in mid of the process or at any circumstances? 

 The truncation will occur and the destination will be empty, to avoid this we can check the availability of the source before initiating the truncation or any other tasks. 

To do so, we can use the following steps:

1.       Create variable in the Package (Package level scope) to which will be used to flag that the source is available or not.

2.       Create a script task with the following script in (it will check whether the source is available or not & update the variable)

                Script task:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Sub Main()

        Dim ConMgr As ConnectionManager = Dts.Connections("<SourceConn>")

        Dim ConStr As String = ConMgr.ConnectionString

        Try

            ConMgr.AcquireConnection(Nothing)

            Dts.Events.FireInformation(1, "", "Connection acquired Successfully on" + ConMgr.Name, "", 0, False)

            Dts.Events.FireInformation(2, "", "Connection String=" + ConStr, "", 0, False)

            Dts.Variables("SourceAvailability").Value = "y"

            Dts.TaskResult = ScriptResults.Success

        Catch ex As Exception

            Dts.Events.FireInformation(1, "", "Connection Failed on" + ConMgr.Name, "", 0, False)

            Dts.Events.FireInformation(2, "", "Connection String=" + ConStr, "", 0, False)

            Dts.Variables("SourceAvailability").Value = "n"

            Dts.TaskResult = ScriptResults.Success

        End Try

      End Sub

End Class

3.       Use the precedence Constraint to write the expression as shown in fig . (Double click on the green arrow to go the Precedence constraint Editor) 

Ie, This precedence constraint will let the execution to flow to the next tasks only if the source is available.

Courtesy: got idea from http://consultingblogs.emc.com .