SSIS – MSSQLTREK

SSIS Server Maintenance Job – Execute as Login failed for the requested login ‘##MS_SSISServerCleanupJobLogin##’

Couple of weeks ago I was analyzing a server for space and noticed SSISDB database was abnormally huge (this Instance was running just a handful of packages). I noticed couple of internal schema tables in SSISDB were huge (with some hundreds of millions of rows), well that’s not right. There should be SSIS Server maintenance job which SQL server creates to purge older entries based on the retention settings right? My immediate action was to check the retention period set and what’s the status of the job. As I suspected, the job was failing (looks like this has been failing since ages) with below error.

The job failed. The Job was invoked by Schedule 9 (SSISDB Scheduler). The last step to run was step 1 (SSIS Server Operation Records Maintenance).
Execute as Login failed for the requested login ‘##MS_SSISServerCleanupJobLogin##’

Next step is to investigate whats up with the user account which the job is complaining about and I noticed ‘##MS_SSISServerCleanupJobUser##’ is orphaned in SSIS database as shown below.

Compare with how it looks on an Instance where this Job is running successfully.

Well, with this the fix is pretty evident, Map the poor fella to the corresponding login (Syntax shown below).

Once this has been done, I kicked off the job and it ran successfully (It took almost 2 hours to finish – had to purge few millions of records) and the subsequent scheduled runs were quick (Just few seconds as expected)

Cheers!

Could not load package “\File System\Test” because of error 0x80070002.

Well, today I had a very funny/annoying/frustrating Issue where I was getting the below Error message when I tried to Run a Package from SSMS connecting to SSIS.

TITLE: SSIS Execution Properties
——————————
Could not load package “\File System\Test” because of error 0x80070002.
Description: Could not find file ‘C:\Program Files\Microsoft SQL Server\110\DTS\Binn\..\Packages\Test.dtsx’.
Source: mscorlib

Basically, I got that SSIS Package from a developer to Deploy on the Server and we do File System Deployments here in my Company. Before deploying the package onto Server and scheduling via Agent, I was asked to manually Run the Package and test it. Well, I was successfully able to run that Package Just by Double Clicking and invoking DTExecUtil.exe. Everything is fine upto this Point. When I tried to Import the Package onto SSIS Server and run that package I was getting this Annoying and frustrating Errors…

Let me recreate the issue and show you what I’m trying to explain…

Step 1: I’ve a Package called “Test” in my Default Package Store as you can see below.

Step 2: I select Run package from SSMS as you can see below.

Step 3: Now, I click on Execute Button. and I get the Error Messages which I’m talking about!

Step 4: Now, Let me double click on the SSIS Package from the NTFS Folder path. I’ll get the same Execute Package Utility where I have an option to execute the package, but this time I’m able to Run the Package without any issues. So what’s really happening here???

Initially I thought it might be because of Permission Issues but that’s not the case. I changed the Package Store Location and Restarted SSIS Service, but it didn’t helped me either..What’s happening really?? I was really frustrated at this point and Of Course Hot Coffee made my mind to work Sharp 😀

If you observe the below Screenshot very carefully, there is a space between the file name and .dtsx!!!!

As you can see It’s “Test .dtsx” not “Test.dtsx“…ha ha! Yup…This was the Issue. Once I removed that Extra space between File name and Extension SSMS and SSIS both were happy as shown below!!

So the Problem was that single stupid Extra Space and I was like “Reallllyyyyyyyyyyyy??” It caused me 20 Minutes of my time….Sounds really Stupid right….?? But trust me folks, it was one crazy and Funny Error.

Bottom Line : SSIS doesn’t like Spaces 😉

Hope this helps when you get into any similar Issue 🙂 Cheers!

Connecting to the Integration Services service on the computer “mirrorstand” failed with the following error: “The RPC server is unavailable. “.

Couple of days ago we’ve seen how to fix DCOM permissions issue while connecting to SSIS remotely. See here.

In this Blog Post let’s see one more annoying error(RPC Server unavailable) we see developers complaining about, while connecting to SSIS 2008 Server from there client machines.

Connecting to the Integration Services service on the computer “mirrorstand” failed with the following error: “The RPC server is unavailable.
“.This error occurs when the computer host name does not exist, or cannot be reached.

I’ve SSIS Service running on “mirrorstand” machine and I’m trying to connect from another machine “node2”. Please note, I’ve not configured Firewall yet on my “Mirrorstand” machine(Windows Server 2008).

How to Fix this Error?
All we’ve to do is add couple of exceptions in our Firewall, basically to allow “MsDtsSrvr.exe” Program and SSIS Port(TCP 135). Let’s see how to do that in action.

Step 1: Logon to your SSIS Server and Open Firewall with Advanced Services and create a new Inbound Rule for TCP Port 135.

Step 2: Click on New Rule and select Port as shown below.

Step 3: Select TCP and enter 135 as port number as shown below.

Step 4:

Step 5:

Step 6:

Now, The most important part comes here. We’ve to add MsDtsSrvr.exe as an exception. Create a new Rule and follow the same screenshots, but You’ve to choose Program instead of Port and browse to the executable.

Typically, you should be able to locate this executable in “C:\Program Files\Microsoft SQL Server\100\DTS\Binn” Folder as shown below.

And, my firewall Inbound Rule looks like below…

Once I added the Port and Program to my Inbound Rules on my SSIS Server, I’m able to successfully connect to SSIS(mirrorstand) from “Node2” machine as you can see below… 🙂

Hope this info helps when you get into this situation…Cheers!

Connecting to the Integration Services service on the computer “mirrorstand” failed with the following error: “Access is denied

In this Blog post let’s see how to fix DCOM Permissions issue for an user who receives below error when trying to connect to “SSIS” Remotely from a remote machine.

The Error Message you’ll receive when you are not granted required DCOM permissions is..

Connecting to the Integration Services service on the computer “mirrorstand” failed with the following error: “Access is denied.
“.

This error occurs when the computer has not been configured to allow remote connections through DCOM, or the user does not have permission to access the SQL Server Integration Services service through DCOM.

Note: The SSIS Server which I’m trying to connect is “MirrorStand”. The login which I’m trying to connect is ‘SREE\HR1″

So, how to fix this Error?

Solution:

Step 1: Log on to SSIS Server and Go to Component Services in Administrative Tools as shown below.

Step2: Navigate to DCOM Config ->MsDtsServer100 as shown below.

Step 3: Right Click on MsDtsServer100 and go to Properties and navigate to “Security” tab as shown below.

Step 4: Click “EDIT” beside all the three Options(Launch and Activation Permissions, Access Permissions and Configuration Permissions) shown in the above screenshot…

Once you click on ‘Edit’ button you will get the below screen where you’ve to add the required login as shown below.

Once adding in DCOM – component services is completed, then navigate to “Users and Groups” in computer Management and add the login in “Distributed Com Users” Windows Group as shown below.

Once you are done with adding the above shown DCOM permissions, you have to Restart SSIS Service. Then the login(user) should be able to connect to SSIS remotely.

As you can see below in the screenshot, I’m able to connect to SSIS Remotely (from a different machine, “Node1” in my case ) as ‘Hr1’ Login.