Archive for November 2007

The November CTP for SQL Server 2008 Is Out!

The download link is here:

Evidently there have been some issues getting it on the Connect site, so they are hosting it in Microsoft’s Download Center for now.

BIDSHelper 1.2 Is Out

BIDSHelper 1.2 is now available at CodePlex. The new version has a number of SSIS specific features, like expression and configuration highlighting, a tool window that lists all the expressions in the package, and a Package Smart Diff tool. Darren (one of the other team members) posted about it on his blog. Keep watching, as we are targeting shorter release cycles with more incremental functionality. If you have suggestions for new features, please post them here.

Changing Destination Tables With the OLE DB Destination

The OLE DB Destination supports using a variable to get the name of the target table. This allows you to switch the destination table dynamically, but there is a caveat. The columns in each table that you will use must match, or the OLE DB Destination will give a NEEDS_NEW_METADATA error.

What’s a potential way that you might use this capability? Well, one recent scenario posed on a forum was this: A set of rows should be loaded to Table A, unless Table A already had rows in it. If it did, the rows should be loaded to Table B. This is fairly straightforward to implement in SSIS, thanks to expressions and the OLE DB Destination working with a variable.

To set this up, I started with an Execute SQL Task. It was configure to retrieve the record count for Table A into a variable called User::RecordCount. A second variable, User::TableName, was created to hold the table name. The value for this variable is determined from the following expression:


This expression evaluates the RecordCount variable. If it is equal to 0, Table A is used, otherwise Table B is used.

The Execute SQL task is connected to a data flow, with an OLE DB Source and the OLE DB Destination. The OLE DB Destination is configured to use the variable for the table name:


That’s all there is to it. I’ve posted a sample package to my SkyDrive here:


A few notes about the sample: There are three tables involved, the source (Numbers) and two destinations (TableA and TableB). All three tables have a single Integer column (named ID in Numbers, and ColumnA in TableA and TableB). The scripts for these are not included in the sample.

Running Password Protected Packages

A recent post on the forums was asking about running packages programmatically. The poster’s package was password protected, and when running the package, they were receiving a message that one of the connection managers was failing to connect.

The code being used was similar to this:

        Dim app As Microsoft.SqlServer.Dts.Runtime.Application
Dim pkg As Microsoft.SqlServer.Dts.Runtime.Package

app = New Microsoft.SqlServer.Dts.Runtime.Application()
pkg = app.LoadPackage(“C:\Projects\TestPackage.dtsx”, Nothing)
pkg.PackagePassword = “password”

This code compiles fine, but does have a small error. The password is being set on the Package object, after it has been loaded. In this case, the Package’s ProtectionLevel property was set to EncryptSensitiveWithPassword, so the error didn’t show up until the package was executed. The problem is that the password needs to be set on the Application object, before the package is loaded. This makes sense, since encrypted values are decrypted when the package is loaded. The following code works fine:

        Dim app As Microsoft.SqlServer.Dts.Runtime.Application
Dim pkg As Microsoft.SqlServer.Dts.Runtime.Package

app = New Microsoft.SqlServer.Dts.Runtime.Application()
app.PackagePassword = “password”
pkg = app.LoadPackage(“C:\Projects\TestPackage.dtsx”, Nothing)

This particular problem is a lot easier to see if the Package ProtectionLevel property is EncryptAllWithPassword, because you’ll get an error as soon as you load the package.

I’m not positive of this (the documentation is a little lacking), but I’m guessing you’d set the Password property on the Package object to encrypt a package that you had created programmatically. Evidently, setting it will not decrypt any existing encrypted values.

SSIS Performance Webcast for the Charlotte SQL Server User Group

I’ll be doing a web cast for the Charlotte SQL Server User Group, our local PASS chapter, on SSIS Performance Tuning on Nov. 28, 2007 at 12:00 EST. From the abstract:

“This presentation will cover the various aspects of performance tuning in SQL Server Integration Services. We will start by covering some general best practices for performance. We will then drill into details of performance optimization for SSIS data flows, including optimizing execution trees, managing buffer size, and evaluating performance metrics to determine where the bottlenecks are. We’ll also cover the logging options and performance counters that can be used to monitor performance.”

If you are interested in tuning in, please sign up here. We’ll be giving away a few copies of Windows Server 2003 Enterprise Edition. To sign up as a member of the user group, go here and select the sign-up option.

Dealing with Unicode and ASCII Text Files

A recent post on the MSDN forums brought up an interesting problem. The poster was attempting to process a folder of files, all of which had the same format, but some were encoded as ASCII (ANSI, DT_STR, or varchar, if you like the SQL Server data types) and some were encoded as Unicode (DT_WSTR, nvarchar).

