Solved

SSIS Problem

Posted on 2011-03-22
12
2,040 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
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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now