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 will complete the objective of sorting the data, but the performance might not be exactly what you would expect. The Sort Transformation does all its work in memory; SSIS packages do things in individual sets. This means your data may need to be sorted and resorted again. If you try to work with large data sets, this sorting will take a long time and use a lot of memory. The solution is to sort the data from the source using an ORDER BY clause. That alone, however, won’t get it done. You also have to tell SSIS that the data is sorted and how it is sorted. This is done in the Advanced Editor of your data source. First, set the IsSorted property of the source’s output to true. Next, set the SortKeyPosition property of each column you specified in your ORDER BY clause; 1 for first, 2 for second, and so on. A SortKeyPosition value of 0 is the default and means that the column is not used in the sort. That’s all there is to it, using this method you will be able to sort your data so the SSIS transformations are happy, and can work without hours of waiting and memory hogging.

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  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  10:14:30 AM  Finished: 10:14:31 AM  Elapsed:  1.185 seconds.  The package execution failed.  The step failed.

When looking at the Maintenance Plan history directly, I had an error that stated: Alter failed for Server 'ejohnsonmobile'. Really nothing at this point has provided any details into a possible fix. Next step was to run a Profiler trace to see what was really going on. When setting up a trace to look for errors, be sure to include the User Error Message and Exception events. Since this is a Maintenance Plan, which is just an SSIS Package, the trace will show you that there is a lot more going on than just the CHECKDB. When looking for errors in the trace I noticed an exception just after the following query was run:

EXEC sys.sp_configure N'user options', 0 RECONFIGURE

The exception this caused was:

Error: 5808, Severity: 16, State: 1
Ad hoc update to system catalogs is not supported.

That’s a little odd, the query was modifying the User Options configuration value, why would we see an error about Ad Hocs updates? Here is where the problem becomes clear. At some point, the Allow Updates configuration option was set to 1. In SQL Server 2000 and older, this option allowed users to make updates directly to the system tables. In SQL Server 2005 and newer, the system tables are gone, replaced with the Resource Database and system views. This option is no longer supported in SQL Server 2005 and beyond, and though you can set Allow Updates to 1 with no error, as soon as you run RECONFIGURE, you will receive the error that ad hoc updates are not supported. Because Allow Updates had been set to 1 and my Maintenance Plan runs the RECONGIFURE statement, this error was thrown and the Maintenance Plan failed.

All I had to do was run “sp_configure 'Allow Updates', 0” to set Allow Updates back to 0 and then everything started working again.

So you have written an SSIS package and now its time to package it up and send it off to your QA department. Excellent; you need to use the build function in Business Intelligence Development Studio. On the surface this is pretty simply but there are a few things to understand. You can’t just right click your project and select Build, or even use the Build Solution menu option. Before you can do either of these things, you need to adjust your project’s build settings. To do that, right-click the project in Solution Explorer, select Properties, and then select Deployment Utility. This should have you looking at the follow dialog.

Notice that the CreateDeploymentUtility option is set to False. This must be set to true before you can build your project. The other setting of interest is DeploymentOutputPath which controls where the build will be written. Set CreateDeploymentUtility to True and click OK. Now you can right-click the project in Solution Explorer and select Build. This will write several files out to your deployment directory. The number of files will vary based on your project; in the case of my test project three files were written.

1. Package.dtsx – The actual SSIS package that was developed. You should have one DTSX file for each package in your solution.

2. Config.dtsConfig – This is the XML configuration file for my package. You could have several of these, or none, depending on whether your packages uses them

3. Test.SSISDeploymentManifest – This is the Deployment Manifest file. You will have one of these per project. This file defines all the pieces of your project and is what you use to deploy the SSIS package to a server.

Now that you have built your package, you can send all the files in your deployment directory to another person or group so that your package can be installed on an SSIS server. That process is simple; just double-click the SSISDeploymentManifest file on a machine that has the SQL Server client tools installed and this will launch the Package Installation Wizard, shown below.

This is a pretty simple wizard, but let’s quickly run through it. First you specify whether you want a File System Deployment or a SQL Server deployment. For this example I will choose SQL Server. Next you choose the location for the package, in our case the SQL Server and the path in MSDB. You can also choose to Rely on Server Storage for Encryption if you want sensitive information that was encrypted in the package to remain after the package has been deployed. Last, you must specify the directory on the server where you want dependencies files, such as configuration files, to be stored. If you have configurations, you will also be allowed to modify the configuration values during the wizard. Keep in mind these can also be modified later.

That’s it, click Finish and the package will be installed on your SQL Server where it can be scheduled and run.

I have written a lot lately about SSIS package development, and that doesn’t apply to everyone that works with SQL Server 2008. So you might be asking, what’s does SSIS have to offer me, the production support DBA? Well, I am glad you asked. In SQL Server 2008, the Import and Export Wizard is built on the SSIS framework. In fact, at the end of the Wizard, you can save your settings into an SSIS package. If you have used the Wizard before, it will feel much the same, choose a source, choose a destination, specify your data, and go. In fact, if you don’t look close you may miss some of the new features.

First, when you choose a destination or source, you will see the .NET Framework Data Providers in the drop downs. This means you can run the import and export wizard directly against Oracle as well as ODBC data sources.

Next, there is a whole set of mapping files that define how data from one data source should map into destinations that are of a different type, Oracle to SQL Server or SQL Server to DB2 for example. These XML files can be found here (Assuming you installed to C:\): C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles. During the wizard, you can accept the decisions made by using these files or you can edit the data type mappings. For example, below is a snippet of the OracleToMSSql file which maps Oracle data to SQL Server data:

<dtm:DataTypeMapping >
        <dtm:SourceDataType>
            <dtm:DataTypeName>NUMBER</dtm:DataTypeName>
        </dtm:SourceDataType>
        <dtm:DestinationDataType>
            <dtm:NumericType>
                <dtm:DataTypeName>NUMERIC</dtm:DataTypeName>
                <dtm:UseSourcePrecision/>
                <dtm:UseSourceScale/>
            </dtm:NumericType>
        </dtm:DestinationDataType>
    </dtm:DataTypeMapping>   

As you can see, NUMBER in Oracle maps to NUMERIC in SQL Server. Lastly, the wizard has been tuned to scale better. For example, if you import a large number of tables, they will be spilt across multiple data flows to reduce concurrent processing.

In the past, the import and export wizard has been a bit of a last resort for DBAs, but now I think its time to give this old dog a second chance.

The Lookup transformation in SSIS has changed a lot in SQL Server 2008. One of the best new features is the ability to pre-build your cache which gives you a lot of control over what is cached and how the cached data is managed. The basic lookup offers additional cache features such as Full, Partial, or even No Cache, but the real power comes in the new Cache Transformation. The Cache Transformation uses the new Cache Connection manager to allow you to build your cache before it is needed. The connection manager and the Cache Transformation are pretty simple. For the Cache Manager Connection, you just need to specify the columns that will be stored and which columns will be indexed, as shown below. The indexed columns will be the only columns you can use as a lookup column in a Lookup Transformation. 

Once you have the connection manager configured, you just use a Cache Transformation to pump data into the cache. The Cache Transformation is very simple; you specify a Cache Connection manager and set up the mapping of the incoming data to the columns in the cache. Last but not least, you pull data from your source and send it into the Cache Transformation. I have a very simple example data flow below which is populating a lookup cache; and the source in this example uses the T-SQL shown.

SELECT ContactID, EmailAddress
FROM Person.contact
WHERE EmailAddress IS NOT NULL
AND EmailPromotion <> 0

This allows me to populate my cache with all email addresses that are not blank, where the person has not opted out of email promotion. Remember, the data flow to populate your cache can be as complex as you want. This can really allow you to pare down large sets of data to a smaller, easier to use data set that is appropriate for caching.

Once you have populated your cache, all you have left to do is use it in a Lookup Transformation. On the first page of your Lookup Transformation properties, specify a Connection Type of Cache Manager and on the connection page, specify your Cache Connection Manager object. Everything else is just like using the Lookup with a regular data source. You will need to set up your mappings and the column (or columns) that you want to return. One other note: when using a Cache Connection Manager with a lookup, you have to select Full Cache as the cache mode. The other two are disabled and for good reason. You have taken control of your cache at this point and don’t want SSIS doing anything further.

So there you have it, managing your own lookup cache should allow you to make your SSIS packages a little more streamlined and allow them to run a bit faster. Not to mention, you should be able to minimize the memory hogging packages that occurred when older lookups got a little out of hand.

SQL Server 2008 Integration Services shipped with the wonderful new Data Profiling Task. This task, much as the name implies, will profile the data in a given table and return a ton of useful information. The task gathers statistics for candidate keys, column length, NULL data ratio, data patterns, and much more. All of the results are written out to XML. The task makes more sense when you look at it, so let’s jump in. The configuration of the task is pretty simple, you select the types of profiles that you want to run and then set the specific options for that profile. In the figure below, I have selected Column Length Distribution for all columns (*) in the HumanResources.Employee table. This profile will gather length statistics on the columns such as Minimum and Maximum Length and provide a distribution of all of the lengths found.  On the general page of the task, you also need to provide a destination, which can be a file connection or a variable.

