SSIS 2005 package works but the related SQL 2008 job fails

Question

SSIS 2005 package works but the related SQL 2008 job fails

mmurudker

SSC Enthusiast

Points: 136
More actions
March 20, 2012 at 7:31 am

#254990

I created a simple SSIS package in Microsoft Visual Studio 2005. I then deployed the package onto SQL Server 2008 so that we could execute it as a job. One step within the package is a ScriptTask that prepares to zip a .csv file (basically just sets some variables).
The SSIS package runs perfectly when we execute it from Visual Studio (no build issues, nothing). However, when we execute it as a job in SQL Server 2008, it fails. The error message is: The binary code for the script is not found. Please open the script in the designer by clicking Edit Script button and make sure it builds successfully.
This package is based another package which does the EXACT same thing. The ONLY difference is that this package which keeps failing as a job is on SQL 2008 while the other one which also runs as a job is on SQL 2005.
I've set DelayValidation = True in the package Properties, PrecompileScriptIntoBinaryCode = True in the ScriptTask Editor. I've tried enabling the '32-bit run time' property in the Job. NOTHING seems to fix this problem.
Deploying this package on SQL 2005 is not an option for me so i NEED to get it to work on SQL 2008 somehow. Has anyone else encountered this type of issue? Can it be resolved?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

What do you use to ZIP your csv file? Is it installed on the SQLServer 2008 machine?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

mmurudker SSC Enthusiast Points: 136 More actions · Answer 2

mmurudker

SSC Enthusiast

Points: 136

March 20, 2012 at 7:55 am

#1461632

Using 7-zip and it's installed on the same server

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

I guess you have this step failing due to SQLServer 2008 cannot find your ZIP providing DLL.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

mmurudker SSC Enthusiast Points: 136 More actions · Answer 4

But it doesn't seem to be failing at the point where the .csv actually gets zipped -it fails at the step just prior to that (where i set the variables to find the output file and rename it before it gets zipped)

mmurudker SSC Enthusiast Points: 136 More actions · Answer 5

And this is what i based the zip & email part of the package on: http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/64028/

I had changed the variables and paths appropriately which is why it works fine in the package yet it's at the ScriptTask where the job keeps failing

MWise SSCarpal Tunnel Points: 4074 More actions · Answer 6

Have you tried upgrading the package to 2008 and deploying that version to your 2008 SSIS server? All you need to do is open the package in BIDS 2008 and run through the upgrade wizard.

MWise

mmurudker SSC Enthusiast Points: 136 More actions · Answer 7

Finally!!! It worked! It worked! It worked! There was one silly thing that needed to be changed in my ScriptTask which was the apparent cause (and it ONLY got pointed out when i upgraded the solution to 2008 cos wasn't an issue in 2005):

Had to change from strDay = CStr(Day(Now)) to strDay = CStr(DateAndTime.Day(Now))

The error 2008 pointed out was 'day is a type and cannot be used as an expression'

Anyway, thanks a mill guys, really appreciate it