So we have a SSIS Package that extracts from our VM and accesses our Azure SQL Database via a Linked Server for some of its data and then creates a .csv file that we then output via WinSCP.
When we tried to Productionize this Package and using a Service Account and SQL Server Authentication, when the SSIS Package gets to the Data Flow Task it just spins and spins and spins trying to run the SQL Server Stored Procedure to access the data via an OLE DB Source. Now I know this works because I can sign onto SSMS as the Service Account and manually run the SQL Server Stored Procedure and it runs in about a minute. When I look at the Output in Microsoft Visual Studio I see...
Data Flow Task, 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.
I found a couple of reference sites which indicate that you have to add the Service Account to the "Performance Counters" Group. I guess I would ask do you have to do that in Azure as well?
I also found a discussion which refers to Admin Rights for the SSIS Account...
I honestly believe this is happening because of the way the Service Account is trying to attempt to access Azure SQL via the Linked Server.
Now tell me if this makes any sense...we have another SSIS Package that accesses [DynamicsCRM] in Azure SQL from the VM side...However...In this case and this SSIS Package, it does not access [DynamicsCRM] via a Linked Server. Instead, we set-up aliases on the VM side to access the Azure SQL Tables we needed.
Sooooo I think that's the path I'm going to go down here...Look at this other SSIS Package as a model that is accessing Azure SQL successfully via a SSIS Package using the same Service Account but try setting up aliases on the VM side.
Thanks for your review and am hopeful for a reply and some insights.