May 27, 2015 at 1:18 am
Hi
I have a new server and have move my packages from SSIS 2008 to SSIS 2014. Some of my packages are failing with the error "Setting the end of rowset for the buffer failed with error code 0xC0047020"
I have a table staging table with the columns all defined as varchar which I then transform via a script into the correct data types into the post staging table.
This all works 100% in on the old server and I've never had this problem before, however on the new server it will read the data and transform it correctly in the SSMS studio, but as soon as I deploy it to the server and I want to run it via the Job Agent, it fails with this error. The error already occurs when I read from the pre-staging table, it doesn't even get to the transform part, (well it seems like it)
I have other packages which I've updated and deployed and which runs 100% correctly in the job agent except for about 5 out of the 35 jobs.
Everything should work, but some how, it just doesn't .
(I will post the full error description a bit later as they are doing updates on the server at the moment)
Any suggestions?
May 27, 2015 at 1:46 am
Are there any other error messages appearing before or after this one?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 27, 2015 at 3:18 am
Hi Chris
Please update manually Rows per batch and Maximum insert commit size properties in OLE DB Destination after package conversion.
Best regards
Mike
May 27, 2015 at 5:09 am
Here is the "proper error description"
Date2015-05-27 12:25:17
LogJob History (MIS - 1 Import Daily Files)
Step ID6
ServerXXX
Job NameMIS - 1 Import Daily TVDP Files
Step NameLoad File 091
Duration00:00:58
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: loader. Microsoft (R) SQL Server Execute Package Utility Version 12.0.4100.1 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 12:25:18 PM Error: 2015-05-27 12:26:15.11 Code: 0xC0047062 Source: 52 Correct The Data Types File 091 522 Script Component [155] Description: System.FormatException: Input string was not in a correct format. 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: 2015-05-27 12:26:15.12 Code: 0xC0047022 Source: 52 Correct The Data Types File 091 SSIS.Pipeline Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "522 Script Component" (155) failed with error code 0x80131537 while processing input "Input 0" (165). 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: 12:25:18 PM Finished: 12:26:15 PM Elapsed: 57.125 seconds. The package execution failed. The step failed.
As mentioned, it works perfectly in SSMS but not in Job Agent, but the rest of the jobs with everything the same except different flat files, works 100% in JA.
May 27, 2015 at 5:20 am
michal.lisinski (5/27/2015)
Hi ChrisPlease update manually Rows per batch and Maximum insert commit size properties in OLE DB Destination after package conversion.
Best regards
Mike
Hi Mike, I just tried it and still get the same error. It's reading from the pre-staging table and somehow it already is get the error on the OLE DB Source rather than the Destination. :crazy:
May 27, 2015 at 6:25 am
Hi Chris
Your second post helped to clarify the problem, let me know you rebuilt again the Script Component after upgrade?
Regards
Mike
May 27, 2015 at 7:07 am
Hi
Yes, I did a rebuild of the scripts in this package as I had a few problems with some of the script taks in other packages. However the rebuild didn't work in this case.
June 3, 2015 at 5:56 am
Just a update, maybe someone will be able to help with this problem
I still get the following error from Job Agent :
Message
Executed as user: licloader. Microsoft (R) SQL Server Execute Package Utility Version 12.0.4100.1 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 01:21:20 PM Error: 2015-06-03 13:22:20.76 Code: 0xC0047062 Source: 52 Correct The Data Types File 083 522 Script Component [170] Description: System.FormatException: Input string was not in a correct format. 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: 2015-06-03 13:22:20.76 Code: 0xC0047022 Source: 52 Correct The Data Types File 083 SSIS.Pipeline Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "522 Script Component" (170) failed with error code 0x80131537 while processing input "Input 0" (180). 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: 01:21:20 PM Finished: 01:22:20 PM Elapsed: 60.546 seconds. The package execution failed. The step failed.
However running the SSIS package via the management studio I receive no errors
But I'v started blocking out my coding in the script in the data flow and have found that the error only comes along when I convert my data into Decimal
I read in a flat file into a pre staging table in SQL and then from that I want to convert the string value "0000000000.00" into a decimal value into the post staging table where the column is defined as numeric (11,2). However the coding (read M$) messes up and give the error on top.
As stated already, it worked 100% in SQL Server 2008 R2 and I've done all that's needed to convert the package to SQL Server 2014. All other packages where there is no decimal values works perfectly except for packages with them in.
I use the following : Row.AMOUNTINVOICEOut = Convert.ToDecimal(Row.AMOUNTINVOICEIn)
Where the AMOUNTINVOICEIn is a DT_STR with codepage 1252 with length of 13
and AMOUNTINVOICEOut is DT_Numeric Precision 11 and scale 2.
Any ideas?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply