SSIS Video Tutorials , SSIS Webcast , SSIS Training , SSIS Study Guide

SSIS Video Tutorials on JumpStart TV:

• Ignoring a SSIS Checkpoint


• Loading a Dimension with the SSIS Slowly Changing Dimension Wizard


• SSIS Configurations


• SSIS Control Flow Basics


• Data Profiling in SSIS


• Applied Fuzzy Lookup


• Change Data Capture with SSIS


• Managing the SSIS Service


• Loading Binary Files into SQL Server Using SSIS


• Running and Scheduling SSIS Packages


• Migrating From DTS to SSIS - Part 1


• BI MetaData Toolkit Quickstart


• Integrating Database Snapshots into Integration Services


• Using Transactions in SSIS


• Using the For Each ADO Enumerator


• Clustering SSIS


• Looping Through and Loading Files with SSIS


• Introduction to Deploying SSIS Packages


• Using the Fuzzy Lookup Transform


• Using Checkpoints in SSIS


• Using a Basic Data Flow Task in SSIS


• Using a Script Transform in SSIS


• Speak Like a SSIS Developer


• SSIS Stumpers V1 – Unpivot Example


• Using the Aggregate Transform for Group Bys in SSIS


• Scheduling SSIS Packages with SQL Server Agent


• Using a Sort Transform in SSIS


• Using Data Viewers in SSIS


• Using the File System Task in SSIS


• Executing SSIS Package in SSMS


• Adding a Time Stamp to a File Name in SSIS


• Parameterizing the Execute SQL Task in SSIS


• SSIS Lookup Transform Basics


• SSIS Lookup Transform - Lookup Failures


• Secure FTP File Transfer in SSIS


• Expression Language Basics

Reference: SSIS Team Blog

SSIS Server Hardware Software Configuration

In February 2008, Microsoft announced a record-breaking data load using Microsoft® SQL Server® Integration Services (SSIS): 1 TB of data in less than 30 minutes. That data load, using SQL Server Integration Services, was 30% faster than the previous best time using a commercial ETL tool.

To illustrate the ability of SSIS to meet such performance needs, Microsoft and Unisys arranged to load over 1 terabyte (TB) of data that was read from flat files on four source servers into a SQL Server database on a single destination server. In this test, the data was read, converted from text fields to database data types, transferred over the network, and inserted into the destination database in less than 30 minutes. To be precise, 1.18 TB of flat file data was loaded in 1,794 seconds. This is equivalent to 1.00 TB in 25 minutes 20 seconds or 2.36 TB per hour.

Microsoft has published a very interesting whitepaper on the same, and the key take-away from this paper are Database Setup, Hardware Configuration, Profiling SSIS performance and some SSIS performance tuning techniques like use of FastParse property for example.

Package This - Free tool to create .chm files

"Package This" is a GUI tool written in C# for creating help files (.chm and .hxs) from the content obtained from the MSDN Library or the TechNet Library via the MSDN Content Service. You select the content you want from the table of contents, build a help file, and use the content offline. You are making personalized ebooks of MSDN or TechNet content. Both help file formats also give full text search and keyword search.

The above paragraph is a excerpt from the tool's home page. I feel that it's a great way to compile whitepapers, help pages or reference material into a single .chm file while would be easy and organized to read and search as well.

Using SSIS with SAP and other Business Intelligence Systems like Siebel , Teradata and others

One of the most interesting whitepaper that I have come across on SQL Server 2008 is "Using SQL Server 2008 Integration Services with SAP BI 7.0". This sounds interesting to me as I have no idea about SAP. Actually there is a probability that very soon I am going to be a part of an assignment where I would be working on a system where I would be using SSIS to translate and migrate data between different BI systems. And the most interesting part is there is no relational database into picture. Soon I might get a chance to architect solution for the requirement. This whitepaper can be divided into two parts:

• Scenario 1: Extracting Data from SAP BI into SQL Server
  


