Solved

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

Posted on 2013-10-28
5
1,206 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 18

Assisted Solution

by:Dave Ford
Dave Ford 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 66

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 66

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

728 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