September 25, 2012 at 3:45 pm
Hi,
I have an ssis package that runs perfectly fine in test/dev environment but when deployed to production it fails. The object that fails is the Execute SQL Query Task which executes the script below:
SELECT CONVERT(NVARCHAR(20),GETDATE(),20),
CASE
WHEN DAY('20120924') > = 6 then CONVERT(VARCHAR(10),DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,'20120924')+1, 0)),101)
WHEN DAY('20120924') < 6 then CONVERT(VARCHAR(10),DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,'20120924'), 0)), 101)
END AS NewDate
The '20120924' is the filedate that is extracted from a filename.
The error is below:
Description: Executing the query "SELECT CONVERT(VARCHAR(20),GETDATE(),20), CASE W..." failed with the following error: "Conversion failed when converting date and/or time from character string.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:29:14 PM Finished: 4:29:16 PM Elapsed: 1.797 seconds. The package execution failed. The step failed.
The script above works fine when executed manually in SSMS in both test/dev and production servers. It also works fine when run in the SSIS Package as a whole (end-to-end) but when it is run in the production server it fails.
I don't have any access to the production so I am at lost on what may be the cause of the failure. Appreciate any help.
Thanks!
September 25, 2012 at 3:55 pm
Get your DBA to look at the filenames in the production pathing. Most likely one of them has an invalid naming convention that you're picking up and trying to convert. You won't be able to directly troubleshoot this without access to the job logs and the data informing the package, you're going to need support from above.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 25, 2012 at 5:38 pm
Thanks!
I have emailed the DBAs already and they are still looking for it but based on their initial response, the files in there have the correct naming convention. They sent me the file as well and I tested it using dev and they all work fine.
When they run it in prod everything fails again - even the one file they are trying to load.
While waiting for their findings, I was just thinking - could there possibly be a problem with the integration services being of different version perhaps between the two servers that might be causing the problem? Something like a problem in the framework or some kind of that sort - I'm pretty much not familiar with it so I was just guessing.
Anyway, you are right - it is really difficult to troubleshoot without the actual look on the data passing in and out in the package especially when everything else works fine in dev.
September 25, 2012 at 5:56 pm
Ask them to stick the package in a job, setup a step log for the SSIS package, run the package (and fail) and ship you the file.
There's a ton more information in that file than you can get from the job errors or whatever is popping on screen, and you're going to need that to help them troubleshoot this 'over the phone' as it were. You might be looping extra files that have similar naming patterns that weren't supposed to be picked up. It might be an invalid value.
The only version differences I could see would be a difference between SP1 and 2, but I can't see date conversion being a serious issue between them, but it certainly couldn't hurt to check. You need the full error list though. Start with the log file, work from there. You may not be seeing the entire error chain *(since the job results truncate the error list) and the real error is elsewhere.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 26, 2012 at 3:28 am
The error 'Conversion failed when converting date and/or time from character string.". ' is usually associated with language settings. Is the default language for the server\login\connection different between prod and test?
Just a thought...
---------------------------------------------------------------------
September 26, 2012 at 3:49 pm
Hi!
So I talked to the DBAs and for some reason they just couldn't run the package on design mode to have a detail look on what data is going in and out in the package. To move away from that SQL script, I created a variable with an expression:
EXPRESSION1:
(DT_DATE)(SUBSTRING(SUBSTRING(@[User::sFileName],FINDSTRING(@[User::sFileName],"_",1)+1,8), 1,4) + "-" + SUBSTRING(SUBSTRING(@[User::sFileName],FINDSTRING(@[User::sFileName],"_",1)+1,8), 5,2) + "-" + RIGHT(SUBSTRING(@[User::sFileName],FINDSTRING(@[User::sFileName],"_",1)+1,8),2))
That is to get the date on the filename and converting it to date data type. This is in turn being referenced by another variable to finally get the desired date based from the date above:
EXPRESSION2:
DAY(@[User::dFileDate]) >= 6 ? DATEADD("D", -DAY(@[User::dFileDate]), DATEADD( "MM", 1, @[User::dFileDate] ) ) : DATEADD( "DD", -DAY( @[User::dFileDate] ), @[User::dFileDate] )
They were tested in dev and works just fine but in prod, the EXPRESSION1 above fails with the error:
Description: Casting expression "(SUBSTRING(SUBSTRING(@[User::sFileName],FINDSTRING(@[User::sFileName],"_",1) + 1,8),1,4) + "-" + SUBSTRING(SUBSTRING(@[User::sFileName],FINDSTRING(@[User::sFileName],"_",1) + 1,8),5,2) + "-" + RIGHT(SUBSTRING(@[User::sFileName],FINDSTRING(@[User::sFileName],"_",1) + 1,8),2))" from data type "DT_WSTR" to data type "DT_DATE" failed with error code 0xC00470C2. End Error Error: 2012-09-26 16:07:46.87 Code: 0xC0019004 Source: PackageName Description: The expression for variable "dFileDate" failed evaluation. There was an error in the expression. End Error
My dev and prod now have the same data being processed so I don't know what's causing the error this time knowing that they are both processing the same data. What's more weird is that in prod, the package seems to complete its run with all the data being loaded from that file to the table but fires the error above.
Am I missing something here?
I'm really thinking now that it could that language difference....
April 22, 2016 at 1:19 am
Hi, have you found any solution?
I am having same problem
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply