[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SSIS Problem

Posted on 2011-03-22
12
Medium Priority
?
2,176 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 664 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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 664 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 672 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

656 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