July 16, 2012 at 3:31 pm
I have created a nightly job in SSMS R2 that has multiple steps that connect to an Oracle server and copy a table to our SQL server 10.50.2811.
The first 2 steps are working correctly but I have run into a problem with one of my tables not updating. The history file shows the following error message:
LogJob History (Business_Daily03)
Step ID3
ServerIS-SQL\ISSQLSERVER
Job NameBusiness_Daily03
Step NameUpdate BR_POS_BUD
Duration00:00:05
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: DOMAINNAME\SqlSvc. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 9:30:01 PM Error: 2012-07-15 21:30:06.17 Code: 0xC0047062 Source: Data Flow Task 1 Source - BR_POS_BUD [1] Description: Microsoft.SqlServer.Dts.Runtime.DtsCouldNotCreateManagedConnectionException: Could not create a managed connection manager. at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction) at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction) End Error Error: 2012-07-15 21:30:06.17 Code: 0xC0047017 Source: Data Flow Task 1 SSIS.Pipeline Description: component "Source - BR_POS_BUD" (1) failed validation and returned error code 0x80131500. End Error Error: 2012-07-15 21:30:06.17 Code: 0xC004700C Source: Data Flow Task 1 SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2012-07-15 21:30:06.19 Code: 0xC0024107 Source: Data Flow Task 1 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:30:01 PM Finished: 9:30:06 PM Elapsed: 4.29 seconds. The package execution failed. The step failed.
The only thing I see different about this table is that it has 17 key fields and I recall reading something about key limitations. Can someone help me troubleshoot what is causing this job step to fail?
Thanks!
July 16, 2012 at 5:04 pm
Not sure what's broken, but in SQL Server, only 16 fields allowed for primary keys.
July 17, 2012 at 7:23 am
Any way around that just to do an import? I do not have any control in the structure of the table. We are importing the data from our software vendor so we can have it in SQL instead of Oracle.
July 17, 2012 at 7:29 am
It looks like the SSIS package couldnt connect to Oracle, have you checked the connection managers connect as they should?
July 17, 2012 at 7:32 am
Yes, the connection is working. The 2 steps before this one connect to Oracle the same way without any issues.
July 17, 2012 at 7:34 am
Well it has to be within that task then which is failing on validation.
What is the task doing, can you attach screen shots?
July 17, 2012 at 8:35 am
I get the following error message when I try to re-create the step:
An exception occurred wihile executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
OLE DB provider "OraOLEDB.Oracle" for linked server "BUSINESS_LIVE" returned an invalid index definition for table "BR_POS_BUD"
(Microsoft SQL Server, Error: 7422)
Sorry, couldn't figure out how to paste the error screen in here so I just re-typed it.
July 17, 2012 at 9:33 am
My guess, and it's that, is that you are transferring over the index definition to SQL Server for the PK, trying to parse it, and it's failing because there are 17 fields.
I assume you could somehow do this manually, omitting or perhaps combining fields, to get to the 16 limit.
July 18, 2012 at 2:11 am
If you want to enforce unique ness, maybe a computed hyshbytes MD5 column based on the 17 columns would be the way to go
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply