SQL Server Integration Services 2008 - Fix for when SSIS Script Tasks Freeze or Run indefinitely without Returning a Result

As you may have seen in my previous posts, I've been having some interesting adventures with SQL 2008 SSIS packages of late. However, the most obscure one hit me today which meant that all Script tasks would run endlessly without error if the code hit ANY exception at all. The problem would happen even when the exception was fully handled within a try...catch blog.

I tried several things like:

1. Remove all code except for a throw new Exception() - SSIS Script task STILL freezes
2. Change the project target back to .NET 2.0 from 3.5 (after removing all code) - SSIS Script task STILL freezes
3. Remove all variables from the Script Task properties - VOILA!
   
   I eventually worked out that a Package Level variable that i'd defined named "DBConnectionString" would stop exceptions from being bubbled up to the parent package and freeze the task when it was marked as either a Read-Write Variable and a Read-Only variable in the script task properties. I removed this variable definition from the script task properties and all exceptions started to bubble up correctly.
   
   I tried to reproduce the issue in a completely new package with no success (on the production 64 bit environment and my 32 bit dev machine). It doesn't appear to be related to the variable name or the value of that variable.
   
   This is just a warning to all those who encounter this unfortunate Script task freezing issue in SSIS. It may also have the appearance of being in an infinite loop as the whole package just sits there and will NEVER complete.

SQL Server Integration Services - How to check for the existence of an Excel Worksheet in SSIS

This code snippet shows you how to check for the existence of a worksheet in an Excel file. I had to do this because the data collection spreadsheet I was importing from would not add a $Warnings worksheet if there were no warnings for a particular set of data. The code below relies on the GetSchema() method of OLEDB connections to obtain schema information.

public class ScriptMain
{
    public void Main()
        {
            string fileToTest;
            string tableToTest;
            string connectionString;
            OleDbConnection excelConnection;
            DataTable excelTables;
            string currentTable;

            fileToTest = Dts.Variables["ExcelFile"].Value.ToString();
            tableToTest = Dts.Variables["ExcelTable"].Value.ToString();

            Dts.Variables["ExcelTableExists"].Value = false;
            if (File.Exists(fileToTest))
            {
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + fileToTest + ";Extended Properties=Excel 8.0";
                excelConnection = new OleDbConnection(connectionString);
                excelConnection.Open();
                excelTables = excelConnection.GetSchema("Tables");
                foreach (DataRow excelTable in excelTables.Rows)
                {
                    currentTable = excelTable["TABLE_NAME"].ToString();
                    if (currentTable == tableToTest)
                    {
                        Dts.Variables["ExcelTableExists"].Value = true;
                    }
                }
            }

            Dts.TaskResult = (int)ScriptResults.Success;

        }
}

For more information, see http://technet.microsoft.com/en-us/library/ms403358.aspx#example1

Fix SSIS 2008 Error - The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data."

When importing a datasource from a flat file today (a csv), (which ran through a Derived Column Transformation and then a Data Conversion Transformation before hitting SQL), I hit the following error in SQL Server Integration Services 2008 (SSIS 2008):

"Data conversion failed while converting column .....The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data."

The dataviewers and error output SSIS debugging techniques didn't show anything unusual in the data as it was transformed. In addition, this problem only occurred on my float columns, and only when the data in the columns is blank. I did some quick checks to see if there were a few extra spaces in some of the fields and did a TRIM() for the derived column - but the same error kept coming up. I even enabled "Ignore truncation" and it still didn't work.

Information: 0x402090DE at ImportTemplateData, Flat File Source [2917]: The total number of data rows processed for file "C:\WorkforceProfileInitialImport\Q2_2008_162_Data.csv" is 1050.
Error: 0xC02020C5 at ImportTemplateData, Data Conversion [4034]: Data conversion failed while converting column "[8c Override Census Period FTE]" (2050) to column "Copy of [8c Override Census Period FTE]" (5338). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at ImportTemplateData, Data Conversion [4034]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "Copy of [8c Override Census Period FTE]" (5338)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "Copy of [8c Override Census Period FTE]" (5338)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Error: 0xC0047022 at ImportTemplateData, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion" (4034) failed with error code 0xC0209029 while processing input "Data Conversion Input" (4035). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Information: 0x40043008 at ImportTemplateData, SSIS.Pipeline: Post Execute phase is beginning.

The underlying issue was that it was trying to convert these blank strings (from the flat file source datatype) into the float datatype and was thereforce constantly failing. The fix is to tick on the "Retain null values from the source as null values in the data flow" and the package then started to run successfully for those columns which had just blank values.