• Scenario 2: Loading Non-SAP Data into SAP BI

I believe this article would make more sense to me once I start getting my hands dirty on SAP with SSIS.

SSIS 2008 Connector Downloads

• Microsoft Connector for Oracle By Attunity


• Microsoft Connector for Teradata By Attunity


• Microsoft Connector 1.0 for SAP BI - Available in SQL Server 2008 Feature Pack, Oct Release


• Microsoft BizTalk Adapter 3.0 for mySAP Business Suite


• Microsoft BizTalk Adapter 3.0 for Siebel eBusiness Applications


• Microsoft OLE DB Provider for DB2


• Microsoft Connectors for Oracle and Teradata


• SSIS Data Source Types and Capabilites Chart for SAP , Siebel , Hyperion Essbase , Oracle , Sybase , IBM DB2 , Teradata , Excel , MS Access , MSMQ , Tibco , etc..

ValidateExternalMetadata property, DelayValidation property SSIS , Package Validation in SSIS , Long time to load packages due to validation in BIDS

When you open a SQL Server Integration Services (SSIS) package in SSIS Designer or add components to a package, SSIS by default checks the data sources used by the various components. This process for SSIS package validation ensures that the external metadata is valid. If the metadata is not valid, you'll receive warning or error messages pointing to the problem. At times, you might want to override the default behavior and open the SSIS package without validation.

• To prevent these connection attempts, you can also enable Work Offline from the SSIS menu. Unlike the DelayValidation property, the Work Offline option is available even before you open a package. You can also enable Work Offline to speed up operations in the designer, and disable it only when you want your package to be validated.


• If you have multiple SSIS projects in a single solution, there is a way to enable the Work Offline option prior to actually opening the project. Locate the BIDS *.user file associated with the project. For SSIS, this file should be located in the same folder as the project (.dtproj) file, and will have a filename like “[project].dtproj.user”. Open this file in Notepad, locate the [offlinemode] tag and change the value from false to true. Now, when the project is opened, it will already be in Offline mode, so you won’t have to suffer through a lengthy validation process.


• You can set DelayValidation to True on package elements whose configuration is not valid at design time to prevent validation errors. For example, you may have a Data Flow task that uses a destination table that does not exist until an Execute SQL task creates the table at run time. The DelayValidation property can be enabled at the package level, or at the level of the individual tasks and containers that the package includes.


• The DelayValidation property can be set on a Data Flow task, but not on individual data flow components. You can achieve a similar effect by setting the ValidateExternalMetadata property of individual data flow components to false. However, when the value of this property is false, the component is not aware of changes to the metadata of external data sources.


• If database objects that are used by the package are locked when validation occurs, the validation process might stop responding. In these circumstances, the SSIS Designer also stops responding. You can resume validation by using Management Studio to close the associated session in SQL Server. You can also avoid this issue by using the settings described in this section.

Reference: MSDN BOL

SSIS Package Object Model

Reference: SQLIS.com

SQL Server 2008 R2 - Official release of Microsoft SQL Server version code-named Kilmanjaro

I was under the impression / information that SQL Server 2010 is the codename of SQL Server 2010. Microsoft has finally raised the curtain on this speculation. The official release of Microsoft SQL Server 2008 version code-named Kilmanjaro is SQL Server 2008 R2. The details of this release can be found on it's official page.

There are many striking enhancements in this release. When I opened it's official page, I was looking out for BI capabilites in this release. Project Gemini features looks like the replacement of Monitoring and Analysis module of Performancepoint. And the main USP of the release, as per my views would be Master Data Services which I feel is the microsoft's offering of MDM.

Below is an excerpt of Master Data Services from the site page:

Master Data Hub
Improve the Quality of Your Data

