SSIS – Unicode data is odd byte size for column 4. Should be even byte size.

Standard

This one was interesting and if it wasn’t for a quite obscure article on page 3 of a Google search (who even goes to page 3 anymore) it would have taken me a lot longer to figure out the problem.

Basically we’re hitting a Kafka queue in SSIS to get and process messages as they appear. There’s some info here on how to connect SSIS to Kafka.

The messages that are coming down from the queue are in UTF8 format and I wanted to store them in an NVARCHAR(MAX) column, in case we ever need to process multilingual messages.

As we get the messages down I’m storing them as a string in UTF8, which as per the below isn’t unicode:

string text = Encoding.UTF8.GetString(msg.Payload, 0, msg.Payload.Length);

And then when we push them to the output buffer we’re using UTF8, still not unicode:

JSonOutputBuffer.AddRow();

JSonOutputBuffer.JSonRaw.AddBlobData(System.Text.Encoding.UTF8.GetBytes(text));

The problem came when I tried to push the field into an NVARCHAR(MAX) column in the database, now this IS unicode So we end up getting an error when attempting to push the value from the buffer into the table:

“Unicode data is odd byte size for column 4. Should be even byte size.”

It was an easy fix, just use Unicode as the encoding when you’re pushing the value to the otuput buffer.

JSonOutputBuffer.AddRow();

JSonOutputBuffer.JSonRaw.AddBlobData(System.Text.Encoding.Unicode.GetBytes(text));

This is caused by SQL Server expecting an even number of bytes in an NVARCHAR column as per the below:

“Notice that compressed Unicode strings are always an odd number of bytes. This is how SQL Server determines that the string has actually been compressed, because an uncompressed Unicode string—which needs 2 bytes for each character—will always be an even number of bytes” Source (You’ll need to search for ‘odd’ to find this section..

If nothing else this was an interesting error that I couldn’t find any more info on! So hopefully this helps someone..

 

Advertisements

SSIS – RecordSet Object set to NULL after ForLoop

Standard

This was a weird and frustrating one. I’ve got a package that has a ForLoop in it and inside the ForLoop I’m loading records that need to use a Lookup. I thought what better time to use a CascheTransform than now! The only issue was that I needed to use the Lookup from inside a Script Component in my Data Flow (not a Script Task).

Now, knowing little about c# I figured I could simply load a new ADO RecordSet into a package variable and use that in my Script Component. so I created a OleDb source and a RecordSet destination:

lookup

Then populated a variable (User::Result) of type Object with my RecordSet for use later in my Script Component

lookup1

Now later in my Script Component I passed the variable in as a ReadOnly variable and populated a new DataTable using the source variable. And it worked for the first run of the ForLoop and then the underlying variable (User::Result) got set to Null. Turns out the issue was caused by using an OleDb adapter to fill a data table:

DataTable dt = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.Fill(dt, Variables.Result);
Credit: Brad2575

The above section of code causes the object to be emptied.

The Answer

The solution to this is to populate the DataTable first, not a RecordSet and pass the DataTable into the object. This avoids having to create the DataTable from the RecordSet each time.

lookup2

You can use code similar to the below to populate the DataTable: 

public class ScriptMain : UserComponent
{
    //Create a new DataTable. This will end up getting passed around your package
    DataTable tbl = new DataTable();


public override void PreExecute()
    {
        base.PreExecute();

        //Set up the columns and PrimaryKey
        tbl.Columns.Add("Column1", typeof(int));
        tbl.Columns.Add("Column2", typeof(int));

        tbl.PrimaryKey = new DataColumn[] { tbl.Columns["Column1"] };

    }
        public override void PostExecute()
    {
        base.PostExecute();

        //Populate the PackageVariable with the newly create DataTable object.
        Variables.LookupTable = tbl;
    }

    public override void LookupInput_ProcessInputRow(LookupInputBuffer Row)
    {
        //Now populate the table with rows from your query
        tbl.Rows.Add(Row.Column1, Row.Column1);
    }

}

 

There are a couple of blog posts here and here that helped me to work out the solution for this.

I hope this can help you too 🙂

SSIS Cache Transform – Failed to prepare the cache for new data

Standard

Encountered this error today when using a cache transform to populate a lookup cache with some data that I’d look up against multiple times:

ct1

I hadn’t set up the cache transform to use a .caw file (more info here), so there were no permissions issues with writing to disk, all that should be happening is writing to memory. Here’s what my SSIS package looked like when the error was received:

ct2

In the above we get the number of days worth of data to load and then load each day separately into the destination table (we’re loading each day as we hit some external limits if we don’t). The issue here was you can’t overwrite or append data to the cache. So all that was required was to move the ‘Load the lookup cache’ step outside of the for loop.

ct3

After the cache was moved out of the loop, it loaded successfully.

 

Login failed for user ‘NT AUTHORITY\ANONYMOUS USER’

Standard

I needed to manually run a project deployment model SSIS package (on box A) that talks to a SQL Server instance on another machine (box B), the package runs a stored procedure and returns some results. Box A and Box B are not linked servers, however the SQL Agent service account and the SQL Server service account from Box A both have permissions to execute the procedure on box B.

When I run the package manually through the SSIS project

Anonymous_User

it fails with the below error:

Anonymous_User2

What’s going on? I’ve got permission to run the stored procedure on Box B as well, but my user seems to be getting replaced with NT AUTHORITY\ANONYMOUS LOGIN. The issue here is that as of SQL Server needs credential delegation to be turned on. You can do this either through a linked server, or as I did it, by creating a SQL Agent job to handle the delegation for me.

There’s more learning to do here as we may have impersonation handling the delegation for me, but for the purposes of recording how an error message relates to a problem, I think this suffices for the moment and will allow further investigation.

 

SSIS -Registry setting specifying configuration file does not exist

Standard

I was having an issue getting an agent job from completing when running a package that relies upon an Indirect XML Configuration File. When executing the agent job I received a couple of warnings, one from the SSIS Package itself and another from the Event Viewer:

Event Viewer:

SSIS_EnvVar.png

SSIS catalogue error:

SSIS_EnvVar2.png

The problem here was that I had my environment variable used to specify the location of my config file set up in my User Variables and not in System Variables. This was also a problem because I’d specified the [NETWORK\SQLAgent] user as the SQL Agent service logon account.

SSIS_EnvVar3.png

Once I switched my variable into System Variables my agent job ran through without error.

SSIS – Keyword not supported: ‘provider’

Standard

Today I’d set up an agent job for a project deployment model SSIS package. In my project I’m using connection managers to handle my database connections and had configured my SSIS package to use the variables I wanted, however when I kicked off my agent job, it failed with this error:

Error: System.ArgumentException: Keyword not supported: 'provider'

SSISPackage_WrongConnectionType

Eventually I figured out that during configuration of my SSIS project I’d pointed an ADO connection in my SSIS package to an OLEDB connection manager in my environment variable. Once I swapped the connection to point to an ADO connection the package ran as expected.

SSIS Script Task Won’t Debug

Standard

You’ve gone into the editor and added your breakpoints to your C# but when you run your package the script task won’t debug, what’s going on?

There are a couple of causes of this we’ve found.

Number 1: You have two or more script tasks and two or more of these have break points in them. Only set break points in one script task at a time.

Number 2: You need to set the Run64BitRuntime property of the package to false. Right-click on your project and select ‘Properties’, then go to the Debugging tab and change the below record to FalseSSIS_Debug