Resetting an Identity Field to 0 when testing a data migration process

I currently have a SSIS package that imports data from Excel by flushing data out of the existing SQL tables and grabbing the new values from the source spreadsheet. I noticed that while testing these scripts, my identity seed value was ominously creeping up. When I moved this to live after testing the migration, this would mean the identity values would be artificially large.

Never fear - It IS possible to reset the identity seed value by using the "DBCC CHECKIDENT" command. For example, to reset a table so that the next identity values is 1, you can run the following as part of your data migration script:

DBCC CHECKIDENT('TableName', RESEED, 0)

This will force the next value generated to be 1. Also note that the DBCC CHECKIDENT command even works with fully qualified names for the tablename parameter. For example:

ALTER PROCEDURE [dbo].[proc_InitializePrimaryTables]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    TRUNCATE TABLE InvestmentManagementDataMigration..OpportunityStatusHistory
    DBCC CHECKIDENT('InvestmentManagementDataMigration..OpportunityStatusHistory', RESEED, 0)
    DELETE FROM InvestmentManagementDataMigration..OpportunityUserRole
    DBCC CHECKIDENT('InvestmentManagementDataMigration..OpportunityUserRole', RESEED, 0)
    DELETE FROM InvestmentManagementDataMigration..FundInterest
    DBCC CHECKIDENT('InvestmentManagementDataMigration..FundInterest', RESEED, 0)
    DELETE FROM InvestmentManagementDataMigration..OpportunitySourceContact
    DBCC CHECKIDENT('InvestmentManagementDataMigration..OpportunitySourceContact', RESEED, 0)
    DELETE FROM InvestmentManagementDataMigration..AssetOwnership
    DBCC CHECKIDENT('InvestmentManagementDataMigration..AssetOwnership', RESEED, 0)
    DELETE FROM InvestmentManagementDataMigration..AssetRelationship
    DBCC CHECKIDENT('InvestmentManagementDataMigration..AssetRelationship', RESEED, 0)
    DELETE FROM InvestmentManagementDataMigration..Opportunity
    DBCC CHECKIDENT('InvestmentManagementDataMigration..Opportunity', RESEED, 0)
    DELETE FROM InvestmentManagementDataMigration..Contact
    DBCC CHECKIDENT('InvestmentManagementDataMigration..Contact', RESEED, 0)
    DELETE FROM InvestmentManagementDataMigration..Company
    DBCC CHECKIDENT('InvestmentManagementDataMigration..Company', RESEED, 0)
    DELETE FROM InvestmentManagementDataMigration..Address
    DBCC CHECKIDENT('InvestmentManagementDataMigration..Address', RESEED, 0)
  
END

Trivia:
Just to be tricky, I tried setting the seed to a negative value (-1) just to see what this would do...and the next value generated was 10. I don't know about you, but last time I checked, 10 does not follow -1!

SSIS Excel Data Source Error - “Text was truncated or one or more characters had no match in the target code page.”

I also hit this error at my previous company http://www.ssw.com.au/ when I was doing migration from Excel spreadsheets via SQL Server Integration Services (SSIS) for my client Queensland Water Infrastructure.

As described by my http://www.oakton.com.au/ colleague Craig at http://waxtadpole.wordpress.com/2008/04/28/hello-world/, this issue also came up at our current client "LL". The issue is that the sample taken by the JET driver to determine the input datatype on the Excel spreadsheet is just not large enough. e.g. if you have 100 characters for "Column A" on every row except for line 2000, then it will not be picked up and the data type will be set to Unicode string [DT_WSTR] 255 - it will then fail with a truncation error. Even if you override it in the Advanced tab, SSIS will keep setting the size back to the value of 255. The fix is to either:

1. Put a large placeholder on the first line of the spreadsheet to force the detection of a larger size field (so it gets promoted to a "Unicode text stream [DT_NTEXT]")



2. Increase the sample size so that it picks up the correct field size (Recommended). You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry key. By default, this is the ludicrously small value of 8(!) lines. For a more reasonable 100 lines, you can copy the following into a .reg file and double click to update your registry:

Windows Registry Editor Version 5.00[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]"TypeGuessRows"=dword:00000064

After you do this, it will suddenly realize that the column settings are too small and update the data type to "Unicode text stream [DT_NTEXT]".

Also note that you should close your spreadsheet before opening your SSIS package - otherwise it will wait indefinitely with "Visual Studio is Busy" messages!