Archive for October, 2009

Comparing tables with TABLEDIFF

October 30, 2009

Recently, I stumbled across a pretty cool utility called tablediff.  This utility is shipped standard with Microsoft SQL Server and is used to compare the data in two tables for non-convergence (dissimilarities).  The contingencies for this tool require that the tables being compared both have a primary key or a similar field.  This utility can be used from the command prompt or in a batch file which means it can be called from SSIS.   (more…)

Creating Dyanamic Email Content using SSIS

October 27, 2009

From time to time, I will be required to send emails with dynamic content to a number of users within an organization.  Typically, a user will want data in that email that is relevant to them or relevant to their subordinates for reporting/notification purposes.  Thankfully, SSIS can handle this task fairly easily.

(more…)

Connecting to an XLSX using SSIS

October 16, 2009

You have likely noticed that documents, templates, spreadsheets, and presentations that you create in the 2007 Office release are saved with new file-name extensions with an x at the end of the extension. For example, when you save a spreadsheet in Excel, the file now uses the .xlsx extension, instead of the .xls extension.  In the 2007 Office release, Microsoft has adopted and XML-based file format that is said to improve file compression and provide better integration and interoperability of data.  Unfortunately, SSIS cannot connect to an XLSX file using the Excel Connection Manager. 

(more…)

Zip Utility Compression Test

October 14, 2009

No matter how much storage space you have, it’s never seems to be enough.  This is when file compression can become crucial to saving space on your file system, moving files quickly between servers and performing day-to-day tasks.  With just a quick search there are an overwhelming number of file compression utilities on the market.  This post outlines a test of 3 popular utilities for both file compression and execution time.

(more…)

Dynamically Zipping Files in SSIS

October 13, 2009

There are a number of posts and articles regarding zipping files in SQL Server Integration Services (SSIS). I have found it fairly easy to accomplish this task using an open source zip utility called 7-zip. This utility allows file compression to be executed from the command line, making it very easy to utilize the ‘Execute Process Task’ control flow. 7-zip allows packing and unpacking of ZIP, 7z, GZIP, BZIP2 and TAR and typically provides better compression than WinZip. In this post, I will explain how to dynamically zip files with dynamically defined connection managers and attributes. (more…)

Performance Tuning in SSIS

October 2, 2009

Tuning SQL Server 2005 Integration Services for EBCDIC Data Conversion

This article talks about tuning the dataflow for 5 million EBCDIC records at 497 bytes per record.  Impressively, the execution time for the dataflow task was cut almost in half by employing a number of tuning practices. 

Read the article at:

http://www.sql-server-performance.com/articles/per/ssis_ebcdic_conversion_p1.aspx

SSIS Logging Package Execution Results

October 2, 2009

I have seen some other posts like this one out there, but I am biased and think my posts are better because I have “prettier” pictures and generally go into more depth on each specific subject. 

In some ETL scenarios, it is necessary to log some or all events at a number of levels. In SSIS, there are a number of ways to do that.  This post will describe how to create a new uniquely named log file for each package execution.

(more…)

SSIS Derived Column Transformations

October 2, 2009

In some ETL scenarios, it is necessary to transform certain data elements such as dates or numeric fields.  For instance, you may have a field that reads 090501; which literally needs to be translated into a date of 05/01/2009.  Other times, you may have a numeric field that does not allow nulls but in the data it is a blank field where ” ” would need to be represented as 0.

In SSIS you can accomplish transformations like these using a Derived Column Transform in your Data Flow task.

(more…)

Thoughts on Error Handling in SSIS

October 2, 2009

I have been thinking about a number of issues that need to be accounted for when developing solutions in SSIS.  Some of them relate to data profiling, but a number  of them relate to SQL tasks, Data Flow and referencing the file system.  Please feel free to add to this list or comment on any of these items.

I will follow up this post with some posts on logging and error handling.

(more…)

Using SSIS to convert DBF files

October 2, 2009

dBase is database management system (DBMS) similar to SQL or Oracle for microcomputers that was originally developed in the early 1980’s.  Over the years, it has lost significant market share to competitors like Microsoft Access.  dBase’s underlying file format, the .dbf file, is widely used in many other applications needing a simple format to store structured data.

I rarely see any .dbf files but occasionally they do pop up and someone needs them converted.  In SSIS you can load this data by using the Native OLE DB\Microsoft Jet 4.0 OLE DB provider.

(more…)