NullReferenceException - PrimeOutput Error

I got an error in SSIS package that is reading data from DB2/AS400 into SQL Server 2005 x64. All rows from the source table were moved to the target table, but DataReader Source still failed:

[DataReader Source VALNREQ [1]] Error: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper90 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer90[] buffers, IntPtr ppBufferWirePacket)

[DTS.Pipeline] Error: The PrimeOutput method on component "DataReader Source F57005" (1) returned error code 0x80004003. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

After a while I realized that a column has been added to the source table. I refreshed the DataReader Source and directed new column to target and finally everything was working.

It would be short sighted to blame Microsoft for this error. I was using select * from table in DataReader Source. I changed it to the current a list of columns and I hope that my package will not fail if someone adds column to the source.

SSIS Send Mail – Mailbox Unavailable

Scenario:

· new development SQL Server 2005 server;

· project to upgrade existing DTS packages to SSIS;

· to notify users of failure, we wanted to use Send Mail task

· We got the following error:

Mailbox unavailable. The server response was: 5.7.1 Unable to relay for myemail@mydomain.com).

This was a new server and network administrators assured me that I am using right login and server (and that I do not need to specify username and password for SMTP server).

To take SSIS out of equation, I tried to configure Database Mail and send test email, but I got the same result. After a while I realized that we should use Windows Authentication using Database Engine Service Credentials instead of Anonymous Authentication.

I came back to my SSIS package and set SMTP connection to use Windows Authentication and then everything was working.

I assume that same error can be encountered when you do not have the right name of the account, name of the server…

Security Architecture of SSIS Packages

One thing that I am thinking about lately is how to make sure that package running in Development or Test environment does not change something in Production? Such problem may occur when:

1. a DBA forgets to update the configuration file
2. test or development is done in mixed environment (for example,  test DW servers are loaded from Production OLTP servers)
3. same connection (and login) is used for both reading and writing…

One way to solve that would be to use different logins in these environments. SQL Server logins (now that they are more robust in (SQL Server 2005) comes to mind as a potential solution.

Another solution is to give developers two sets of Windows accounts: regular login for everyday development work and a special login for firefights in Production  environment. That way a developer will not be able to mix up the privileges and accidentally change something in production.

SSIS Package Template in Visual Studio

If you put a package template in the following folder:

%ProgramFiles%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems

It will be available in Add > New Item:

Just make sure that you (re)generate new ID for each package created this way.

New Package ID

After a package is created using a package template, you should change its ID. If you forget to do that, SSIS logging will use same id for both original and the copy and you will not be able to differentiate in the log between results of these two packages.

• Open the package in VS. Make sure that you are in Control Flow.
• Click on ID property and choose Get New ID from the menu.

dtsConfig Sharing - Suppress Configuration Warnings

Often I have one dtsConfig file for my SQL connections for a project.   Unfortunately, the problem starts when you add a connection to one of the packages that will not be shared across. The other packages that use this same dtsConfig file will attempt to load it and throw an error since they don't contain the new connection.

One solution is to use a separate dtsConfig file for each DB connection, and then each package will selectively use only the ones it needs.

Alternate solution is to set SupressConfigurationWarnings property to True.

Another solution., 12/9/2009

Reviewer: Pelle (Halmstad, Sweden)

I only have one config file (ServerVariables.dtsconfig)
that all my packages consumes. The config file sets local variables that i use in expressions for the connectionstring (ServerName). All packages must have local variables for every db connection but it is up to me if i want to use them in the package.

Good Point, 11/2/2009

Reviewer: SSIS Developer (JerseyCity, United States)

Good Point!

Excel (and Access) Data Source in 64-bit environment

When you try to run a scheduled job (with SSIS package) that connects to Excel or Access file on 64-bit server you will get errors like following:

Excel Destination 1 1 [15]] Error: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.

[DTS.Pipeline] Error: component "Excel Destination 1" (15) failed validation and returned error code 0xC020801C.

[Connection manager "Excel Connection Manager "] 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".

This is all due to the fact that SQL Agents tries to run SSIS package using a 64-bit version of dtexec.exe. The utility will try to use 64-bit version of JET/ISAM driver to access Excel/Access files and fail since they do not exist.

Solution is to run 32-bit version of dtexec.exe. You can do that by manually creating the command that references the right utility. First, copy the dtexec parameters of the job step that you created (see picture):

Then replace it with a new Opertaing System (CmdExec) job step and assemble the command line from reference to the 32-bit dtutil (typically C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe) and copied parameters.

Cannot execute in 64-bit environment because the script is not pre-compiled

The task cannot execute in 64-bit environment because the script is not pre-compiled. Please turn on the option to pre-compile the script in the task editor."

This error is also result of .NET Framework incompatibility. Proper solution is to upgrade to SP2 or to install hotfix. However, if your IT does not want you to do that before thoroughly tested, you can do following:

• Make sure that each script task has PreCompile = True
• Open Script Task Editor, switch to Script tab and make sure that PrecompileScriptIntoBinaryCode = True
• Click on Design Script to open code editor (Visual Studio for Applications) and then choose it using File > Close and Return (VSA will recompile and store binary code in the package).
• Build project (and copy the package from Bin folder to target location)

couple of other gotcha's I have found, 2/28/2008

Reviewer: D (Washington DC, United States)

Also if there are any viewers or any other type of break point in the package remember to remove it also as it will cause it to fail as well. Also I found that if you are referencing 32 bit dll's in your script you need to set run64bitruntime to false.

SSIS and Vista (.NET Framework incompatibility)

If you have Vista as a development machine, you should upgrade to SP2 or upload fixes described in the following knowledgebase article. Apparently, there is a difference in version of .NET Framework which prevents Script task from working properly.

http://support.microsoft.com/default.aspx?scid=kb;en-us;931846

This problem may occur also if you have .NET Framework 2.0 hotfix later than build 2.0.50727.300 installed.