June 6, 2013 at 5:04 pm
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
June 19, 2013 at 5:02 am
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
June 24, 2013 at 1:30 pm
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
July 18, 2013 at 8:42 am
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
July 14, 2015 at 5:16 am
Janine Bocciardi (7/18/2013)
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
Hey man thanks for the hint, it was just what i was looking for!
I was running into the same issue as OP and your solution just solved my problem.
And btw, your answer must be the only answer in all internet to address this issue lol
Thanks 😀
July 14, 2015 at 10:08 am
You're welcome! (just don't call me "man" - lol)
Cheers,
Janine
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply