• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1262
  • Last Modified:

SSIS R2: errmsg --> SYSCOLUMNS is an undefined name <-- going from SQL to DB2

Hi All

I have an 2008 R2 package that has seven data flow tasks going from SQL Server 2008 to the same DB2 table.   Six of the seven flows work great, but one (2nd in the chain) returns an error message:  
[OLE DB Destination [227]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available.  Source: "Microsoft DB2 OLE DB Provider"  Hresult: 0x80040E37  Description: "DTA.SYSCOLUMNS is an undefined name. SQLSTATE: 42704, SQLCODE: -204".

Open in new window

Question:  What's the 'SYSCOLUMNS is an undefined name' ?

I dropped and re-created the DB2 destination, remapped, same errmsg.

Thanks in advance.

( SSIS and SQL expert, DB2 N00b )
Jim Horn
Jim Horn
4 Solutions
Dave FordSoftware Developer / Database AdministratorCommented:
SYSCOLUMNS is a "catalog view" that lists all columns and their corresponding table-names. On DB2 for i, it's located in schema QSYS2. I don't know why your query is looking for it in DTA.

actually the schema varies by platform
on mainframes and luw it's sysibm
I suggest you use the IBM DB2 provider, not the Microsoft one. You don't mention your DB2 version and platform. You'll need to download IBM Data Server Client or at least Runtime Client from here:  http://www-01.ibm.com/support/docview.wss?uid=swg27016878
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
>I don't know why your query is looking for it in DTA.
Me neither.  

The source task is OLE DB from SQL Server, there's a derived column task to add rows, all eight are nvarchar's.

The destination task is OLE DB into DB2 table DTA.F554075I.
When I hit the Preview button it correctly displays the current contents of the table.

It may be a destination table business rule issue that isn't displaying an intuitive enough error message, but I don't have the DB2 experience to flush this out.   I am getting a couple of truncation warnings, but no other error messages.

And, since the first data pump succeeded, I know it is not a privs issue.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
I discovered that there was a multi-column primary key constraint in the table, and that the source file had duplicates rows within these columns.  Once I eliminated the duplicates, the error message disappeared.

Points lowered and awarded for troubleshooting assistance.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now