July 11, 2014 at 12:32 pm
I have an execute process task in SSIS that executes a .Net program.
When this is running as a Sql Server Agent job in Sql Server 2005, I have no issues. The operating system of the server is Windows Server 2003 R2.
Now I'm trying to get this working as a Sql Server Agent job in Sql Server 2012. The operating system of the server is Windows Server 2012 R2. And now I have issues.
If I run the SSIS package in Visual Studio debug mode, I have no issues! It's only when I try running the same package from a Sql Server Agent job that there are problems. The .Net program errors out with a System.TypeInitializationException.
I thought this might be an issue running the .Net program as 64 bit so I changed the agent setting to 32 bit. That didn't help. I thought it might be an issue with authorization (since I can run it in debug with me as the user). I put in a class in the program that changes the user through impersonation. That didn't work either.
Since I don't have a full-blown version of Visual Studio on the database server, I haven't been able to capture the error and debug the program to see where it is blowing up.
Does anyone have any idea why something like this would work in Sql Server 2005/Windows 2003 but not in Sql Server 2012/Windows 2012 when run as an agent job? And on top of that, why would it work when I run it manually in debug mode but not when run as an agent job?
I'm at a loss.
July 14, 2014 at 11:46 am
Does the SSIS Package is developen on BIDS 2005
If yes, then upgrade/migrate that particular SSIS package by opening BIDS 2008/ SSDT 2012...
If no, try to opn in BIDS 2008/ SSDT and execute manually ( in debug mode with post execute and pre execute for that particula Process task)
Thanks,
SP
July 14, 2014 at 11:56 am
Yes, the package was developed in SSIS 2005. Yes, the package that executes the process task was updated to SSIS 2012 through the wizard. Yes, I have no issues running the package in debug mode connecting to Sql Server 2012, running both 64 bit and 32 bit debug mode.
It is only when I run the package through Sql Server Agent in 2012 that I have issues. I still can't get to the bottom of this and it is driving me crazy.
Oh, and if I haven't mentioned this already, when this runs as a Sql Server Agent job from Sql Server 2005, there are no issues, even when running as an agent job.
July 14, 2014 at 12:43 pm
Check the account under which the job is running and check its permissions. I'd had cases when it had been an issue.
Igor Micev,My blog: www.igormicev.com
July 14, 2014 at 12:51 pm
The .Net program errors out with a System.TypeInitializationException
Does anyone have any idea why something like this would work in Sql Server 2005/Windows 2003 but not in Sql Server 2012/Windows 2012 when run as an agent job?
There are multiple things that can cause a .NET application to not work on a new operating system. One being the .NET framework version, if it used 2.0 is 2.0 framework installed on the new server? Does it reference DLL files that are not properly deployed to the new server or are not compatible with 2012 OS?
How was the program setup on the 2003 server?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
July 14, 2014 at 1:16 pm
Good suggestions, Shawn, but the program does work. It works when I run the package in debug and it works when I execute the package under DTSEXEC. It only does not work when I run it as a Sql Server Agent job in 2012 (it works when run as a Sql Server Agent job in 2005).
I'm looking into permissions, as suggested by Ivan, although sql server agent permissions and proxies are not my strong suit.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply