Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Package executes successfully in dev/test environment but fails in production Expand / Collapse
Author
Message
Posted Tuesday, September 25, 2012 3:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 05, 2013 2:50 PM
Points: 38, Visits: 235
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!
Post #1364325
Posted Tuesday, September 25, 2012 3:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, April 12, 2014 12:51 AM
Points: 5,986, Visits: 6,930
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.


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1364330
Posted Tuesday, September 25, 2012 5:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 05, 2013 2:50 PM
Points: 38, Visits: 235
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.
Post #1364373
Posted Tuesday, September 25, 2012 5:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, April 12, 2014 12:51 AM
Points: 5,986, Visits: 6,930
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.



- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1364377
Posted Wednesday, September 26, 2012 3:28 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:14 AM
Points: 5,844, Visits: 12,571
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...


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

Post #1364519
Posted Wednesday, September 26, 2012 3:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 05, 2013 2:50 PM
Points: 38, Visits: 235
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....
Post #1364962
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse