• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2217
  • Last Modified:

SSIS Problem

Hello Experts,

I have a following problem with SSIS.Pulling out data from Sybase database and putting into SQL Server using SSIS.Package is getting executed successfully on QA Server but when created job on Production packages executed for 7 hrs and then get failled.Partial data load has been done but after that got following error.Using SQL server 2008 ,Windows server 2008.

Message
Executed as user: for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  4:04:28 PM  Error: 2010-07-18 23:03:01.42     Code: 0xC0202009     Source: Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred.

Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Communication link failure".  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "TCP Provider: An established connection was aborted by the software in your host machine.  ".  End

Error  Error: 2010-07-18 23:03:01.54     Code: 0xC0209029     Source: Description: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "OLE DB Destination Input" (188)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (188)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.  End Error

  Error: 2010-07-18 23:03:01.61     Code: 0xC0047022     Source: .Pipeline     Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination" (175) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (188). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.  End Error  

Error: 2010-07-18 23:03:02.57     Code: 0xC02020C4     Source:     Description: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.  End Error  

Error: 2010-07-18 23:03:02.60     Code: 0xC0047038     Source: Push new records to Destination SSIS.Pipeline     Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Interim" (1) returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.  End Error  

Error: 2010-07-18 23:03:02.69     Code: 0xC001F009     Source:       Description: The type of the value being assigned to variable "User::maxModifiedDatetime_curr" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.  End Error  

Error: 2010-07-18 23:03:02.94     Code: 0xC0047062     Source: Push new records to Destination Push max date to variable [124]     Description: System.Runtime.InteropServices.COMException (0xC001F009): The type of the value being assigned to variable "User::maxModifiedDatetime_curr" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.     at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)     at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PostExecute()     at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPostExecute(IDTSManagedComponentWrapper100 wrapper)  End Error  


Error: 2010-07-18 23:03:02.96     Code: 0xC0047018     Source: Push new records to Destination SSIS.Pipeline     Description: component "Push max date to variable" (124) failed the p...  The package execution fa...  The step failed.

Thanks!
Stad

0
Stad110011
Asked:
Stad110011
3 Solutions
 
rajvjaCommented:
Hi,

  It might be the problem that your local security principles are blocking the long running connections.
Or connection to the server is broken at certain times. Try running with chunks of data and check how it runs.
0
 
Barry CunneyCommented:
Hi Stad,
Because you need to pull data from Sybase and becauase of the first message in the list I am wondering if the provider which is specfied in the OLEDB source, Microsoft SQL Server Native Client 10.0 is correct for Sybase
0
 
Barry CunneyCommented:
Hi Stad,
Check this article out which mentions about Sybase and SSIS and Sybase drivers
http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/bfbafbb9-c095-4250-9dc0-85f3731c9bda/


0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Barry CunneyCommented:
Also in the Connection Managers pane in BIDS SSIS, there should be a connection object for your Sybase source - right click on this and choose Edit
In the pop up dialog there shoud be a Test Connection button
Click this button
What message is returned?






 
0
 
jimtpowersCommented:
Since it is working fine in QA but failing in Production, I'm wondering if your user context in Production has the necessary rights to your Sybase database?
0
 
Stad110011Author Commented:
Rajvja / BCUNNEY,

Thank you for instant reply.

Rajvja - I will try to load chunk of data.

BCUNNEY  - I got the message as TEst conncetion succeeded.PAckage was able to load partial data e.g till november from January but failed for December.
0
 
Stad110011Author Commented:
Jimtpowers,

User context is fine because I can see some of rows out of whole row set  in destination
0
 
Barry CunneyCommented:
As a test, it may be worth setting a timeout value in the Source Connection setting, to rule out timeout as the problem
0
 
Barry CunneyCommented:
Another approach may be to look at what is happening from the Sybase side - I have no epxertise on Sybase, but maybe there is soem sort of an audit trace that can be done on the Sybase database to see what it is getting hit with from the SSIS package connection and why it is erroring
0
 
Alpesh PatelAssistant ConsultantCommented:
1. It may be permission issue
2. May be port is blocked
3. Data issue
4. Source & Destination Data type difference use Conversion
0
 
PedroCGDCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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