?
Solved

SSIS Problem

Posted on 2011-03-22
12
Medium Priority
?
2,141 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

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.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

762 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