• Centralized approach to defining, deploying, and managing master data
• Ensure reporting consistency across systems and deliver faster more accurate results across the enterprise
• Serves both BI and operational requirements and improve the quality and efficiency of data and processes for all systems
• Enables organizations to track versions of master data to answer questions about master data at a specific point in time
• Supports integration through a Services (WCF) API, business rules, workflow, notification, and hierarchy management

Stewardship Portal
Improve the Quality of Your Data

• Central portal to create, edit and update master data master including members and hierarchies
• Enables direct stewardship and ensures data quality by enforcing business rules and managing supporting domains
• Enables model administrators to upload data, review validation status, manage data versions, develop models, copy versions of models and define the structure of hierarchies

Getting feedback / progress from batches and stored procedures

Many a times, application development comes up with a requirement where if a stored procedure takes a certain period of time (say for ex. more than 30 secs) then the procedure should get terminated. One of the better option can be updating user with the status of what operation is being performed. This means updating the progress status of a stored procedure.

RAISERROR is the key to this solution. But before proceeding, there are certain facts which once should be aware of about RAISERROR:

• RaisError used with the wrong severity (second parameter) might terminate the batch or the connection, severities less or equal to 10 will just send an informational message to the calling stack.
• Syntax: RAISERROR (Message, Severity, State) –> e.g. RAISERROR (‘SomeMessageForYou’,0,10)
• Although the Information messages are a good way to send life signals back to the client, SQL Server will batch these informational messages also together up to the end of the batch unless you use the WITH NOTWAIT option of the RaisError.

Basically at the client, the following code can be used:

conn.FireInfoMessageEventOnUserErrors = true;
conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);

In the conn_InfoMessage method we are evaluating the information from the thrown “errors”, determine the calling batch from the Message text of the error and the progress from the state.

if (Message == "Batch1")
{ progressBar1.Value = PercentageComplete; }

else if (Message == "Batch2")
{ progressBar2.Value = PercentageComplete; }

Reference: While wandering the blogosphere, I came across an interesting blog post from a MCS consultant named Jens. This post is my version of explanation from the original post.

Task to watch for files in a directory missing in SSIS Task Repository

I have worked on a couple of huge data warehousing projects. I have observed that, one of the common requirement in a typical ETL project remains of watching the files coming down from the source system. And this becomes a must if the source system in a Mainframe system.

The general architecture remains like this:

1. Feeds arrive from the source system in case if the source is some legacy system like mainframe. Also there may be jobs which fetch data from the OLTP system via push/pull methodology and then it's staged into a text file.
2. Feeds are guided to the target ETL directory through some schedulers, for ex. Autosys
3. A File Watcher remains on the ETL system which detects the presence of the file/feed, picks up the same and then after ETL, the same gets loaded to either a staging DB or to the destination DB.
4. Generally it's BCPed to the staging DB, and after application of business rules, it's loaded into the destination DB or data warehouse.

