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 2012 Package Won't Run as SQL Server Agent Job Expand / Collapse
Author
Message
Posted Thursday, June 6, 2013 5:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 10:39 AM
Points: 3, Visits: 42
Hello,

I'm new to the forum so sorry in advance for any etiquette I break :)

I have an SSIS 2012 package that has a OLE DB data source connecting to an Access Database. The package runs fine when i run it from SQL Server Data Tools. I then deploy it to an SSIS Catalog and I can run the package fine from there. Next I add it as the only step in an SQL Server Agent Job and I get 4 consistent error messages.

"failed validation and returned validation status "VS_ISBROKEN""
"One or more component failed validation"
"There were errors during task validation"
"An error occurred executing the provided SQL command: "select * from 'zqryExportTesting'". Object reference not set to an instance of an object.

I've tries everything i can find. I've set the package encryption to be EncryptSensativeWithPassword instead of the default EncryptSensativeWithUserID. I've tried the 32 vs 64 bit runtime (I'm using 32 in the scenarios where it works). I've set the SQL Server Agent services to log on as the same user I am currently logged on to the server as. I've set up a proxy with the same user I am logged on to the servers as. I'm pretty sure i've tried every combination i've researched and still got nothing. I'm sure there is a simple setting or trick I'm missing.

Thanks a TON in advance for any help you can provide.
-Nick
Post #1460927
Posted Wednesday, June 19, 2013 5:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 20, 2013 2:04 AM
Points: 222, Visits: 331
If I have understood you rightly, then when you call the SSIS Package in a job step. You have a tab there by the name "Execution Options" there you can mark a Tick against the check box "use 32 bit runtime" and give it a try.

Sriram

Post #1465068
Posted Monday, June 24, 2013 1:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 10:39 AM
Points: 3, Visits: 42
Thank you for your reply. I had tried the 32-bit runtime and I still had the issue.

I started by project over from scratch and slowly added one small piece at a time. I finally got the error when I entered the query from Access as the data source. It is a pretty complicated query with lots of joins and functions. I'm not sure what is causing the issue but when I just exported a table or a simple query I had no issues getting it to run with the SQL Server Agent. The strange thing is the complicated query works just fine as the source until i try and run it with the SQL Server Agent. Oh well, go figure. Thanks to anyone who took a look at problem and gave it some thought.

-Nick
Post #1466879
Posted Thursday, July 18, 2013 8:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 29, 2014 7:26 AM
Points: 18, Visits: 27
I had the same problem. Checking the 32-bit runtime box still sometimes didn't result in it actually "sticking." I found that I had to actually alter the job step to add the proper '/X86' syntax to the command. I actually tested this by checking the box and then scripting the job - still no 32-bit option set! I honestly think there is a bug in the 2012 GUI for setting up the job step, but in any case, try this:

Right-click the job, choose Script Job...check out the section for the SSIS job step. Look at the @command piece. You SHOULD see '/X86' after the server name and before the '/Par' (see below).

If you don't, you can copy the whole line, ADD the '/X86' and then modify the job...like this:

EXEC dbo.sp_update_jobstep
@job_name = N'MyJOBName',
@step_id = 1,
@command=N'/ISSERVER "\"\SSISDB\MyPackage.dtsx\"" /SERVER "\"MyServer\"" /X86 /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E'
GO



Post #1475065
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse