none
SSIS Package Runs In Development Studio But Not As A Job RRS feed

  • Question

  • 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: SIROCCO\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:30 PM  Error: 2011-08-16 21:31:57.27     Code: 0xC0047062     Source: Load FactSales Get Sirocco Sales & Marketing Charges [18033]     Description: System.Data.SqlClient.SqlException: Conversion failed when converting date and/or time from character string.     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-08-16 21:31:57.36     Code: 0xC0047022     Source: Load FactSales SSIS.Pipeline     Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Get Sirocco 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-08-16 21:31:58.05     Code: 0xC0209017     Source: Load FactSales MMI Data Slice From Staging [1]     Description: Setting the end of rowset for the buffer failed with error code 0xC0047020.  End Error  Error: 2011-08-16 21:31:58.05     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 0xC0209017.  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: 2011-08-16 21:31:58.08     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  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  8:00:30 PM  Finished: 9:32:14 PM  Elapsed:  5504.48 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 ???

    Wednesday, August 17, 2011 10:03 AM

All replies

  • The Error is : Conversion failed when converting date and/or time from character string.

    Which means that the string that you were trying to convert was of the form dd/MM/yyyy but the default for all users is MM/dd/yyyy 

    so create a proxy account for SSIS execution and set the users Date Time format from regional settings to that particular Format 

    http://www.codeproject.com/KB/database/SQL_Server_Agent_Proxy.aspx



    --------------------------------------------------------

    Surender Singh Bhadauria

     


    Wednesday, August 17, 2011 10:19 AM
  • can you provide me with more details about proxy account creation?

     

    Thanks,

    Yousef Amarneh


    Wednesday, August 17, 2011 10:29 AM
  • Surender's solution is assuming a LOT that hasn't been confirmed yet.  So much so I'm certain his solution won't work.

    First - please describe your package.  I see the error is produced inside a Script that's attempting to insert into SQL Server.  Why a script?

    Second - where are you getting this data from?  A flat file?  What format is the date in?


    Todd McDermid's Blog Talk to me now on
    Wednesday, August 17, 2011 4:11 PM
    Moderator
  • I am agree with others, you should tell us more about  your package,

    what did you there? what source? and what transformations? and could you provide sample input rows?

    did you connect to same data source on development and production? did you used same data as source or data changed?


    http://www.rad.pasfu.com
    Thursday, August 18, 2011 5:42 AM
    Moderator
  •  


    Thank you guys for your replying,

     

    I am getting the data from SQL server table and the data format is DD/MM/YYYY and I am using the following script

     

    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 

     


    thanks, Yousef Amarneh
    Thursday, August 18, 2011 8:32 AM
  • Hi Yousef,

    Strange that you use this in script task and not a OLEDB Source with a sql command from variable as data access mode.

    What can help is to implement a messagebox that shows you the actual query that will be sent to the SQL DB. If you could post that one?

    Thursday, August 18, 2011 8:44 AM
  • I got the following message 
    ---------------------------
    SC_5ecac85b6c2645c3b22da5ecb1325f2f.vbproj
    ---------------------------
    SET DATEFORMAT DMY SELECT TOP 1 [Sales & Marketing Charges] FROM [Price Master] WHERE [Product Code] = '248' AND Distributor = 'MMI On-Trade' AND [DATE] < cast('15/08/2011' as date) ORDER BY [DATE] DESC
    ---------------------------
    OK   
    ---------------------------

    thanks, Yousef Amarneh
    Thursday, August 18, 2011 8:59 AM
  • Try to pass your Date in the YYYY/MM/dd format and change the where to [DATE] < 'yyyy/MM/dd' without the cast.

    Is there a reason why you didn't use the OLEDB source with the sql command from variable?


    Thursday, August 18, 2011 9:07 AM
  • Actually I didn't implement this package, I am just trying to fix that problem, and  I don't know if there is any reason why he didn't use the OLEDB source with SQL command from variable, and I am not expert in SSIS development, so can you please give me more details about it?
    thanks, Yousef Amarneh
    Thursday, August 18, 2011 9:20 AM
  • I believe I drawed my conclusions to quicly.

    In fact your query looks fine as well. Could you put your code in a try - catch and put the messagebox in the catch. Or does it fails on any record? And what happens if you execute the statement in the SQL server itself?

     

    Thursday, August 18, 2011 9:43 AM
  •  

    I ran it in the SQL and it worked correctly, and when I set try and catch in the code and deploy the package again the job failed and this is the error :

    Executed as user: SIROCCO\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:  1:54:13 PM  Error: 2011-08-18 13:57:43.96     Code: 0xC0047062     Source: Load FactSales Get Sirocco Sales & Marketing Charges [18033]     Description: System.InvalidOperationException: Showing a modal dialog box or form when the application is not running in UserInteractive mode is not a valid operation. Specify the ServiceNotification or DefaultDesktopOnly style to display a notification from a service application.     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-08-18 13:57:44.23     Code: 0xC0047022     Source: Load FactSales SSIS.Pipeline     Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Get Sirocco Sales & Marketing Charges" (18033) failed with error code 0x80131509 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-08-18 13:57:44.59     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  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  1:54:13 PM  Finished: 1:57:50 PM  Elapsed:  217.532 seconds.  The package execution failed.  The step failed.


    thanks, Yousef Amarneh
    Thursday, August 18, 2011 10:36 AM
  • The try - catch + messagebox is for debugging, a messagebox @ runtime will result in an error. but while debugging, you don't have any error? Even with the same data?
    Thursday, August 18, 2011 11:13 AM
  • yes I don't have any error while debugging even with the same data


    thanks, Yousef Amarneh
    Thursday, August 18, 2011 11:17 AM
  • Remove the message box from the catch block because when a package is running from service it cannot render the message box and throws the exception

    --------------------------------------------------------

    Surender Singh Bhadauria

     

    Thursday, August 18, 2011 12:13 PM
  • The person that created this package didn't know SSIS either.  What they did is like using a hammer to fasten a screw.  It does not one, but probably TWO round-trip calls to your SQL Server for each row.  It uses non-standard date formats.  It (almost surely) uses a hardcoded connection, not a managed connection (although that's an assumption).

    I do see a problem here though.  The code does inform SQL Server that a DMY format date is what it should be trying to interpret... but your code doesn't guarantee that a DMY date will be delivered to SQL Server.  Are you sure that Row.FullDateAlternateKey has a DMY formatted date in it?  The only way to be sure is to instrument that part of the code.

    To instrument script components in production you need to do two things:

    1. Turn on Package Logging (if you haven't already), and make sure that Information events are captured for (at least) the Data Flow this component is in.
    2. Add ComponentMetaData.FireInformation calls to your script (instead of MessageBox.Show calls!)

    Now when your script runs in BIDS, you'll be able to see messages in your Output window.  When you run it on the server, the messages will be captured in your package logging.

    ===================================

    On to the subject of "doing this right" and using a screwdriver to fasten the screw, instead of a hammer.

    This process should be done using a Lookup component and a Derived Column that follows the lookup.  That's it.  Done.


    Todd McDermid's Blog Talk to me now on
    • Proposed as answer by Eileen Zhao Monday, August 22, 2011 10:09 AM
    • Marked as answer by Eileen Zhao Friday, August 26, 2011 7:45 AM
    • Unmarked as answer by Yousef Amarneh Wednesday, September 14, 2011 7:14 AM
    Thursday, August 18, 2011 4:51 PM
    Moderator