Tracking SSIS Tasks Execution Duration

All my previous posts are related to the niche requirements. And here I come again to discuss one more such interesting concept.

In SSIS projects, we come across few packages that accomplish a complex business logic. Such packages surely include a chain of control flow tasks. Achieving business logic is not the last step but the first. Once the business logic is achieved in SSIS package, there comes the need to optimize its execution duration which leads us to track the execution time of individual tasks in the package. The tasks which consume more time must be observed and optimized. 

Solution: SSIS Logging to the table is the best way to track the duration. The pros here is we can maintain a history of the executions and compare it with the fresh executions as well. However I recommend to make the history confine to a month or so as per the need. 

When we set the SSIS Package logging to SQL Server tables, a table named "SysSSISLog" is created that holds the execution details like ID, Event, TaskName, StartTime, EndTime...

Below is a step by step process to track the duration.

01. Package Design:

Below is a sample package design that includes multiple tasks few embedded in others.

02. Package Execution:

Below is the shot of package execution.

03. Execution details stored in "SysSSISLog" table:

Query displays the execution details stored in "SysSSISLog" table.

The results include event name, task name, starttime, endtime etc., There can be multiple records depending on number of the events defined for every task.

04. Execution Duration Query:

Observe the last column in the results displaying the duration of every single task. I have set the format in HH:MI:SS.


05. Conclusion:

The objective of this post was to show how to track the duration every single task takes. We very well know that the number of records may impact the execution time. But here we are assuming a static number of records processed through the package. And also here we are not discussing how to optimize the design because it depends upon the package design and the tasks that it include (may be by reducing the components in the tasks or using more of SQL statements rather than relying on SSIS transformations or any other fix).

*We can integrate the "Custom Auditing" concept here but its again a vast area which takes more time to discuss.

I hope its useful just as the previous posts.....Happy SSIS'ing. :)

UPSERT Statement in T-SQL

The MERGE statement is also referred as the UPSERT statement. It allows us to merge multiple rows from one table into another table. Illustrating MERGE Statement with an example using step by step process and with multiple executions. We shall see SCD Type 1 Implementation here.

Note: If you are looking for MERGE using SCD Type 2 Implemenation, click here.

          MERGE is about comparing a source and destination table and depending upon the changes, new records get inserted and existing get updated.

Basic Syntax:

MERGE [INTO] Target_Table

   USING Source_Table

   ON JOIN_Condition

[WHEN MATCHED [AND Search_Conditions]

   THEN DML_Statement]

[WHEN NOT MATCHED

   THEN DML_Statement]

Implementation:

Source Table: DEPT

CREATE TABLE [dbo].[DEPT](

            [DEPTNO] [numeric](2, 0) NOT NULL,

            [DNAME] [varchar](25) NULL,

            [LOC] [varchar](25) NULL

)

Target Table: DEPT_MERGE

CREATE TABLE [dbo].[DEPT_MERGE](

            [DEPTNO] [numeric](2, 0) NOT NULL,

            [DNAME] [varchar](25) NULL,

            [LOC] [varchar](25) NULL,

            [START_DATE] [datetime] NULL,

            [END_DATE] [datetime] NULL

)

INSERT data into DEPT:

INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (10, N'ACCOUNTING', N'NEW YORK')
INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (20, N'RESEARCH', N'MIAMI')

INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (30, N'SALES', N'CHICAGO')

INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (40, N'OPERATIONS', N'BOSTON')


Screen Shot:

MERGE Statement Code:

/************************************* MERGE STATEMENT ***********************************/

MERGE DEPT_MERGE AS D

            USING DEPT AS S

            ON D.DEPTNO = S.DEPTNO

WHEN MATCHED AND (S.DNAME <> D.DNAME OR S.LOC <> D.LOC)

THEN

            UPDATE SET

            D.DNAME = S.DNAME,

            D.LOC = S.LOC,

            D.END_DATE = GETDATE()

WHEN NOT MATCHED

THEN

            INSERT (DEPTNO, DNAME, LOC, [START_DATE])

            VALUES (S.DEPTNO, S.DNAME, S.LOC, GETDATE());

Run-1 Execution: 4 Rows affected.

Run-1 Results: All rows from source into the destination along with start dates.

Further Observation:

To observe how it behaves for the second execution, lets make some changes in the source data. A new record is inserted and an existing record is updated in the source table as below.

Run-2 Execution:  2 rows affected. (1 new row inserted and 1 row updated).

Run-2 Results: Observe the data changes.

Summary: This is identical to Slowly Changing Dimension of SSIS. If you want to handle the data changes in T-SQL then this is most efficient way. Hope this helps. Happy T-SQLing. :)

Write Back Data Prototype

This is the first post on Reporting Services on the blog, and as usual this is not a routine concept but a customized requirement from client.


The objective of the SSRS project here is to first generate a SSRS 2008 report, something like below:

1. Report Design:

The below is a report displaying the client information like ID, Name and City. Along with the actual data we have a dummy column which is linked to a URL, which is a GUI to change the data that inherits on the database.

 2. Report on Report Server:

The below is a shot of report which is deployed on to Report Server.

3. Customized URL:

