December 2005 - Posts

Integration Services Error and Message Reference

Finally, it has arrived. Well 2006 has arrived almost....whooohooo. It's just not as sexy as Y2K though, is it?

The list in the below link shows all the predefined Integration Services errors, warnings, and informational messages, in numerical order within each category, along with their numeric codes and symbolic names. You can use this to properly trap for errors and provide better user errors.

-- Brian Knight

Professional SQL Server 2005 Integration Services Sample Chapter

In a few weeks, my new book will ship out called Professional SQL Server 2005 Integration Services. The book was a collaborative effort that involved 9 other fantastic authors incuding 3 SQL Server MVPs. The book is in tutorial form so after you're introduced to a concept, you're walked through it in example form. Special thanks to all the SSIS team at Microsoft for tech editing the book!

You can now download a sample chapter below, which contains a large tutorial that may help you get started with SSIS. I love using this example in my speaking sessions because it shows the bulk of the features from SSIS and is a real-world scenario involving looping and archiving of files. Hope you enjoy!

-- Brian Knight


* - Buy the book on Amazon.

* - Download Chapter 5 - Creating an End-to-End Package

Setting Variables in the Script Task in SSIS

One of the key reasons that you use the script task is to change the value of a variable at runtime. There is a lot of real-world scenarios that you would use this for. If you're reading this you have already probably thought of a few. To set a variable in the script task, there are two main methods you can use.

Method 1

This method involves using the LockOneForWrite method in the VariableDispenser class. The advantage to this method is it allows for you to read and write to the variables at runtime without having to use the ReadOnlyVariables and ReadWriteVariables options in the Script task. The price of that though is that you have to write quite a bit more code. In the following example, I'm going to open the AlertAdmin variable for writing and then set it to the boolean value of True.

Public Sub Main()
Dim vars As Variables
"AlertAdmin", vars)
   vars("AlertAdmin").Value = True
   Dts.TaskResult = Dts.Results.Success
End Sub

Method 2

The second method is typically what I would recommend using just for simplicity. In this method, you would open the Script task and before clicking on Design Script, you must ReadOnlyVariables and ReadWriteVariables options. If you have more than one variable you wish to be available in the Script task, you can seperate them with commas. If you do not set this, you will not see an error at design time but at run time, you'll receive the error shown below: 

The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)

at ScriptTask_8693feb81f2d4b7b8a26ae87a8a5f960.ScriptMain.Main() in dts://Scripts/ScriptTask_8693feb81f2d4b7b8a26ae87a8a5f960/ScriptMain:line 19

With the variables now being passed in, you can perform the same type of action as I showed in the earlier script in a single line (the second line shown below). The locking is done by the Script task UI.

Public Sub Main()
"AlertAdmin").Value = True
   Dts.TaskResult = Dts.Results.Success
End Sub

The ReadOnlyVariables and ReadWriteVariables options are available to you in the Expressions tab too if the variable name that you wish to pass in is unknown or dynamic. There are other ways to set the variables of course. Most tasks have hooks into the variables like the Execute SQL task. Hope this helps!

-- Brian Knight 

Templates in SSIS

Templates in SSIS provide a great way to create standards across your company or enterprise. For example, you may want to enforce a standard annotation set of notes at the top of each package or have each package come with a standard set of connections or error handlers to speed up development. To use a template, first create a package just as you would want to see it as a template. Add all the connections, tasks, comments or log providers. After the package meets your needs, copy it to the following directory (of course replace %Program Files%) : %ProgramFiles%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems

For a developer to consume the template, he must only right-click at the project-level node in BIDS and select New Item. You'll see the package you copied over there and once selected, all your information you created earlier is ported over.

-- Brian Knight

What Log Provider Type is Best in SSIS?

SSIS has the ability to do very detailed logging that puts DTS Package Logs to shame. In the past, you would have to had to write customized logging routines to do what SSIS does out-of-the-box. SSIS offers a number of logging providers that you can select by going to SSIS menu and selecting Logging. You must then chose what type of provider you wish to use for logging. Which provider you chose depends on your goals:

  • SQL Server Profiler create a profiler trace file - This option gives you some interesting ties in with Profiler if you're examining performance issues like long-running packages. With this seamless integration, you can quickly tie the log to a System Monitor trace to see performance counters.
  • SQL Server Table writes events to a SQL Server table. -  This option is my personal favorite since it allows for rich reporting using Reporting Services or the reporting tool of your choice. This is a must-have for most environments that need operational reports in real-time. I like to create reports against the table that refresh so you can see all the running packages and their progress.
  • Windows Event Log writes each event to the Windows Application log - What makes this choice a nice one is it creates hooks for monitoring products like Tivoli or Microsoft Operations Manager (MOM) to use for alerts.
  • XML File format writes each event to a structured XML file -  This can also be used for reporting on a website if you use an XSLT file for formatting. It works well if you need to share the information with a 3rd party especially.
  • Text file writes to a simple flat file - This option is the easiest to configure and is the most understood by traditional tech-heads.

What's especially nice about the new logging mechanisms is you don't have to chose just one. You can use for example the table for relational reporting and the Windows Event log for operational monitoring tools.

-- Brian Knight

Professional SQL Server 2005 Integration Services Book Available


Professional SQL Server 2005 Integration ServicesIn mid-January, my new book comes out called Professional SQL Server 2005 Integration Services from Wrox. You can pre-order it today on or go to Amazon to get the better price :). This book will help you get past the initial learning curve quickly so that you can get started using SSIS to transform data, create a workflow, or maintain your SQL Server. Offering you hands-on guidance, you'll learn a new world of integration possibilities and be able to move away from scripting complex logic to programming tasks using a full-featured language.

What you will learn from this book

  • Ways to quickly move and transform data
  • How to configure every aspect of SSIS
  • How to interface SSIS with web services and XML
  • Techniques to scale the SSIS and make it more reliable
  • How to migrate DTS packages to SSIS
  • How to create your own custom tasks and user interfaces
  • How to create an application that interfaces with SSIS to manage the environment
  • A detailed usable case study for a complete ETL solution

-- Brian Knight 

Upgrading DTS to SSIS

Next week I have a new article on about upgrading your DTS packages to SSIS. If you're reading this, you can be the first to see it by clicking here.

-- Brian Knight

Quickly Retrieving the Row Count for All Tables

My typical query I run for retrieiving a list of tables and how many records in each table there are is to use the sp_msforeach stored procedure. The sproc loops through each table in a database and performs an action. Given that, you can run a query like this to obtain a list of tables and how many records are in each table:

sp_msforeachtable "Print '?' select count(*) from ?"

The problem for me with this above query is that it's not sortable without some additional customization. So, I needed a different answer. The sysindexes table holds some very valuable information for a developer or DBA. The table shows you a record for each index in your database as well as the number of rows that are estimated to be in the table. It's not guaranteed to be up to date as far as the number of rows but it's a good number to start with. To model a query at that table, you can use the following syntax:

select convert(varchar(30),object_name(id)) [Table Name], rows from sysindexes
where object_name(id) not like 'sys%' and indid = 1
order by object_name(id)

Instantly you retreive in a single recordset the number of estimated records in each table. The key thing to keep in mind with the above query is I'm weeding out any table that began with sys. If you have a user table that begins with sys, it will be excluded. The indid=1 part of the syntax retrieves only the primary index. There are flaws to this method because it's not exact but it does give you a rought idea of your system information.

-- Brian Knight

Dynamically Changing the Colors of a Value in Reporting Services

In Reporting Services you can dynamically alter the color of a font by clicking the drop-down box for a font color and selecting Expression. This works well, if you would like to show a red color if your not crossing certain price points and green if your numbers look good. The syntax to perform this type of action would look like this:

=IIF( Fields!ytd_profit.Value < 1,"red","green")

Essentially the above is a clasic IF THEN....ELSE statement. If the profit is less than $0, then turn the color red, otherwise keep it green. The problem comes if you want a third or fourth condition. In this type of situation, you'll need to use the SWITCH statement. This statement in Reporting Services performs the same type of activity as a CASE statement in VB. Here's an example of how you could use the same logic as before but show especially good profit as blue.

=Switch( Fields!profit.Value < 1,"red",
Fields!profit.Value > 0,
Fields!profit.Value > 100, "blue")

-- Brian Knight

SSIS Packages are Encrypted by Default

By default, SSIS files in development are encypted to prevent an unauthorized person from seeing your SSIS package. The type of encyrption is seamless behind the scene and is at a workstation and user level. So, if you were to send a package that you're developing to another developer on your team, he would not be able to open it by default. The same would apply if you logged in with a different user. You would receive the below error:

There were errors while the package was being loaded.
The package might be corrupted.
See the Error List for details.

The error is very misleading. In truth, you can't open the package because the originating user encrypted the package whether on purpose or not. To fix this, the owner of the package can open the package and in the Properties pane, select a different option (like a package password) for the ProtectionLevel option. The default option is EncryptSensitiveWithUserKey. To protect the entire package with a password, select the EncryptAllWithPassword option.

An option that I like is that a SSIS designer encrypts all packages with the default option and when he's ready to send to production, he can develop a batch file to loop through a directory's .dtsx file and set a password. The batch file would use Dtutil.exe and look like this: 

for %%f in (*.dtsx) do Dtutil.exe /file %%f /encrypt file;%%f;3;newpassword

This would loop through each .dtsx file in yoru directory and assign the password of newpassword. The production support group could then use the same batch file to reset the password to a production password.

-- Brian Knight

SQL Server 2005 Business Intelligence Metadata Samples Toolkit Released

For the past few months, I've been working on a whitepaper for Microsoft on metadata. The whitepaper evolved as did the product. Well, finally both the product and the whitepaper have been released! So what does it do? Well it's a starter kit that provides you all the code for both viewing and analzying your enviornment's metadata. For example, if you have dozens of SSIS packages in your environment that all load a similar table, you can use this tool to see what the impact would be if you altered the table or the package as well as see the lineage of how the data got into the table. The kit includes:

  • DependencyAnalyzer: a utility that scans SSIS packages and SSAS databases for enumerating metadata (objects, properties and dependencies).
  • DependencyViewer: a utility to view metadata organization & inter-dependencies (lineage/impact analysis).
  • Analysis Services Samples : sample SSAS packages that can be analyzed
  • Integration Services Samples: sample SSIS packages that can be analyzed
  • Report Model Samples (Report Builder): report builder model
  • Reports: sample reports built from metadata store
  • SQL: SQL script for creating metadata store

Download the Toolkit:

I did a quick video also on how to use it that can be downloaded in MPG format here. Downloader beware though! The video is not optimally compressed and runs 80 MB. But, if you have a high-speed internet connection here's the link:

-- Brian Knight

DTS Designer Doesn't Exist in SQL Server 2005 by Default

Once you upgrade your tools to SQL Server 2005, you can't manage by default SQL Server 2000 packages. The answer is to download a piece of the SQL Server 2005 Feature Package (, which includes the DTS Designer. After this installation, you can update packages just like you were in Enterprise Manager. They didn't include this by default to reduce the surface area of the installation.

After you upgrade your SQL Server instance you'll be able to find your 2000 packages in the Microsoft SQL Server Management Studio under the Management node then Legacy -> Data Transformation Services. In an upgrade of an instance, packages and jobs don't move so you can slowly upgrade your DTS packages using the Upgrade Wizard.

-- Brian Knight

Speeding up the VS/SQL User Interface Load Time

One of the frustrating things about the new Visual Studio interface is the load speed. This applies to SQL Server 2005 as well. I have a very new computer which is quite speedy and the splash screen still takes 5-10 seconds to load. On my old machine, this is more like 10-15 seconds while the splash screen is loaded before SQL Server Management Studio would show the login dialog box. A remedy I stumbled across was to add the -nosplash switch at the end of the executables call in the shortcut. After killing the splash screen from displaying, my load time on my good machine was down to 1-2 seconds before being prompted for a login screen. I come in an out of tools so much, that this is quite useful.

To do this, right-click on the shortcut in the start menu for your tool like Business Intelligence Development Studio or SQL Management Studio and click Properties. In the target text box change the shortcut to point to the following for SQL Management Studio:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe" -nosplash

For Business Intellgience Managment Studio, the same logic applies:

"C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\devenv.exe" -nosplash

Also, this works for the Visual Studio environment in general. I personally hop in and out of these tools all day long and this solved a lot of frustration.

--Brian Knight

New Books Online Available

Keeping to their promise, Microsoft plans to release a version of Books Online every quarter for SQL Server 2005. A new version of the SQL server 2005 Books Online is now available only a month after the RTM release at:

The download has new tutorials, instructions and descriptions of common tasks. It's worth the download for sure but downloader beware: the download is 113 MB!

--Brian Knight

Three methods for dynamic queries

There are three main ways to do dynamic queries inside of SQL Server. My personal favorite method is option one below since it's a single line of code. Option three helps with caching of parameters though. Needless to say, dynamic queries should be avoided whenever possible :).

DECLARE @ColumnName sysname
@TableName sysname
@strSQL varchar(255)
DECLARE @unicodestrSQL nvarchar(255)

SET @TableName = 'Employees'
SET @ColumnName = 'LastName'

--Method one

EXEC ('select max(datalength('+@ColumnName+')) from ' + @TableName);

--Method two

SET @strSQL = 'select max(datalength('+@ColumnName+')) from ' + @TableName
EXEC (@strSQL)

--Method three

SET @unicodestrSQL = N'select max(datalength('+@ColumnName+')) from ' + @TableName
EXEC sp_executesql @unicodestrSQL;

-- Brian Knight


Preventing Schema Changes with DDL Triggers

DDL triggers are a fantastic way to prvent DDL events in a production database. In the following database triggers, you prevent any type of DDL event, like altering a sproc in production. If a statement is issued, the statement is logged, rolled back and a user receives a message. Databae triggers can be raised at nearly any level. The below example captures any database-level event. There are also server DDL triggers that captures server events like creating logins or changing the configuration of a database.




PRINT 'DDL events are prohibited, your statement was rolled back.'
DECLARE @schema sysname
DECLARE @object sysname
DECLARE @eventType sysname;

SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname'
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'
SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')

INSERT [dbo].[DatabaseLog]







(GETDATE(), CONVERT(sysname, CURRENT_USER), @eventType,

CONVERT(sysname, @schema), CONVERT(sysname, @object),

.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),



-- Brian Knight