SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Package executes successfully in dev/test environment but fails in production


SSIS Package executes successfully in dev/test environment but fails in production

Author
Message
quasar_phoenicis
quasar_phoenicis
Mr or Mrs. 500
Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)

Group: General Forum Members
Points: 550 Visits: 258
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!
Evil Kraig F
Evil Kraig F
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34443 Visits: 7660
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
quasar_phoenicis
quasar_phoenicis
Mr or Mrs. 500
Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)

Group: General Forum Members
Points: 550 Visits: 258
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.
Evil Kraig F
Evil Kraig F
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34443 Visits: 7660
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
george sibbald
george sibbald
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39124 Visits: 13702
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...

---------------------------------------------------------------------
quasar_phoenicis
quasar_phoenicis
Mr or Mrs. 500
Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)

Group: General Forum Members
Points: 550 Visits: 258
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....
celia.moreno
celia.moreno
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 71
Hi, have you found any solution?
I am having same problem
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search