Eric Johnson

Calling Child Packages in SSIS

2010-10-28T17:48:00Z

Calling an SSIS package from another SSIS package is, on the surface, a simple task. You just use the Execute Package Task and point it to a package on a SQL Server or somewhere in the file system, as shown below. It is pretty simply right? But what if you need the parent package to pass information to the child package? That is accomplished by using variables in the parent package and package configurations in the child package. Let’s look at an example. I have created two packages, one named Parent...(read more)

Virtual Classes

2010-10-26T20:50:18Z

I have two online classes starting on November 1st that I recorded on the SSWUG.org vClass platform. The first is “Introduction to SQL Server Replication” and is a 12 session class on all things replication. Cost is $199……for more information or to register, click here: http://www.vconferenceonline.com/event/home.aspx?id=107 The second class is “SQL Server Integration Services” and is also 12 sessions. This class covers basic to advanced topics on SSIS. Cost is $199……for more information or to register,...(read more)

The SSIS Pivot Transformation

2010-10-20T17:47:00Z

The Pivot transformation is one of the most useful transformations in SSIS, and also the biggest pain to configure. It comes with one editor, the Advanced Editor, so no pretty GUI here. Let’s walk though an example and hopefully it will help you learn how to use pivot. First off, let’s define the source data and the destination of our pivoted data. We will worked from a small set of survey data where each person was asked four questions, see the table below. Question Answer SurveyID Name EricJohnson...(read more)

TSQL TRY…CATCH

2010-10-12T17:46:00Z

Transact-SQL is a great language for data manipulation, but it has its weaknesses. Unlike “real programming languages” T-SQL is confined to procedural code. Sure, you can build “modules” by using stored procedures and functions, but for the most part, all of the work will be procedural. It has in the past also lacked error handling syntax leaving you with the need to write GOTO statements and labels to control the flow. Well, if you hadn’t noticed, SQL Server 2005 introduced TRY…CATCH blocks to T-SQL....(read more)

Working with Text Data Sources

2010-10-05T13:00:00Z

A common source for SSIS packages is the flat file. These are just plain text files that are delimited by some character such as a comma, pipe, or tab. The problem is when you connect SSIS to a flat file it assumes a few things about the file, specifically the data types of all the columns. Flat files do not contain metadata so SSIS has no good way to know the data type. Further, SSIS does not look at the data and attempt to make an educated guess. What you end up with is all the columns configured...(read more)

Data Conversion in SSIS

2010-10-01T18:43:17Z

When it comes to converting data from one type to another in SSIS, you have a few choices of methods. We are going to look at a few options and discuss the differences. With all of these, we are assuming that the input data type is compatible with the new output data type. In other words, we are not converting the word “one” to the number 1, this would require fancier logic. So first up, there is the appropriately named Data Conversion tranformation. This task will take one or more input columns...(read more)

SSIS Virtual Class

2010-05-13T17:54:39Z

I recorded a Virtual SSIS Class with the good folks over at SSWUG and the first airing of the class will by May 15th. This is 100% online so you can do it on your own time and from anywhere. The class will run monthly and I will be available for questions through out. You get the following 12 sessions on SSIS, each about an hour. Session 1: The SSIS Basics Session 2: Control Flow Basics Session 3: Data Flow - Sources and Destinations Session 4: Data Flow - Transformations Session 5: Advanced Transformations...(read more)

Getting Dynamic in SSIS Queries

2010-03-22T15:00:00Z

When you start working with SQL Server and SSIS, it isn’t long before you find yourself wishing you could change bits of SQL queries dynamically. Most commonly, I see people that want to change the date portion of a query so that you can limit your query to the last 30 days, for example. This can be done using a combination of expressions and variables. I will do this in two parts, first I will build a variable that will always contain the 1 st day of the previous month and then I will dynamically...(read more)

New T-SQL Functionality in SQL Server 2008

2010-03-15T15:00:00Z

In my most recent posts I have looked at a few of the new features offered in T-SQL in SQL Server 2008. In this post, I want to take a closer look at some of the smaller additions, but additions that are likely to pack a big punch in terms of efficiency. First let’s talk a little about compound operators. This is a concept that has been around in programming languages for a long time, but has just now found its way into T-SQL. For example, the += operator will add the values to the current variable...(read more)

Script Data in SQL Server 2008

2010-03-08T16:00:00Z

Here is a feature that I have been waiting for since I started working with SQL Server 6.5; the ability to script the data from a table. When you deploy a SQL Server database, you often need to include data in several tables. This can be for lookup values, configuration tables, or just seed data. In the past, you had to manually write a script to insert the data. You could also write a creative SELECT statement that would include hard coded text to append the ‘INSERT INTO……’ logic into a string and...(read more)

SQL Server 2008's MERGE Statement

2010-03-04T16:00:00Z

SQL Server 2008 provided us with some new T-SQL syntax; one of those that I am most excited about is the new MERGE statement. Gone are the days of IF....THEN logic to decide whether a row needs to be inserted, updated, or deleted. The MERGE allows you to take care of the logic and the insert all in one shot. What's more, you can compare an entire record set all at once instead of going row by row. Here's is a quick example of using MERGE. MERGE tbl_address AS current_addresses USING( SELECT customer_objid...(read more)

Using SQL Server 2008's EXCEPT and INTERSECT Statements

2010-03-01T21:29:06Z

SQL Server 2008 shipped with several enhancements to the T-SQL language. In a recent blog, we looked at the new MERGE syntax; today we are going to look at the new EXCEPT and INTERSECT syntax. Both of these operators allow you to compare results of two queries. Using EXCEPT, you can find all the rows that exist in one query but not the other. Using INTERSECT, you can find all the rows that are the same in both tables. This makes more sense with an example so let’s take a look. I took a copy of the...(read more)

The Trouble with SSIS Sorting

2010-02-03T14:00:00Z

Many SSIS transformations, such as the Pivot and the Data Profiling Tasks, require that the data being fed into them be sorted. Without first sorting, some transformations will throw an error and not run, while others will run but the results will not be as expected. The answer is to sort the data before it is fed into the transformation; the problem is that this isn’t as simple as you may think. The obvious and simple way to accomplish this is to use the Sort Transformation in SSIS. The Sort Transformation...(read more)

Understanding the SSIS Package Protection Level

2010-01-12T17:46:05Z

One property of all SSIS packages that you must understand is the ProtectionLevel. This property tells SSIS how to handle sensitive information stored within your packages. Most commonly this is a password stored in a connection string. Why is this information important? If you don’t set the ProtectionLevel correctly, the package may become unusable. Other developers may be unable to open the package or the package may fail when you go to execute it. Understanding these options lets you get out in...(read more)

Troubleshooting a Failed Maintenance Plan

2009-12-23T20:20:40Z

I recently ran into an odd little problem with a Maintenance Plan that I wanted to share. I had a plan that was running the Check Database Integrity Task that suddenly started failing. Nothing about the databases had changed and if you ran a manually DBCC CHECKDB all the databases came back clean. The job history showed a meaningless, truncated message: Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress ...(read more)