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


SSIS 2012 Package Won't Run as SQL Server Agent Job


SSIS 2012 Package Won't Run as SQL Server Agent Job

Author
Message
nparadiso
nparadiso
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 48
Hello,

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

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
Sriram-288748
Sriram-288748
Old Hand
Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)

Group: General Forum Members
Points: 356 Visits: 340
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
nparadiso
nparadiso
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 48
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
Janine Bocciardi
Janine Bocciardi
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 30
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



ricardodias_17
ricardodias_17
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 13
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 :-D
Janine Bocciardi
Janine Bocciardi
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 30
You're welcome! (just don't call me "man" - lol)

Cheers,
Janine



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