November 8, 2012 at 3:25 pm
I've inherited a poorly documented and configured environment and am trying to upload a SSIS package to be tested. Our dev and test databases are located on the same server, using [servername]\DEV and [servername]\TEST. I've been assured that the packages that have been developed in the past are located on this server.
When I try to connect to SSIS using [servername], I'm presented with a list of folders under "Stored Packages". The only one which I'm able to expand is MSDB, and this contains a list of what appear to be Maintenance Plan-created packages, i.e., they don't look like anything that we've created here. Additionally, there's a folder called "DEV" and a folder called "TEST", but when I try to expand either of these, I'm met with:
"Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur... [yadda, yadda]
Login failed for user [domain][myuser]. (MsDtsSrvr)"
Now, a couple of things before you start telling me to check out MsDtsSrvr.ini:
I've confirmed that I can connect to [servername]\DEV and [servername]\TEST. In fact, I'm sysadmin on both.
When I looked at MsDtsSrvr.ini initially, the ServerName node was set to .\DEV and .\TEST for both of those folders. After changing them to [servername]\DEV and [servername]\TEST and restarting the SSIS service, there was no change in the behavior.
Halp!
November 8, 2012 at 4:05 pm
have you checked the DCOM Component permissions?
November 8, 2012 at 4:08 pm
No. How or where do I do that?
November 9, 2012 at 12:01 am
Connect to a Remote Integration Services Server (SSIS Service)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 9, 2012 at 3:45 am
You mentioned that both DEV and TEST are on the same server. How are you connecting to [servername], locally or remotely? The following comments assume that you are connecting remotely. I had a similar experience 2 days ago connecting from ssms on my laptop to a remote server. For testing purposes, I shared the packages folder (locate the physical folders wherever they are on the server) on the server and made sure my domain account had access to the share. I then created and entry for the server in the Config file on my laptop above the /TopLevelFolder closing tag as follows:
<Folder xsi:type="FileSystemFolder">
<Name>AnyName</Name>
<Servername>servername</Servername>
<StorePath>\\servername\packages</StorePath>
</Folder>
Then restarted the SSIS service on my laptop and reconnected. I'm no expert but this worked for me, then I began isolating issues which eventuallt led me to the root cause.
November 9, 2012 at 7:28 am
I'm connecting locally.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply