Solved

SSIS Problem

Posted on 2011-03-22
12
2,080 Views
Last Modified: 2013-11-10
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
Comment
Question by:Stad110011
[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
12 Comments
 
LVL 11

Assisted Solution

by:rajvja
rajvja earned 166 total points
ID: 35190124
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
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 35190143
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
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 35190279
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 17

Expert Comment

by:Barry Cunney
ID: 35190325
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
 
LVL 4

Expert Comment

by:jimtpowers
ID: 35190533
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
 

Author Comment

by:Stad110011
ID: 35190545
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
 

Author Comment

by:Stad110011
ID: 35190644
Jimtpowers,

User context is fine because I can see some of rows out of whole row set  in destination
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 35190743
As a test, it may be worth setting a timeout value in the Source Connection setting, to rule out timeout as the problem
0
 
LVL 17

Assisted Solution

by:Barry Cunney
Barry Cunney earned 166 total points
ID: 35190790
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
 
LVL 21

Accepted Solution

by:
Alpesh Patel earned 168 total points
ID: 35239514
1. It may be permission issue
2. May be port is blocked
3. Data issue
4. Source & Destination Data type difference use Conversion
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 35957237
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

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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…
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.

726 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