Solved

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

Posted on 2013-10-28
5
1,177 Views
Last Modified: 2016-02-11
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.
Jim

( SSIS and SQL expert, DB2 N00b )
0
Comment
Question by:Jim Horn
5 Comments
 
LVL 18

Assisted Solution

by:daveslash
daveslash earned 34 total points
ID: 39606318
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.

HTH,
DaveSlash
0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 33 total points
ID: 39606573
actually the schema varies by platform
on mainframes and luw it's sysibm
0
 
LVL 8

Assisted Solution

by:mustaccio
mustaccio earned 33 total points
ID: 39606913
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
0
 
LVL 65

Author Comment

by:Jim Horn
ID: 39608460
>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.
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 0 total points
ID: 39645079
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.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now