When Package in MSDB run from a job it completes but it only populates 4 out of 31 Tables

Question

Post reply

When Package in MSDB run from a job it completes but it only populates 4 out of 31 Tables

Welsh Corgi

SSC Guru

Points: 116520
More actions
March 28, 2012 at 12:45 pm

#258326

When Package in MSDB run from a job it completes but it only populates 4 out of 31 Tables .
What could be happening?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

This is in the Job History:

Step NameLoadXYZDW_CYP_A_Thru_C

Duration00:09:51

Sql Severity0

Sql Message ID0

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted0

Message

Executed as user: UserName\ServerName$. ...2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 2:26:04 PM Error: 2012-03-28 14:26:05.71 Code: 0xC0040019 Source: Load Claims_Special_Use Load cyp_Claims_Special_Use (SSIS.Pipeline)

Description: Data Flow objects cannot be loaded. Check if Microsoft.SqlServer.PipelineXml.dll is properly registered.

End Error Error: 2012-03-28 14:26:05.71 Code: 0xC0010018 Source: Load cyp_Claims_Special_Use

Description: Failed to load task "Load cyp_Claims_Special_Use", type "SSIS.Pipeline.2".

The contact information for this task is "Performs high-performance data extraction, transformation and loading;

Code: 0xC0040019 Source: cyp_Class_Footnotes Load cyp_Class_Footnotes (SSIS.Pipeline)

Description: Data Flow objects cannot be loaded. Check if Microsoft.SqlServer.PipelineXml.dll is properly registered.

End Error Error: 2012-03-28 14:26:05.72 Code: 0xC0010018

Source: Load point_cyp_Class_Footnotes Description: Failed to load task "Load cyp_Class_Footnotes", type

"SSIS.Pipeline.2". The contact information for this task is "Performs high-performance data extraction,

Code: 0xC0040019 Source: Load cyp_Class_Table Load cyp_Class_Table (SSIS.Pipeline)

Description: Data Flow objects cannot be loaded. Check if Microsoft.SqlServer.PipelineXml.dll is properly registered.

End Error Error: 2012-03-28 14:26:05.74 Code: 0xC0010018 Source: Load cyp_Class_Table

Description: Failed to load task "Load cyp_Class_Table", type "SSIS.Pipeline.2".

The contact information for this task is "Performs high-performance data extraction, transformation and loading;

Code: 0xC0040019 Source: Load cyp_Client_Master Load cyp_Client_Master (SSIS.Pipeline)

Description: Data Flow objects cannot be loaded. Check if Microsoft.SqlServer.PipelineXml.dll is properly registered.

End Error Error: 2012-03-28 14:26:05.75 Code: 0xC0010018 Source: Load cyp_Client_Master

Description: Failed to load task "Load cyp_Client_Master", type "SSIS.Pipeline.2".

The contact information for this task is "Performs high-performance data extraction, transformation and loading;

End Error Error: 2012-03-28 14:26:05.75 Code: 0xC0040019 Source: Load cyp_Company_Location

Load cyp_Company_Location (SSIS.Pipeline) Description: Data Flow objects cannot be loaded.

Check if Microsoft.SqlServer.PipelineXml.dll is properly registered. End Error Error: 2012-03-28 14:26:05.76

Code: 0xC0010018 Source: Load cyp_Company_Location

Description: Failed to load task "Load cyp_Company_Location", type "SSIS.Pipeline.2".

The contact information for this task is "Performs high-performance data extraction, transformation and loading;

Code: 0xC0040019 Source: Load cyp... The package executed successf... The step succeeded.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Welsh Corgi (3/28/2012)
When Package in MSDB run from a job it completes but it only populates 4 out of 31 Tables .
What could be happening?

First, is it me or does SSIS just not like you? 😉

Do you have any logging setup in your packages to help you identify potential problems?

Also, you might want to look into this: http://dtloggedexec.codeplex.com/.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Lynn Pettis (3/28/2012)
Welsh Corgi (3/28/2012)
When Package in MSDB run from a job it completes but it only populates 4 out of 31 Tables .
What could be happening?
First, is it me or does SSIS just not like you? 😉
Do you have any logging setup in your packages to help you identify potential problems?
Also, you might want to look into this: http://dtloggedexec.codeplex.com/.

Did you see the following error:

Data Flow objects cannot be loaded. Check if Microsoft.SqlServer.PipelineXml.dll is properly registered.

End Error Error: 2012-03-28 14:26:05.74 Code: 0xC0010018 Source: Load cyp_Class_Table

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

I have logging set to log OnError and OnTask Failed but this is all that the log contains:

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message

Obviously I'm not doing it right?

I'll take a closer look at your article.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Welsh Corgi (3/28/2012)
Lynn Pettis (3/28/2012)
Welsh Corgi (3/28/2012)
When Package in MSDB run from a job it completes but it only populates 4 out of 31 Tables .
What could be happening?
First, is it me or does SSIS just not like you? 😉
Do you have any logging setup in your packages to help you identify potential problems?
Also, you might want to look into this: http://dtloggedexec.codeplex.com/.
Did you see the following error:
Data Flow objects cannot be loaded. Check if Microsoft.SqlServer.PipelineXml.dll is properly registered.
End Error Error: 2012-03-28 14:26:05.74 Code: 0xC0010018 Source: Load cyp_Class_Table

Yes, and unfortunately nothing I Googled seemed to match. Guess my Google-fu isn't strong today.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Oh don't let me down Lynn, you my only option here. :laugh:

I tried goggling and I did not get much either.

One link told me to register the dll which I could not find on my machine (64 bit 2008 R2) and it gave a kb link that was not any help to me..

http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/79eccbec-3e06-4143-808f-be4088757ef2

Maybe is you goggled it manually it would work.:hehe:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Could it have something to do with SQL Server Integrated Services 10.0 failing to start?

The Tables in which I'm populating from a transformation task (AS400 Source) are not populating.

The Tables that do populate are the ones that I had to use an OPENQUERY Statement encapsulated in a Stored Procedure.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Going out on a limb here, I don't think so. From BOL:

The Integration Services service is not required if you only want to design and execute Integration Services packages. However, the service is required to list and monitor packages using SQL Server Management Studio. For more information about Integration Services service, see Managing Integration Services Service and Managing Integration Services Packages.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

Lynn Pettis (3/28/2012)
Going out on a limb here, I don't think so. From BOL:
The Integration Services service is not required if you only want to design and execute Integration Services packages. However, the service is required to list and monitor packages using SQL Server Management Studio. For more information about Integration Services service, see Managing Integration Services Service and Managing Integration Services Packages.

Dah, it has been another very long day. I remember now.

Thanks!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

Please do not respond to this post for I have restated the question at the following post:

http://www.sqlservercentral.com/Forums/Topic1274990-1550-1.aspx

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 11

Issue has been resolved.

It was a permissions issue on the Account used for the SQL Server Agent.

For the task that I had to use Stored Procedures with OpenQuery Statements it worked.

The majority of the tables were being populated from an AS400 using a Data Transformation task.

For now I used my account for the SQL Agent Service but I need to determine a more appropriate Account to use for this Service. It was set to Network Service which does not cut it.:blush:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/