The initial issue was determining which way the file was encoded. After a little research, I located a post entitled “Detecting File Encodings in .NET” by Heath Stewart. This showed how to check the encoding of a file in C#. I converted the code to VB.NET and set it up to work within a Script Task. These are the results:

    Public Sub Main()
”’Code based on sample originally posted here:
Dim file As System.IO.FileStream = Nothing
Dim isUnicode As Boolean = False

file = New System.IO.FileStream(Dts.Connections(“TestFileAnsi”).AcquireConnection(Nothing).ToString(), _
System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.Read)
If file.CanSeek Then
Dim bom(4) As Byte ‘ = Byte ‘// Get the byte-order mark, if there is one
file.Read(bom, 0, 4)
If ((bom(0) = &HEF And bom(1) = &HBB And bom(2) = &HBF) Or _
(bom(0) = &HFF And bom(1) = &HFE) Or _
(bom(0) = &HFE And bom(1) = &HFF) Or _
(bom(0) = 0 And bom(1) = 0 And bom(2) = &HFE And bom(3) = &HFF)) Then
isUnicode = True
isUnicode = False
End If
‘// Now reposition the file cursor back to the start of the file
file.Seek(0, System.IO.SeekOrigin.Begin)
‘// The file cannot be randomly accessed, so you need to decide what to set the default to
‘// based on the data provided. If you’re expecting data from a lot of older applications,
‘// default your encoding to Encoding.ASCII. If you’re expecting data from a lot of newer
‘// applications, default your encoding to Encoding.Unicode. Also, since binary files are
‘// single byte-based, so you will want to use Encoding.ASCII, even though you’ll probably
‘// never need to use the encoding then since the Encoding classes are really meant to get
‘// strings from the byte array that is the file.
isUnicode = False
End If

Dts.TaskResult = Dts.Results.Success

Catch e As Exception
Dts.TaskResult = Dts.Results.Failure
If Not file Is Nothing Then
End If

Dim vars As Variables
Dts.VariableDispenser.LockOneForWrite(“isUnicode”, vars)
vars(“isUnicode”).Value = isUnicode
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
End Try
End Sub

Essentially, the script above is getting the file path from a flat file connection manager.  It’s then checking the byte order mark (BOM), which is what tells us if the file is Unicode or not. The script assumes that the file is ASCII, if it can’t determine it from the BOM. The result of the check is written to a boolean variable defined in the package called isUnicode.

That part was relatively straightforward. Originally, I thought I could just use this variable to set the Unicode property on the flat file connection manager through an expression, and use the same dataflow, as long as I typed all the string columns as DT_WSTR (I really should have known better). Oddly enough, when I first tested this, I found that if I set the Unicode property to false on the connection manager, but set the column types to DT_WSTR, I could process ASCII files without a problem, but Unicode files didn’t send any rows through, even though there was no error. If I set the Unicode property to true, and then set it to false via an expression, the flat file source threw a validation error because the error output was using a DT_NTEXT type, and it needs to use DT_TEXT with ASCII files.

What I ended up with instead isn’t quite as simple, but it does work consistently. I created two connection managers, one configured for ASCII, and the other for Unicode. Both connection managers have an expression defined that sets their ConnectionString property based on the same variable, so they both point at the same file. Then I created two data flows, one for ASCII, one for Unicode. This eliminates any metadata errors. Finally, I set precedence constraints from the script task to each data flow, and made execution conditional based on the isUnicode variable.


The constraint to DF ANSI looks like this:image

and the constraint to DF Unicode looks like:image

The sample files for this (included a text file formatted as Unicode and ASCII) are on my Live Drive:


To switch the test file from ASCII to Unicode, change the file path in the “testFilePath” variable.

Checking for the Existence of a File

The File System Task in SSIS doesn’t support checking to see if a file exists. You can work around this easily with a script task. Create a new script task and add a Imports statement referencing the System.IO namespace to the top of the script.

Imports System.IO

Then add the following to the Main method:

If File.Exists(Dts.Connections(“ConnMgrA”).AcquireConnection(Nothing).ToString()) Then
Dts.TaskResult = Dts.Results.Success
Dts.TaskResult = Dts.Results.Failure
End If

This script checks the file referenced by the ConnMgrA connection manager. If it exists, the script task returns Success, meaning execution will follow the Success constraint from the Script Task. If the file does not exist, the task will fail, and the Error constraint will be used. You could also set a variable with the results, and use that in an expression on a precedence constraint.

If, instead of using a connection manager, you want to get the file name from a variable, you can replace the If statement with the following:

If File.Exists(ReadVariable(“FileNameVariable”).ToString()) Then

The variable locking is occurring in the ReadVariable method. To see the definition for it, please refer to Daniel Read’s blog post here. This is a good practice to follow when working with variables in Script Tasks.

There is a Connect posting here requesting that the File System Task be enhanced to support checking for a file’s existence. If you’d like to see this in a future version, please vote for issue.