Login failed for user xxxxxxxxx. (MsDtsSrvr)

The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer.  For more information, see the topic "Configuring the Integration Services Service" in SQL Server 2012 Books online

The developer today was trying to create a new folder in MSDB under the 'Stored Packages' in Integration services. However, he was getting the following error.

On this server we already had a named instance and Integration Services installed. I had recently created another instance for this developer to use and it looked like when he was trying to create the folder in the MSDB folder he was hitting the wrong database instance.

After some Google searches, I found some support pages that were related to this error.
https://msdn.microsoft.com/en-us/library/ms137789.aspx
https://indepthsql.wordpress.com/2012/03/15/ssis-error-the-sql-server-instance-specified-in-ssis-service-configuration-is-not-present-or-is-not-available-this-might-occur-when-there-is-no-default-instance-of-sql-server-on-the-computer-for-m/

The first step was to find this file called MsDtsSrvr.ini.xml. So I right clicked on Integration Services in SQL Server Configuration Manager and looked at the 'Service' tab. There you will find the 'Binary Path' for the Integration Services. You should be able to find that file under that directory. If not just do a file search.

It is typically located in %ProgramFiles%\Microsoft SQL Server\xxx\DTS\Binn. But depending on where you install the binaries this path will change. If you have a named instance then it will be different.

Edit this file in notepad and look for the following lines.

<Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>.\NamedInstance</ServerName>
    </Folder>

The item highlighted in Red will be the Database Instance under whose MSDB Database the package is being stored. I found that the packages were being stored in the first named instance that was created on this server. Next I checked the SQL Server error logs and I saw login failed messages related to this developer in the older instance. This confirmed the problem.

There are two solutions to this problem.
1) Edit this file and enter the name of the instance to which this user already has access to.     
     This would be the new instance that was created.
2) Grant this user full permissions on the MSDB database in the older named instance. 

There are pros and cons for both. I decided to edit this file and point to the new instance since no one was using the Integration services and the old instance together. I guess the previous DBA installed the Integration Service when it was not going to be used for long.

Data source name not found and no default driver specified at System.Data.Odbc.OdbcConnection.HandleError

Started: 1:03:12 PM Progress: 2012-02-08 13:03:15.24 Source: Data Flow Task Validating: 0% complete End Progress Error: 2012-02-08 13:03:16.15 Code: 0xC0047062 Source: Data Flow Task V91MOD APIMAG [1] Description: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)

DSN data sources does not appear in the DSN drop down menu in connection manager.

Recently while creating a SSIS package I had to connect to IBM iSeries AS400 servers and I had asked someone to install the ODBC drivers. So they had installed the IBM iSeries Access for Windows and created a SYSTEM DSN for me to use. I was using the connection string like the following.

DSN=mySystemDSN; user=myuser; PWD=mypass;

But I was getting the above mentioned error. After researching for some time I found that BIDS is a 32 bit application and installed version of SQL Server was 64 bit application and hence the issue. I could not even run the package in debug mode. The recommendation was to change a property in 'Solution's properties'. Change the property called 'Run64BitRunTime' to FALSE.

 

Also you need to enable the 32 bit runtime in tab 'Execution options' in the SQL Server Job step that will run the SSIS package.

 

But in spite of all this I still had issues running the package in both the debug mode and the SQL Server job step.

One more issue that I found was you could not pick the DSN in the drop down menu of the connection manager. If you pick the option ADO.NET provider (.Net Providers\ODBC data provider) then you won't find the ODBC DSN data source that you created.

The trick is to create both the 32 bit and the 64 bit DSN using the ODBC administrator. For this go to Program menu and then IBM iSeries Access for Windows and create the system DSN under both 'ODBC Administrator' and the 'ODBC Administrator 64 bit'.

This way while editing the package you will find the DSN in the connection manger. This will be the 32 bit DSN and when you run the package it will also find the 64 DSN if required.

Could not open global shared memory to communicate with performance DLL

SQL Server SSIS :

[SSIS.Pipeline] Warning: Could not open global shared memory to communicate with

performance DLL; data flow performance counters are not available. To resolve,

run this package as an administrator, or on the system's console.

"SQL Server:SSIS Pipeline 10.0" performance object does not appear in the "Performance object" list while another user accesses some SSIS pipeline counters in SQL Server 2008, in SQL Server 2008 R2 or in SQL Server 2005  

The Package works fine when executed as 'Administrator' but fails for every other user. The SQL Server Agent service is running under a different user which is also administrator but the job running the package still fails.

 
There is a Microsoft support page that discusses this issue.

http://support.microsoft.com/kb/2496375

But based on my investigation I found that the workaround for this issue is to use OLEDB Destination instead of SQLDB Data destination. Not sure what is the practical difference in using one versus the other. But for most situations either of them work.

  

You might be able to run the package in debug mode and have a success. But it will fail if you run it on the command line via DTEXEC or run as a SQL Server job step. 

So the workaround will work fine.