In SSIS what I have seen missing is the File Watcher task. It needs to be implemented by either infinite looping to watch for a file (it's a very bad methodology of implementing the functionality) or using WMI for the same, or finally thru a work-around by signalling mechanism or through scheduling.

I found a similar implementation of the same in SQLIS, where a component called "File Watcher Task" is available. But seems like it has serious limitations and is also not bug free. I am just assuming this from the comments and components description. May be Microsoft should make this functionality available through some service packs or at least in the next version.

Writing Test scripts for T-SQL : Tool to test T-SQL code

Have you ever heard of "TDD in SQL Server" or "TDD in T-SQL"? If Yes, your general awareness is quite higher than me. Firstly when I heard of it, I got confused with what's TDD? Then I got it, it's Test Driven Development using SQL Server or T-SQL.

And if someone is reading my blog for a while, then you must be able to guess it right. Yes, there's a tool for testing T-SQL or in general this tool can be the starting point for TDD. This tool is called "T.S.T. The T-SQL Test Tool". I went thru the help file of this tool, but honestly speaking, I have not seen TDD with this much effort in any of my development projects till date. TDD is a nice paradigm, but in practice, in pressure projects where the timelines are tight, it's not generally feasible to go thru this paradigm, especially when one is expected to deliver 10 medium to complex SP's per developer per day. It can also be said, that I have not yet got a chance to get familiar with this paradigm of development in T-SQL.

The project description as posted on the tool site says: "TST is a tool that simplifies writing and running automated tests for code written in T-SQL. At the heart of the TST tool is the TST database. This database contains a series of stored procedures that represent a test API. Part of this API is similar with those found in Unit Testing libraries familiar to programmers in C# or Java."

SQL Tools : SQL Server Blocked Process Monitor Tool

Welcome a new SQL Server tool on the block, "SQL Server Blocked Process Monitor". It's available on Codeplex. The project description as available on the tool's site is as below:

"The SQL Server Blocked Process Monitor makes monitoring, tracking and troubleshooting blocking inside SQL Server 2005 and SQL Server 2008 easy. You'll no longer have to manually enable and run a trace to capture the blocked process report. It is developed in C#."

Also one update that I got from Jonathan Kehayias blog is that it requires the following componets to execute. Below is an excerpt from his blog. A few of these components are the minimum requirements to install SQL Server 2008 itself. Honestly speaking this tool can be a collectible for DBAs, but being from the Data warehousing side, as of now I don't find much use of it for me. May be some DBA would like to review this one.

Excerpt:

If you want to test the application, you can do so if you have .NET 3.5 installed and you install the SQLCLR Types and SQL SMO features from the SQL Server 2008 Feature Pack: http://www.microsoft.com/downloads/details.aspx?FamilyId=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en

Windows Installer 4.5 Redistributable:

http://www.microsoft.com/downloads/details.aspx?familyid=5A58B56F-60B6-4412-95B9-54D056D6F9F4&displaylang=en

Microsoft .NET Framework 3.5 Service Pack 1 (Full Package)

http://download.microsoft.com/download/2/0/e/20e90413-712f-438c-988e-fdaa79a8ac3d/dotnetfx35.exe

Microsoft SQL Server System CLR Types x86:

http://go.microsoft.com/fwlink/?LinkId=123721&clcid=0x409

Microsoft SQL Server 2008 Management Objects x86:

http://go.microsoft.com/fwlink/?LinkId=123708&clcid=0x409

This project will be split into a version for SQL Server 2008, which is what exists today, and another version targeting .NET 2.0 and SQL Server 2005, which will be coded correctly in the next release.

SSIS Performance : Webcast / Video / Training / Reference

Here is some valuable content out from Microsoft on SSIS Performance.

Measuring and Understanding the Performance of Your SSIS Packages in the Enterprise (SQL Server Video)
by Denny Lee, Senior Program Manager on the SQLCAT team

Tuning Your SSIS Package Data Flow in the Enterprise (SQL Server Video)
by David Noor, Senior Development Lead on the SSIS team

Understanding SSIS Data Flow Buffers (SQL Server Video)
by Bob Bojanic, Developer on the SSIS team

Designing Your SSIS Packages for Parallelism (SQL Server Video)
by Matt Carroll, Senior Development Lead on the SSIS team

Reference: Dougbert's Blog

Enterprise Policy Management Framework with SQL Server 2008

Before 2 months, I posted an article titled "Policy Management Tool / Framework for SQL Server" in which I presented my views on the Enterprise Policy Management Framework.

Recently I found a nice whitepaper on the same, which can be of good use for documenting purposes and for presentation preparation if wants to get approval from senior management for use of this framework within the organisation.

It seems like Microsoft is also interested in promoting this framework as it seems a good value addition to the feature set of SQL Server 2008. Leveraging the capabilities of SQL Server 2008 in a controlled and desired fashion in the direction of compliance can be termed as Enterprise Policy Management Framework. This whitepaper can be download from MSDN from the following link.