CODEWITHSUNDEEP
viewssispackageoledb
JohnDoDo
JohnDoDo

Reputation: 4900

SSIS - what do I look for? "Invalid column type from bcp client for colid 1."

I have an issue with a SSIS package which was working up till now but suddenly starts throwing up an error which I don't know were to place.

I'm uploading bulk records into a OLE DB Destination, inside a view, and I get this error:

An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid column type from bcp client for colid 1.".

What does: Invalid column type from bcp client for colid 1. mean? I've searched high and low for this but still don't understand why it fails now (and it worked before).

I've checked the data that goes to the view and I see no differences in types or lengths.

What do I look for? Have you encountered this? what was the issue?

Upvotes: 2

Views: 7035

Answers (3)

Robbie
Robbie

Reputation: 71

This happened to me. Here’s my understanding of what happened:

  • I created a table that included column MY_COLUMN VARCHAR(50)
  • I created a view of this table for use in importing data via SSIS.
  • I created an SSIS package to import data through this view.
  • I tested the package and it worked fine.

Here’s where the problem started:

  • Later, I realized that VARCHAR(50) wasn’t long enough for the data that MY_COLUMN should hold, so I increased it to VARCHAR(100).
  • I updated the SISS package to reflect the change.
  • However, no one has told the view that the underlying schema has changed. So it still thinks the length of MY_COLUMN is VARCHAR(50). (Sorry to anthropomorphize here…)
  • The next time I ran the SSIS package, it failed with the error "Invalid column type from bcp client for colid xx."

Solution: Tell the view about the schema change, i.e. recompile it. No need to drop it and recreate it. (Note, recompile simply means to run the script that modifies the view.) It’s simple fast and easy.

Unfortunately, this is a typical Microsoft error message: Completely accurate but utterly useless.

-Robbie

Upvotes: 0

JohnDoDo
JohnDoDo

Reputation: 4900

I've managed to fix this by deleting and then recreating the view where I was inserting the data.

I'm posting this, maybe someone else could use it and not end up tearing their hairs out.

Upvotes: 5

Widor
Widor

Reputation: 13275

I'd be surprised if it had just stopped working and nothing is different.

In general terms, the error is saying that the data type of the data provided by SSIS (your 'BCP client') does not match that of column #1 in your destination.

In other words, I'd expect to find either the wrong type of data defined, or a string that exceeds the defined length, or a decimal where you should have an Int. Might take some searching, but the bad data's there somewhere!

Upvotes: 5

Related Questions