SSIS Package Runs In Dev. Studio But Not As a Job

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

SSIS Package Runs In Dev. Studio But Not As a Job

Author

Topic

Amarneh
Starting Member

10 Posts

Posted - 2011-09-18 : 03:27:46

I ran a SSIS package from development studio and it worked correctly, but I when I ran it from a job I got the following error:

Message
Executed as user: Project\BIAdmin. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 8:00:42 PM Error: 2011-09-17 21:39:13.06 Code: 0xC0047062 Source: Load FactSales Get Sales & Marketing Charges [18033] Description: System.Data.SqlClient.SqlException: Operand type clash: date is incompatible with int at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket) End Error Error: 2011-09-17 21:39:13.39 Code: 0xC0047022 Source: Load FactSales SSIS.Pipeline Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Get Sales & Marketing Charges" (18033) failed with error code 0x80131904 while processing input "Input 0" (18035). 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: 2011-09-17 21:39:13.58 Code: 0xC0047022 Source: Load FactSales SSIS.Pipeline Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Union All" (1549) failed with error code 0xC0047020 while processing input "Union All Input 1" (1550). 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: 2011-09-17 21:39:13.58 Code: 0xC02020C4 Source: Load FactSales MMI Data Slice From Staging [1] Description: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. End Error Error: 2011-09-17 21:39:13.59 Code: 0xC0047038 Source: Load FactSales SSIS.Pipeline Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "MMI Data Slice From Staging" (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 DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:00:42 PM Finished: 9:39:23 PM Elapsed: 5920.67 seconds. The package execution failed. The step failed.

please note that the development studio and the job are found on the same server.
any solution please ???

Thanks,
Yousef Amarneh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-18 : 03:34:48

seems like error in script task. There seems to be datatype incompatibilty between columns coming from different parts of union query. Please check if datatypes of corresponding columns are same

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Amarneh
Starting Member

10 Posts

Posted - 2011-09-18 : 03:44:24

The datatypes are tha same and it runs correctly on the development studio.

Thanks,
Yousef Amarneh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-18 : 05:56:18

are you sure the you're running same package from job? how have configured to call package from job? is it taking package from file system or sql server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Amarneh
Starting Member

10 Posts

Posted - 2011-09-18 : 07:20:15

it's taking from file system

Thanks,
Yousef Amarneh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-18 : 08:13:35

and any difference in values being send from job than when it executed locally?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Amarneh
Starting Member

10 Posts

Posted - 2011-09-18 : 08:23:56

No differences between two cases

Thanks,
Yousef Amarneh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-18 : 08:35:06

are you using configuration files for setting properties?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Amarneh
Starting Member

10 Posts

Posted - 2011-09-18 : 08:54:07

Yes I am using configuration file.

Thanks,
Yousef Amarneh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-18 : 08:59:52

it might be that you're passing a config value probably for a variable which is causing it to behave differently while executing from the job.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Amarneh
Starting Member

10 Posts

Posted - 2011-09-18 : 09:15:13

no I didn't pass any values for variables, the config file contains connection string.

Thanks,
Yousef Amarneh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-18 : 10:48:38

Ok. by any chance are you pointing to different db tables while executing from the job?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Amarneh
Starting Member

10 Posts

Posted - 2011-09-19 : 02:55:32

Can you explain your question please??

Thanks,
Yousef Amarneh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 04:31:47

ie any change in tables used in local version of package as against job version

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Amarneh
Starting Member

10 Posts

Posted - 2011-09-19 : 05:13:46

no

Thanks,
Yousef Amarneh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 06:07:23

have you done any changes to packages since you added job to server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Amarneh
Starting Member

10 Posts

Posted - 2011-09-19 : 07:45:16

no I haven't

Thanks,
Yousef Amarneh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 08:02:37

can you list out steps you did from completing the development of package locally to calling it from job?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DaddyQ
Starting Member

1 Post

Posted - 2011-09-20 : 09:28:54

If you've used paths in your package, you may need to change them to fully qualified unc paths from mappings.

Amarneh
Starting Member

10 Posts

Posted - 2011-09-21 : 02:49:21

I use script control and it contains the following script:

' Microsoft SQL Server Integration Services Script Component
' Write scripts using Microsoft Visual Basic 2008.
' ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.SqlClient

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
Inherits UserComponent

Dim connMgr As IDTSConnectionManager100
Dim sqlConn As New SqlConnection
Dim sqlCmdProduct As SqlCommand
Dim sqlCmdCustomer As SqlCommand
Dim sqlParam As SqlParameter
Dim SMChanges As Double

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
connMgr = Me.Connections.ADONETConnection
'The next line replaces Connection Open and Close Operations
sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)
End Sub

Public Overrides Sub PreExecute()
MyBase.PreExecute()
'
' Add your code here for preprocessing or remove if not needed
''
End Sub

Public Overrides Sub PostExecute()
MyBase.PostExecute()
'
' Add your code here for postprocessing or remove if not needed
' You can set read/write variables here, for example:
' Me.Variables.MyIntVar = 100
''
End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

sqlCmdProduct = New SqlCommand("SET DATEFORMAT DMY SELECT TOP 1 [Sales & Marketing Charges] FROM [Price Master] WHERE [Product Code] = '" + Row.ProductAlternateKey + "' AND Distributor = '" + Row.DepletionIncomeSegment + "' AND [DATE] < cast('" + Row.FullDateAlternateKey + "' as date) ORDER BY [DATE] DESC", sqlConn)

If IsDBNull(sqlCmdProduct.ExecuteScalar()) Then
Row.IsSMNull = "True"
SMChanges = 0
Else
Row.IsSMNull = "False"
SMChanges = sqlCmdProduct.ExecuteScalar()
End If

Row.SalesMarketingChargesByDate = SMChanges

ComponentMetaData.FireInformation(0, "", "", "", 0, True)

End Sub

End Class

the error was made from this control, it's not a matter of package path or the steps to call the package because I have another packages and they ran correctly.

Thanks,
Yousef Amarneh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources