SSIS imports NULLs instead of numeric values from an Excel file

Let's imagine that you have been assigned a task to import some kind of parts catalogue from an Excel file to SQL Server table. You decided to use Integration Services. One of the columns in the Excel file contains catalogue numbers. Most of them look like this XYZ123456 but some of them are plain numeric values.

This is what can happen:

Excel          SQL Server
CAT_NO         CAT_NO
---------      ---------
XYZ121156      XYZ121156
XYZ654321      XYZ654321
ABC120456  =>  ABC120456
CBA183456      CBA183456
ZYX123416      ZYX123416
123            NULL       (!)
ZXV654521      ZXV654521

This article from SQL Server 2005 Books Online explains this weird behaviour:

Missing values
The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data type of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values for cells that contain data of the other type. (In a tie, the numeric type wins.) Most cell formatting options in the Excel worksheet do not seem to affect this data type determination. You can modify this behavior of the Excel driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window.

Importing data from DBF files using SSIS - configuring Connection Manager

This is an example Connection Manager configuration:

The most important thing to remember is that the Data Source property should only contain a path to the folder with DBF files (e.g. c:\temp\3\), not a full path to the file (e.g. c:\temp\3\sales.dbf).
The particular file that should be used is selected later in the Name of the table or the view combo box in OLE DB Source configuration.

Importing data from DBF files using SSIS on 64-bit SQL Server 2005 - errors during package execution

If you are trying to import data from DBF files using Integration Services (SSIS) and you are getting errors similar to:

Error: The AcquireConnection method call to the connection manager "DBF files" failed with error code 0xC0202009.

Error: component "DBF file source" (1) failed validation and returned error code 0xC020801C.

Error: An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

check whether you are not running 64-bit version of SQL Server 2005. If so, go to project properties and set the Run64BitRuntime property to false.

I found it here.

UPDATE:

If you are then using dtexec.exe utility to run such a package and encounter similar errors, read this thread and use 32-bit version of dtexec.exe.

Calling an SSIS package from a SQL Server Agent job step

If possible, always use Operating system (CmdExec) SQL Server Agent job step and DTExec.exe utility to call an SSIS package from a SQL Server Agent job step. The gives you much more verbose output information than SSIS Package step and makes troubleshooting much easier.

Just compare this:

Executed as user: DOMAIN1\sqlagent2005.
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 10:12:30 AM
Error: 2006-10-27 10:12:30.83
Code: 0xC0014060
Source: {9D739ECB-C57C-4A4D-9DFE-80183F7B7002}
Description: Failed to decrypt a package that is encrypted
with a user key. You may not be the user who encrypted this
package, or you are not using the same machine that was
used to save the package.
End Error
Could not load package "C:\Program Files\Microsoft SQL Server
\90\DTS\Packages\Package1\Package1.dtsx" because of error
0x8009000B.
Description: Failed to remove package protection with error
0x8009000B "Key not valid for use in specified state.". This
occurs in the CPackage::LoadFromXML method.
Source: {9D739ECB-C57C-4A4D-9DFE-80183F7B7002}
Started: 10:12:30 AM
Finished: 10:12:30 AM
Elapsed: 0.438 seconds.
Process Exit Code 5.
The step failed.

and this:

Executed as user: DOMAIN1\Administrator.
The package could not be loaded.
The step failed.

UPDATE:

Also, check the comment below for yet another reason that proves the point I am making here.

SSIS Back Up Database task - yet another annoying SQL Server 2005 'feature'

If you are using the Back Up Database tasks in your SSIS packages, you should be aware that every time you click on the Databases drop-down-list-like thing and then you click OK to close it, the folder location in the Folder textbox is automatically set to the default backup location. So if you are using another location to store your backups, you have enter it every time you change or even view the list of backed up databases.

SSIS package configuration files

SSIS package may ignore a configuration file (the dtsConfig file) and use variable values stored in the package (the dtsx file), if the configuration file contains one or more special Polish characters (even if there are used only in comments - <!-- the comments go here -->).

I have no idea when or why it happens, but I have a package that worked fine, then started to behave strangely and then it all came back to normal, when I removed special Polish characters from the comments in the configuration file. The bug is reproducible.

HOWTO: Format nicely an XML document (beautifying XML)

From time to time I have an XML file that needs to be formatted nicely. If the file is small, I usually do it manually.

However, lately I was working with SSIS. I created a package with an XML configuration file. And it came out that Visual Studio 'thinks' it is a great idea to put all content of the file in one line. Well, it is not, but I have already got used to such bugs in SQL Server 2005.

The configuration file was totally unreadable, but I remembered that I had already had such problem in the past. After some searching, I downloaded XMLStarlet (that I had also used last time).

All you have to do to format nicely an XML file is to type:

xml.exe format ugly_formatted.xml > nicely_formatted.xml

Of course, XMLStarlet can do much more.

SSIS package execution fails when you run it from a SQL Server Agent job

I created a SQL Server Integration Services (SSIS) package. It was pretty simple and it worked fine. After testing it, I created a SQL Server job with a single step that was supposed to execute the package.

And it took me two days to make it work. Unfortunately, documentation dealing with this topic is pretty poor.

I was getting different errors - for example:

Executed as user: SOME_DOMAIN\sqlagent2005. The package could not be found.
The step failed.

Unable to start execution of step 1 (reason: Could not get proxy data for
proxy_id = 2). The step failed.

This is a method I found (I am positive that there are other ways to get the same result, too).

1. Create a credential (make sure that the chosen account can execute jobs).

2. Create an SSIS proxy using this credential.

3. Set the proxy in the 'Run as' combo in a job step definition.

4. Grant access to C:\Program Files\Microsoft SQL Server\90\DTS\Packages (or wherever you deployed your SSIS package) to the domain account that you used to create the credential.

UPDATE:

Later I found a good article about this problem - An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step.