2007-07-18 14:27:52,098 [1] ERROR reporting.Processor [(null)] - Problem with DTS Script
2007-07-18 14:27:52,895 [1] ERROR reporting.Processor [(null)] - {Microsoft.SqlServer.Dts.Runtime.DtsError, Microsoft.SqlServer.Dts.Runtime.DtsError}
The SSIS runs fine from the process on my machine, from SQL Server on my machine and from SQL Server on the server. But when I run it from the C# executable I get the above errors. This process also runs other SSIS scripts and they all work fine. I am using the following code to execute the script. Can anybody give me some ideas on how to troubleshoot this problem.
Package package = app.LoadFromSqlServer("\\Maintenance Plans\\SCRA2", "ppntt240", "load_abc", "bcp123", null);
DTSExecResult result = package.Execute();
Variables vars = package.Variables;
int rowcount = Convert.ToInt32(vars["count"].Value);
String rowcount2 = "0000000000" + rowcount.ToString();
string fullcnt = rowcount2.ToString().Substring(rowcount2.Length - 10, 10);
if (result == DTSExecResult.Failure)
{
Console.WriteLine("Task failed or abended");
log.Error("Problem with DTS Script");
log.Error(package.Errors);
}
else
{
Console.WriteLine("Task ran successfully");
Can you enable logging in the package so that you can get more detail about the error?|||How do I do this?|||Right-click on the package in the designer, choose Logging, add a log provider (Text is simple to set up, but database is good too), and check the checkbox beside the package name. In the details tab, make sure you check OnError and OnWarning,|||Logging is not an option if I right click. If I select run I see logging and it ask for log provider e.g.(text file, event log or SQL Server) and Configuration String. Is this where I turn it on? What are the best options? Thanks.
|||If you open the package in BIDS, go to the control flow for the package, and right-click in an empty area (one not occupied by a task), you should see the logging option.|||Is there anything unique to the server that would cause the following errors?
PackageStart,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:21 PM,7/19/2007 12:35:21 PM,0,0x,Beginning of package execution.
OnError,PPNTT240,PNCNT\FF22882,Data Flow Task Direct_Prod,{F36A5050-8FC5-4A2F-A457-42AB134844CF},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".
OnError,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".
OnError,PPNTT240,PNCNT\FF22882,Data Flow Task Direct_Prod,{F36A5050-8FC5-4A2F-A457-42AB134844CF},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1073450982,0x,component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC0202009.
OnError,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1073450982,0x,component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC0202009.
OnTaskFailed,PPNTT240,PNCNT\FF22882,Data Flow Task Direct_Prod,{F36A5050-8FC5-4A2F-A457-42AB134844CF},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,0,0x,(null)
OnWarning,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-2147381246,0x,The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
PackageEnd,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,1,0x,End of package execution.
|||
agentf1 wrote:
Is there anything unique to the server that would cause the following errors? OnError,PPNTT240,PNCNT\FF22882,Data Flow Task Direct_Prod,{F36A5050-8FC5-4A2F-A457-42AB134844CF},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".
OnError,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".
Looks like you have problem with you source data. Since you get out-of-range problem, my bet is that the source date format is messed up. Probably server is expecting mm/dd/yyyy and your source date is dd/mm/yyyy
Thanks.
|||Data is fine. Script runs on server outside of C#.|||Are you absolutely positive that the package is connecting to the same data sources when you run it from C#? I have a hard time believing that an error message on a data conversion error isn't caused by the data
|||As John said, Check for the connections in the data flow task (Direct_Prod) when you run from the C# and in Server. Thanks|||It is the same. It was running fine and then one day just started bombing. It runs from the server ok and runs from the C# ok when it is on my desktop. Strangest thing I have ever seen.
I recently added the logging so I am sure I have the same version.
|||I figured out my problem. The date comes in like this in most cases 07/09/2007 but comes in like this when run via C# on the server 7/9/2007. I am doing this when I build my query in the string + HolidayEndDt.ToString.Substring(0, 10) +
It is also apparently giving me problems in another spot where I compare month to see if it is EOM and it fails because it is comparing 07 to 7/ since I am using substring for that as well.
What do most people do in these instances? How do you build a query in a string that contains a date? Thanks.
|||
HolidayEndDt.ToString.Substring(0, 10)
What do most people do in these instances? How do you build a query in a string that contains a date? Thanks.
Assuming you HolidayEndDt is datetime type,
HolidayEndDt.ToString("yyyyMMdd") should solve your problem, and as far as i know thats one of the best way to deal with dates.
Thanks
|||Yes, that is what I did. Thanks.
Actually I did HolidayEndDt.ToString("MM/dd/yyyy") but I guess there is 6 in one and half dozen in the other. More or less the same thing.
No comments:
Post a Comment