Package Validation Error in Custom PipelineComponent

Hi

I'm developing an PipelineComponent (ComponentType.Transform). When I try to execute this component, the following error is thrown:

Code Snippet

===================================

Package Validation Error (Package Validation Error)

===================================

Error at Data Flow Task [DTS.Pipeline]: Buffer Type 1 had a size of 0 bytes.

Error at Data Flow Task [DTS.Pipeline]: The buffer manager failed to create a new buffer type.

Error at Data Flow Task [DTS.Pipeline]: The Data Flow task cannot register a buffer type. The type had 50 columns and was for execution tree 0.

Error at Data Flow Task [DTS.Pipeline]: The layout failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

Program Location:

at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.ValidateAndRunDebugger(Int32 flags, DataWarehouseProjectManager manager, IOutputWindow outputWindow, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, ProjectItem startupProjItem, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchActivePackage(Int32 launchOptions)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.Launch(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)

Can anyone help me, please? The "Integration Services Error and Message Reference" didn't help me much.

Thank you

Manuel Bauer

The error does not give me any clues other than it happens during a validation stage, so what is in your Validate method?

Have your tried debugging this, i.e. Visual Studio attached to the execution host?

|||

Hi

Thank you for your answer. My pipeline component's validate method runs without any problems, I debugged this in design time and in run time (before the exception occured). In my opinion, the problem is the early validation (http://sqljunkies.com/WebLog/knight_reign/archive/2005/04/20/12365.aspx) of the data flow task. As I expect, the data flow task does any validation itself.

I can't imagine what "The Data Flow task cannot register a buffer type. The type had 50 columns and was for execution tree 0." means, so I really don't know where the problem could be.

The exception is thrown immediately after running the package (also after running the validate method of my pipeline component).

|||

Does the package work if you take your component out of it?

The early validation vs late validation is the same Validate method calls, and since it generally works, I'd say it is clear that your transform has a problem, regardless of if it is the early or late call. Early and Late means normally Validate gets called twice, there is no difference between them. The Task will have a validate method, and a Data Flow will then call Validate for each child component.

How can the exception be thrown "after" running a package, Execute will never get called if a task fails validation. What is the return of your Validate method when you debug it?

Did you debug against dtsdebughost or just Visual Studio (designer)? For simplicity when trying to resolve run-time issues I set the code project debug option to use dtexec directly, just using /F and a package file. It is faster than attaching to a designer instance of VS.

|||

I found a solution for my problem. I was thinking wrong about this validation thing, I thought that the data flow task does some independant, general validation itself.

In my component, I "forgot" to call base.Validate(), which would no be a problem if the own validate method is implemented as id should. In my case, it was not.

Thank you for your competent analysis of my problem.

Manuel Bauer

Package Validation Error + Code 0xC004801C

Hi,

I am learning to use SSIS for creating packages. I am getting the following error when I try to execute my package...

Package Validation Error

Error at Data Flow Task[DTS. Pipeline]:The "runtime connection "FlatFileConnection "(122)" in the connection manager collection, Connections, of component "Flat File Destination ("120") does not have a value for the ID property. Verify that the ConnectionManagerID property of the runtime connection object has been set for the component.

Error at Data Flow Task [DTS.Pipeline]: component "Flat File Destination" (120) failed validation and returned error code 0xC004801C.

Error at Data Flow Task[DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

I'd appreciate if someone can help me with this. Do I need to change some settings? Also, what does the number in brackets indicate, say in FlatFileConnection(122)? Is it valuable information while debugging?

Just to give an idea, my package is like this -

source FlatFile -> Derived Column -> Lookup1 ->Lookup2 ->OLE DB destination(SQL server DB table)

Lookup1 and Lookup2 have error redirected to one flat file destination each.

Thanks in advance!

Geez....the error was just because I hadn't assigned a Connection Manager to that Flat File destination!! Oops!!!

Package Validation Error

Hi,

I am using Look-UP Transformation to do ETL from a Flat file to a SQL Server Fact Table doing Keylookups from different Dimension Tables:

I am using two connections in the Connection manager, One for the FlatFile and one for the SQLServer Table..

While Executing the Package, I am getting the following error:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task [DTS.Pipeline]: The "runtime connection "OleDbConnection" (110)" in the connection manager collection, Connections, of "component "OLE DB Destination" (102)" does not have a value for the ID property. Verify that the ConnectionManagerID property of the runtime connection object has been set for the component.

Error at Data Flow Task [DTS.Pipeline]: component "OLE DB Destination" (102) failed validation and returned error code 0xC004801C.

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

Can anyone help me ?

Thanks in Advance,

Sundar

I guess this might help you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=108043&SiteID=1

|||Make sure that in the flat file source and the destination that you have actually chosen a connection manager.

You'll get this error if you don't chose a connection manager.|||

Thanks a lot Phil and Unni.

Once I configured the connection Manager, Package worked like a charm !!

Cheers,

Sundar

Package Validation Error

I have a package which takes a text file and imports data into a SQL table. It returns the Package Validation Error:

Error at Data Flow Task [SQL Server Destination [1430]]: Failure inserting into the read-only column "ZipCode".

The column ZipCode in the sql table is a varchar(50) column. I do not understand why it thinks the column is a read only column; if someone could shed some light on this I would greatly appreciate it.

Thanks!

GN

Take a closer look at the table, perhaps it is a calculated column. What is the CREATE TABLE statement for that table?|||

It's not a calculated column. It is just a standard varchar column. The create table script generated by SQL is:

CREATETABLE [dbo].[ZipToState](

[ZipCode] [varchar](50)COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL,

[StateAbbr] [varchar](2)COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL,

[City] [varchar](50)COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL,

[PreferredCityName] [varchar](50)COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL,

[ID] [int] IDENTITY(1,1)NOTNULL

)ON [PRIMARY]

Actually I'm starting to think that it may actually be a permissions issue.

Thanks!

GN

|||

Okay I've confirmed that it is a permission error.

When I use my login, which has sysadmin privleges, for the database connection there is no problem inserting into the table fields. When I use another login that does not have sysadmin privleges then the package errors out stating the field is read-only. I've the login the bulkadmin server role but I get the same error. I've also given the login the explicit server permission Administer bulk operations but I keep getting the same error. Additionally the login has insert, delete, and select permissions for the table. So does anyone know what permissions are needed for the login to be able to perform the bulk insert via the package?

Thanks!

Package Validation Error

I have a package which takes a text file and imports data into a SQL table. It returns the Package Validation Error:

Error at Data Flow Task [SQL Server Destination [1430]]: Failure inserting into the read-only column "ZipCode".

The column ZipCode in the sql table is a varchar(50) column. I do not understand why it thinks the column is a read only column; if someone could shed some light on this I would greatly appreciate it.

Thanks!

GN

Take a closer look at the table, perhaps it is a calculated column. What is the CREATE TABLE statement for that table?|||

It's not a calculated column. It is just a standard varchar column. The create table script generated by SQL is:

CREATE TABLE [dbo].[ZipToState](

[ZipCode] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[StateAbbr] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[City] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[PreferredCityName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[ID] [int] IDENTITY(1,1) NOT NULL

) ON [PRIMARY]

Actually I'm starting to think that it may actually be a permissions issue.

Thanks!

GN

|||

Okay I've confirmed that it is a permission error.

When I use my login, which has sysadmin privleges, for the database connection there is no problem inserting into the table fields. When I use another login that does not have sysadmin privleges then the package errors out stating the field is read-only. I've the login the bulkadmin server role but I get the same error. I've also given the login the explicit server permission Administer bulk operations but I keep getting the same error. Additionally the login has insert, delete, and select permissions for the table. So does anyone know what permissions are needed for the login to be able to perform the bulk insert via the package?

Thanks!

Package hangs on parallel "upserts"

I have data flow tasks, one which validates the import file and one which processes the import file if the validation passed. The validation runs and pushes the three row types to three different recordset destinations. When I enter the processing data flow task, I have three parallel trees processing each recordset saved in the previous task. I'm using a script component to generate the rows which are then sorted and merged with the production database to find existing records. Based on this, I split to an OLE DB command (running an UPDATE command) or OLE DB destination (to simply insert the records.)

In this particular case, all records are being updated and nothing is being inserted new. Two of the three trees will complete the sort but hang on the merge, split, and OLE DB command components. The other will do the same but also hang on the split.

In another case, I truncated each destination table before running the package and the package runs fine.

Are toes being stepped on in the data flow task and causing a deadlock?

Update: I removed the sort transformation and sorted the rows before pushing them to the recordsets and I still get the same results.

If you were getting a deadlock then I think SQL Server would capture this and throw an error. That's not what's happening here. Its more likely that a lock is occurring. Use sp_who2 and sp_lock to determine if blocking is occurring.

-Jamie

|||

A common issue when your modifying the same table with multiple paths in your data flow is that you will have a deadlock on the destination table. To resolve:

1) choose "Fast load" and "Don't lock table" in the OLEDB Destination

2) ensure that you don't have a race condition between the paths, i.e. that the updates depends on the inserts _in the same run_ have reached the DB before the updates.

Cheers/Kristian

|||I checked the sp_who2 stored procedure and nothing was in the BlkBy column.
Fast load is being used and I chose "don't lock table" but that didn't fix the issue.
The updates don't depend on the inserts. I merge the input with production on keys and split on NULL values in production.

I ended up splitting the three trees into three data tasks to see if that would fix the issue but I get the same results, only this time I noticed no all rows have left the merge. The merge is a left outer join on keys and in this case all records input are already in the database. So I'm joining 62k input records with the same number of (identical) production records and 9,937 rows leave the merge. Also, when running the package, that task remains yellow.
|||

The solution is pretty simple. Do the inserts and updates in seperate data-flows. You can use raw files to pass data between data-flows.

-Jamie