maandag 26 september 2011

SSIS Errors in TDS Stream & the expression for variable "MyVar" failed evalution.

Current I'm developing SSIS packages on a development environment running Windows 2003R2 SP2 32Bits and SQL 2008 SP2 32 Bits.

I just recent did get access to this environment, before that all development has been done in production environment running Windows 2008 64Bits and SQL 2008 SP2 64 bits

In the nearby future I will receive a development environment that represents the production environment, but for now I have to do with the development environment I have.

While developing on the production environment all seams to be working fine, now developing on the development environment I ran into some strange issues.

First let me see you what I have for packages, it pretty simple,  a master that executes some packages in parallel:
When I ran this package from a SQL Agent job add random the package will fail with the error:
The expression for variable "MyVar" failed evalution. There is an error in the expression.....

The packages executed are not more then some audit info and a simple DFT: OLE_DB Source > OLE_DB Target.
The variables displayed in the processing packages are also defined in the master package.
When running the packages from BIDS no errors are displayed on the MyVar validation.

The second error I do receive is : SSIS package fails with Protocol error in TDS stream
This error occures when running a package that needs uses a view as OLE_DB source that uses the same table to check of inserts are needed as it is used as OLE_DB Target to write the records to.

First let's start with some solutions I found on the web for the second metioned error: SSIS package fails with Protocol error in TDS stream.

First I found comes from (Ramoji Ryali's Fundas). He says that this error is issued when the SQL Network Protocol is changed from TCP/IP to NamedPipes. The solution should be:
  1. Go to Start--> All Programs--> Microsoft SQL Server 2008--> Configuration Tools --> SQL Server Configuration Manager
  2. Expand the node SQL Native Client 10.0 Configuration
  3. Right click on "Aliases" and then select "New Alias..."
  4. Provide the IP address of the Source SQL server against the "Server" field and specify the Source server name against the "Alias Name" field.
  5. Choose the protocol as "NamedPipes" and click OK
He also metioned that he is processing data from server X to server Y, this is not my case, I'm even processing on the same server for developing and same database.

The second possible solution I did found came from Microsoft:
They mention that I could have to do with the TCP Chimney feature that is enable in Windows 2003 SP2.

First thing to do is to check or you have the latest NIC driver on your server installed. When this is the case you can try to disable the chimney feature following the below steps.
  1. Click Start, click Run, type cmd, and then click OK.
  2. At the command prompt, type:
  3. Netsh int ip set chimney DISABLED
  4. Press the ENTER key.
I tried this second solution and was able to do 3 runs before writing this blog without the TDS error. But I also find that I did not use with(nolock) in the source view query on the join of the table that I need also to insert the records into. Because it's working I don't want to enable the chimney again to see what was really causing the error. Maybe when I have some spare time left I will let you know. For now I hope you here find a solution for your problem.

Let's get back to the first error: The expression for variable "MyVar" failed evalution. This seems to occure on the master packages, I think I already know why, I will tell you what is done in the master package.

In the master package I do have some connection managers for the package that need to be executed. I use a connectionstring expression on the connectors to make it flexible to run the packages when they are placed in a different location.

The expression is: @[User::varFolderPackages_str"]+"<nameofpackages.dtsx>"
The variable @[User::varFolderPackages_str"] is also an expression based variable that consists of the expression: @[User::varFolderSSISRoot_str"] + "Packages\\" where @[User::varFolderSSISRoot_str"] again is an expression @[User::varFolderRoot_str"] + @[User::varNameSSISProject_str"] + "\\"

Those are both filled hard by a package configuration with D:\SSIS and SSIS_Staging_Hubs for example.

Because I don't realy know what is causing this, and it does occure random, some runs the master packages runs without error and the next following run does again error on the variable evalution I think thought it might have to do with the parallel processing and handeling the variables.

The simpel solution is that I changed the package connection managers to a hardcoded connection string. For now this solves my problem, no errors last 3 run's, but it removed my flexibility. All my SQL connections are created by using package configurations based on a SSISMetaData.SSISConfiguration table. I think I will start adding the package connection managers to.

Hope you enjoy this blog! When you have comments, better solution and recommendation I like to hear from you. Dynamic Intelligence | LinkedIn | Blog | Twitter