The Excel Connection Manager is not supported in the 64-bit version of SSIS

When attempting to execute a package that makes use of an Excel Connection Manager you may be presented with the following errors.

• SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "YourConnectionMgrName" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
• [Connection manager "YourConnectionMgrName" Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

The problem is that Microsoft doesn't provide a 64-bit Excel Connect Manager component (as stated in the error). This means that to run the package it must be run using the 32-bit DTEXEC.exe. When running in BIDS (Visual Studio) it's as simple as setting a project property. Below is the default property on a 64-bit machine. Change True to False to run the package.

If you're running the package under a SQL Server Agent job you must also set a similar property on the job step that runs the package. Notice the Use 32 bit runtime property. Make sure this is checked.

Overwrite Excel File in SSIS: Workaround for Excel Connection Manager

I recently needed to create a process that would ultimately create an Excel file for import into another system. The other system was built and maintained by a third party and I had no control over it. It required that an Excel file be present in a specific directory with a specified name. The solution required that 1) if the file did not already exist it would be created and 2) if the file did already exist that it would be overwritten and a copy of the new file would be written to an archive folder with the date and time appended to the file name (so that the other historical files wouldn't be overwritten and so that it would be easy to know what date and time the file was created).

Current File Name: Extract.xls

Historical File Name: Extract_YYYYMMDD_MMSS.xls

I figured this would be easy. The first thing I did was look for the OverwriteFile or ReplaceFileproperty. What I found was that the only non-connection specific property the Excel Connection Manager exposed was FirstRowHasColumnName. At that point I figured I would experiment and find out what would happen if I tried to write to a pre-existing file. The result was that the data being written to the file was simply appended to the pre-existing data in the file. Definitely not the behavior I was looking for.

I then hit "the Google" as George W. so fondly calls it. I found solutions that proposed deleting data from worksheet(s), deleting worksheets, and creating new worksheets using the Jet 4.0 SQL syntax to "DROP" and "CREATE" tables. In Jet/Excel terms, a table is an Excel worksheet. I found that the DROP didn't actually delete the worksheet but deleted rows from the worksheet. This could have worked except that the next time the worksheet is written to, the new rows start after the last row of the previously deleted data, leaving a bunch of empty rows in the beginning of the worksheet. This approach did not cut the mustard. So ended my search for the wheel I was attempting to not reinvent.

The approach I came up with does the following.

1. Creates a shell (only the headers) Excel file with the name Extract_wip.xls.
2. Uses a Data Flow to populate the wip Excel file.
3. Copies the wip file to an Archive folder but renames to Extract_YYYYMMDD_MMSS.xls.
4. Renames the wip file to Extract.xls, overwriting the previously created Extract.xls files (if one exists).

This is a screen shot of the Control Flow.

*If you don't have an archival/historical requirement you can remove that functionality and make this solution even simpler.

Connection Managers

ExcelFile

The Connection Manager used in the final step of the process to rename the wip file to the final file name. Nothing fancy here although I would suggest making this configurable.

ExcelFileArchive

The Connection Manager used to copy the wip file to an archive folder with the date and time in the file name. Below is the expression used to generate the file name. You will want to set the Connection Manager's ConnectionString expression to this.

"C:\\ExcelExtract\\Archive\\Extract_" + RIGHT( "0000" + (DT_STR, 4 , 1252) DATEPART( "year" , GETDATE() ) , 4 ) + RIGHT( "00" + (DT_STR, 2 , 1252) DATEPART( "month" , GETDATE() ) , 2 ) + RIGHT( "00" + (DT_STR, 2 , 1252) DATEPART( "day" , GETDATE() ) , 2 ) + "_" + RIGHT( "00" + (DT_STR, 2 , 1252) DATEPART( "hour" , GETDATE() ) , 2 ) + RIGHT( "00" + (DT_STR, 2 , 1252) DATEPART( "minute" , GETDATE() ) , 2 ) + ".xls"

ExcelFileForDataFlow

This Connection Manager should point to the wip file. You'll want to have a ConnectionString expression similar to the following.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ExcelExtract\Extract_wip.xls;Extended Properties=\"EXCEL 8.0;HDR=YES\";"

*HDR is whether the file has a header row.

ExcelFileWIP

This Connection Manager points to the wip file and is used to copy and rename the populated Excel file.

SomeOLEDBDataSource

This is used by the data flow. There could be one or more Connection Managers for your data flow.

Control Flow Tasks

Connection Type: EXCEL

Connection: ExcelFileForDataFlow

SQL Statement:

CREATE TABLE [ThisWillBeYourWorksheetName](

[SomeCharVal1] nvarchar(25),

[SomeCharVal2] nvarchar(255),

[SomeCharVal3] nvarchar(50),

[SomeDecimalVal1] double precision,

[SomeDecimalVal2] double precision,

[SomeIntegerVal1] integer)

*It is important that you use the JET syntax including the appropriate data types when writing your CREATE TABLE script.

This task will create a new Excel file with a worksheet named using the name in your CREATE TABLE script. It will use the file name found in the ExcelFileForDataFlow Connection Manager.

*If a file by the same name exists it will add a worksheet to that file. This is why we want to make sure this file does not exist when we start our process. Hence the wip file.

This is the Data Flow that writes to your Excel File. It doesn't matter what the data flow does, just make sure that it writes to an Excel Destination that uses the ExcelFileForDataFlow Connection Manager.

Destination Connection: ExcelFileForArchive

OverwriteDestination: True

Operation: Copy File

Source Connection: ExcelFileWIP

This File System Task copies the wip file to an Archive (or any other) folder using the Extract_YYYYMMDD_MMSS.xls naming convention.

Destination Connection: ExcelFile

OverwriteDestination: True

Operation: Rename File

Source Connection: ExcelFileWIP

This File System Task renames the wip file to the Extract.xls name, effectively overwriting the previously created Extract.xls file (if one exists).

Summary

While it seems like it would make sense for the Excel Connection Manager to allow for the overwriting of an existing file, it doesn't. The above process is the simplest way I have found to reproduce this seemingly lacking functionality.

SQL Server Default Backup Location

SQL Server makes it easy to set a server's default data and log locations. In SQL Server Management Studio, simply right click on the top level server node in the Object Explorer and select Properties. Then click on the Database Settings page selector on the left side of the screen. At the bottom of this screen there are text boxes for entering the default Data and Log locations.

Unfortunately it's not as easy to set the default backup location. To do this you need to modify the server's registry. Open the Registry Editor by typing regedit either on a command line or in the Search programs or files box at the bottom of the Windows Start Menu. Once you're in the Registry Editor navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\YourInstanceName\MSSQLServer. Now enter the directory you'd like to be your default. Save and close the Registry Editor.

The next time you attempt to backup or restore a database you will be presented with your new default backup location. No more navigating to a different location over and over again.

The Future of Visual Studio Database Projects

It is with great sadness that I relay the decision by Microsoft to do away with the Visual Studio Database Project type that I love so much. This project type has given database developers the tools needed to implement high quality Database Development Life Cycle processes. Some highlights can be found here.

However, all is not lost. VSDB will be replaced by what was until yesterday referred to as project Juneau and is now officially named SQL Server Data Tools. It will also be hosted in the Visual Studio environment.

SSDT has many similarities with VSDB but is a complete rewrite from what I understand. Below are some of the features previously unavailable in VSDB.

1. Code navigation (Go to Definition, Find All References)
2. Enhanced code analysis and design time validation
3. Table designer similar to SSMS
4. Connected (live database) and disconnected (declarative) development
5. LocalDB - SQLExpress without the need to explicitly install any SQL Server instances
6. Entity Framework integration for a much more tightly integrated development experience between the data and application layers
7. Support for CLR objects
8. Support for SQL Server 2012 and SQL Azure databases (both support 2005, 2008, and 2008 R2)
9. Integration of SSIS, SSRS, and SSAS projects into the next edition of Visual Studio (currently Visual Studio 2011)

In my opinion, of the biggest shortcomings (as of SSDT CTP3) of SSDT is that there is no longer support for database unit tests. Automated unit tests have a huge impact of quality and I believe are key to robust DDLC.

While I am sad to see VSDB go, I am confident that Microsoft will create an even better tool in SSDT. I look forward to exploring it and sharing my findings in future posts.

SSIS Configuration File (.dtsconfig) Behavior (relative path issue)

Let me start by saying that I prefer SQL Server configurations over all other configuration types in SSIS for the majority of configurable values in my SSIS projects because they are more secure, easier to decipher, and fit well into database deployment methods and tools that I use.

Unfortunately, not all configurations can be of the SQL Server configuration type as we need an initial configuration that stores the connection string for the database that the SQL Server configurations live in.

This is where it becomes necessary to use an XML configuration file type of SSIS configuration. One of the problems with using an XML config file is that SSIS does not understand relative paths. This means that all environments must store the config file in the exact same directory structure, otherwise the file will not be found (except in some circumstances which I'll explain later in this post).

To get around this problem we can use something called an Indirect XML configuration file configuration type to store this single configuration containing the connection string for the SSIS configuration database. See the first configuration in the screen shot below.

The beauty of this configuration type is that it allows you to use an XML config file in such a way that its location is not environment specific. It does this by getting the location of the XML config file from a Windows Environment Variable. Unfortunately this must be created in environments where SSIS packages are being developed (where BIDS is installed) and in runtime environments where the config file does not live in the same directory as the SSIS packages being executed. While this does require added effort, it only has to be done once per environment. Below is a screen shot of the creation of a sample Environment Variable.

If you store your XML configuration file (.dtsconfig) in the same directory as the rest of your SSIS packages (.dtsx) then it is not necessary to create this environment variable on machines where your packages will be executed by the SSIS runtime and/or dtexec/dtexecui. SSIS will attempt to find the Environment Variable and when it cannot, it will then look in the root path of the SSIS packages and use the config file found there.

However, if your design requires that you store your XML config file in a location other than your package location you must create the Environment Variable in all locations.

At the risk of repeating myself, BIDS will not be able to find your config file unless you hard-code it's absolute path in a regular XML configuration file configuration (non-Indirect XML configuration) or create an Indirect XML configuration using an Environment Variable. This is true even if the config file lives in the same directory as your packages. This works fine when there is only one developer and that developer uses only machine to develop. Once you have multiple development team members with potentially different directory structures, you can avoid many headaches by making your XML config file location environment agnostic. Don't fear the Environment Variable!

OLE DB Destination or SQL Server Destination

SSIS offers a number of Data Flow destination components. When working with SQL Server databases as a destination there are two obvious choices.

OLE DB Destination

SQL Server Destination

Microsoft claims up to a 15% performance increase by using the SQL Server destination. Therefore it seems like a good choice. However, there is one rather large caveat. SSIS must be running on the same SQL Server as the destination database. It cannot write to a remote SQL Server.

That said, I have seen many solutions that have both SSIS and the destination database on the same server so it seems to make sense to use it in these situations. Personally, I choose not to. By using the SQL Server destination you're locking yourself into having to make a code change if you decide to split your SSIS and database instances across multiple servers. Having SSIS code dependent on a solution's hardware architecture is something I always try to avoid.

SQL Bulk Copy

In an earlier post I talked about a generic SSIS package for staging data using SQL Bulk Copy. I have been using it extensively without fail . . . until just recently.

I got a new set of files from a new business unit that we were bringing into our data warehouse solution. Of their 20 files 14 loaded without issue and the remaining 6 failed with an error similar to the one below.


System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException: The given value of type String from the data source cannot be converted to type int of the specified target column.


I kept going back to the developers that created the extract process and telling them that they were doing something wrong with their encoding or delimiters but neither us nor them could actually find the issue. Until now.

Turns out the issue is related to SQL Bulk Copy, numeric columns, and null values. Apparently SQL Bulk Copy does not, by default, keep nulls. Instead, it converts them to empty strings. Well, as I'm sure you know, SQL Server doesn't like it when you try to load empty strings, or any strings for that matter, into a numerically typed column.

There are a couple of solutions.

1. Make sure to replace all nulls with zeros when created flat files with numeric data that will be consumed by BCP.
2. Use the KeepNulls option when executing BCP. In my SSIS solution I changed my SqlBulkCopy object creation to look like this . . .

Dim bc As SqlBulkCopy = New SqlBulkCopy(dbConn, SqlBulkCopyOptions.KeepNulls, bulkCopyTransaction)