The below is a customized URL to make changes to the data, which will be inherited on the database.
This has to be designed in ASP.Net project which has to be linked with SSRS.

4. Report and URL integration:

The below screen shot demonstrates a new window pop-up rises, when the Link for a row is clicked. The new window will pop-up with the existing data. The user can now make changes in the editable textboxes and click on UPDATE. Once it is clicked that will make changes to the data in the database.

5. Enhancements:

When user makes changes and clicks on UPDATE button, it should do two operations.

v  It should update the data in the database. (It is accomplished already in the present version of code)

v It should refresh the report page so that the changed data appears instantly. (Got to work on this tomorrow with the developer) 

This in one simple example of the integration of ASP.Net and SSRS. Hope this is useful!!

SQL Server 2012 Integration Services Enhancements

This is an article to discuss few of the many enhancements made to SSIS 2012.

The very first change we notice is the BI development tool itself. The common tool for all BI projects, The Business Intelligence Development Studio (BIDS) is now called SQL Server Data Tools (SSDT).

1) New Project Dialog Box

Two templates are available here. One is for Integration Services Project, this type is available in previous versions of SSIS as well. The second one is Import Project Wizard, to import a project from SSIS Catalog. To enhance an existing SSIS project, we go for this option.

Rich Look Designer: With all new features added, the designer looks so cool to work.

2) SSIS Toolbox:  

Not the regular Toolbox, call it "SSIS Toolbox". A dedicated toolbox for SSIS tasks and components.

3) Parameters: 

New window in the designer which allows us to specify a runtime value for packages, tasks etc.,

4) Variables and SSIS Toolbox Button: 

Two shortcut buttons can be found on the right most side of the designer for variables and SSIS toolbox respectively.

5) Status Indicators:

In this latest version of SSIS, right after a execution of a task or when we have any issue with the configuration of Task is indicated with Green Tick Mark and Red Cross Mark accordingly. No fill up of Green or Red to the entire task now.

6) Expression Indicators: 

If a connection, component or task are configured with dynamic expressions, they are highlighted with "fx" icon, right on tasks.

7) Enhanced Data Viewers: 

Data Viewers helps us a lot in viewing the data at run time. Now we have this feature little modified as show in the image.

8) New Control Flow Tasks & Data Flow Items:

Business requirements are endless. The business people are hungry of Information. In order to turn the junk into information, the ETL developer has to implement logic using transformations available in the toolbox. In case the requirement is complex and not in hands of within transformations, then .Net Script Task and Script Component always helps us in fixing them. As expected, lets welcome new guests in the form of new tasks and components.

9) Finally, Undo-Redo: 

Thanks for the development team for adding a very small feature, (Undo and Redo) but still very important while designing a package. When this feature was present in the previous versions of SSRS , I was wondering why this was not included in SSIS. Anyways, it’s a good news to get the CTRL-Z and CTRL-Y in SSIS.

10) Zoom In and Out: In the right side bottom corner of the workspace you can find the zoom feature to enlarge or reduce the components size. The complex packages can now be easily navigated adjusting the zoom feature.

11) Brand New Deployment Architecture: This is most important feature in SSIS. I have gone through the deployment process and it is as below.

Hope this helps those looking for an overview in the enhancements in SQL Server 2012.

Happy SSIS'ing ;)

Searching for a Column or Table in Multiple Databases? Here is an easy way!!

Hi Guys
Today while working on a requirement, I was searching for a specific table in my entire SQL Server instance. My Instance contains around 75 databases, and searching manually is not a good idea,  seriously. To find a object in a specific database (not in the whole instance), first thing that comes to any developer's mind is to use System Objects like sys.tables or sys.columns. But again executing and checking the results using below system query on all 75 databases again involves manual effort.


-- System Objects Based Query
SELECT T.Name AS TableName, C.Name AS ColumnName 
FROM sys.tables T
INNER JOIN sys.columns C ON T.Object_ID = C.Object_ID
ORDER BY T.Name

So thought of using CURSOR at-least here. Developer's hate it for its performance but there are certainly few situations where you would like to use it. Here is the solution in the form of CURSOR I designed that picks every table, column and the concerned database to which they belong.


-- Cursor that loops over all the databases in a SQL Server instance
DECLARE @NAME VARCHAR(50)
DECLARE @SQL VARCHAR(500)
DECLARE @DATABASENAME CURSOR
SET @DATABASENAME = CURSOR FOR
SELECT name FROM sys.databases
OPEN @DATABASENAME
FETCH NEXT FROM @DATABASENAME INTO @NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'USE '+ @NAME + 
' SELECT ''' + @NAME + ''' AS [Database], T.name AS TableName, C.name AS ColumnName
FROM sys.tables T
INNER JOIN sys.columns C ON T.object_id = C.object_id'
EXEC (@SQL)
FETCH NEXT FROM @DATABASENAME INTO @NAME
END
CLOSE @DATABASENAME
DEALLOCATE @DATABASENAME
GO

If you want to exclude the four system databases to be avoided from querying, you can make use of a filter with WHERE database_id NOT IN (1, 2, 3, 4)

Hope it is useful for those who work on multiple databases with lot of objects in there. :)