Solved

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

Posted on 2013-10-28
5
1,189 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

912 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

15 Experts available now in Live!

Get 1:1 Help Now