Once you set up the task with all the data profiles you want to run, you simply execute the SSIS package and let it work it’s magic. If you wrote to a variable, you’re on you own to do something with the data within the SSIS package. On the other hand, if your wrote to a file, the SQL Server 2008 Client Tools come complete with the Data Profile Viewer which can be found at in your program menu under Microsoft SQL Server 2008 | Integration Services. This is a pretty simple application that allows you to browse the XML file created by the Data Profiling Task. The figure below shows the results for the Column Length Distribution profile I ran earlier. As you can see, there is quite a bit of information returned, and this is just one of the profiles. If you take some time and dig into all the profile types, I think you will be very impressed with the amount of information this task provides. If you’re the creative type, you can run this task and write the data to a variable which can be further queried by your SSIS package. With a little work, you could have a great tool at your disposal the next time your presented with a new database that you nothing about.

SSIS is a pretty useful tool for designing ETL processes. One of the transformations I was disappointed with in 2005 was the lookup. I found it a little better than the lookup functionality in DTS, but not too much better. Well, Microsoft must have read my mind because SQL Server 2008 has a new shiny Lookup transformation that is, in my opinion, much improved.

In 2005, the lookup had cache options, but they were really heavy handed. And you could deal with a lookup not finding a match by using the error output, but then how would you know the difference between a non-match and a real error? Let's start with the cache. There are now three cache options and two choices for your cache source.



• Full Cache - This will load the entire reference dataset into memory before the first lookup is performed. This can be very efficient for small tables, but think what would happen if your lookup table was a few GB in size….that's a lot to load into cache.
• Partial Cache - This is probably what most people think of when they conceptualize lookup cache in their head. At first, the cache is empty and each time a row matches or doesn't match for that matter, the row, or the fact that the lookup didn't find a row, is cached. Subsequent rows with the same lookup fields will find their data, or lack thereof, in cache.
• No Cache - The lookup will generate the reference dataset each time the transformation runs.

As for data sources for your cache, you can still go to the database, but now you can also set up your own custom cache connection. This gives you the ability to build your cache in a separate step and then reference it with your lookup. This is a little involved so I won't go into more detail here, but keep your eyes open for another blog entry... ;) Furthermore, if you use the partial cache, you can manage your cache size on the Advanced page of the Lookup's properties.

So I like the changes to caching, but I like the output options even more. You can now specify that rows with no match be redirected to their own output, or be ignored all together. This now gives you three outputs to work with; one for a match, one for no match, and another for errors. I put together a small sample package and added a screen shot below, you can see that I am using all three outputs. In this case, when an e-mail address is not found in the lookup, I use a derived column to put "NA" into the email address column before I load. I then union this data with the rows that found a match and do an insert into my destination table.

In the end, this new lookup is much cleaner and easier to use than its 2005 predecessor. Plus the addition of the Cache Connection Manager is a big win…..more on that coming soon.



 

The Slowly Changing Dimension (SCD) transformations are pretty handy in SSIS packages. They let you compare a new set of data to an existing table and insert or update as needed. Alternatively, you can have them insert new rows and mark old rows as "Expired" instead of updating rows when a change occurs. This is great if you want to maintain history. Now these are meant for dimensions in a data warehouse, but you can use them against any table in your database. The reason for this post is to look at an issue that can occur with SCDs when you have duplicate data in your incoming data set.

When an SCD analyzes a set of data it looks at each row and compares it to the destination. It decides whether the row will be inserted or updated (or inserted with the old row being marked inactive). Notice I said this comparison is with the incoming data and the destination. The SCD does check for duplicates that may exist in the incoming data. So if you have duplicates, that also happen to be new rows when compared to the destination, all the duplicate rows get "flagged" for insertion. You can probably guess what happens next. The first row gets inserted but the second and subsequent duplicates cause a Primary Key violation when they attempt to insert the now duplicate record.

Now this might seem like a glitch to some, but this functionality is "by design ". The button line is that the data being inserted should be de-duplicated prior to being inserted into the destination. In a perfect world, the data would come from a source where it lived in a normalized, and therefore de-duplicated, state. If you don't have the luxury of living in a perfect world, you can build a manual data de-dup process or use a Sort transformation. One of the options of the Sort transformation is to "Remove Rows with Duplicate Sort Values". Just drop one of these in, check the box, and you have magically de-dup your values.