[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1243
  • 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.
Jim

( SSIS and SQL expert, DB2 N00b )
0
Jim Horn
Asked:
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.

HTH,
DaveSlash
0
 
momi_sabagCommented:
actually the schema varies by platform
on mainframes and luw it's sysibm
0
 
mustaccioCommented:
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
 
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.
0
 
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.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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