5

I created a SSIS package that pulls data from Google Analytics using the Google.Apis.AnalyticsReporting.v4 library. I have added the library to the GAC using gacutil on my development machine and also on the production machine. If I run the package from Visual Studio on either machine it runs correctly, but if I run the deployed package from SQL Server (through SSMS) it crashes. I have tried running in 64-bit and 32-bit mode with the same results. Other Google.Apis libraries are registered and work fine, only the AnalyticsReporting.v4 causes the crash. Does the GAC have issues with assemblies with numbers in the name (the v4)? Below is a sample of the code that is triggering a crash.

using Google.Apis.AnalyticsReporting.v4;

namespace ST_468d8ac6166c473dacba923eda37a1d1
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            AnalyticsReportingService service = new AnalyticsReportingService();
        }
    }
}
Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
Brandon
  • 766
  • 4
  • 13
  • Default from VS will be in 32 bit mode, default from server will be 64 bit. Try changing the execution mode on the server to 32 bit (look for the advanced tab when you're readying the job for launch) – billinkc May 20 '20 at 18:10
  • I tried switching the execution to 32 bit and that doesn't seem to have made a difference. The script task is failing before anything in the script can execute (I tried inserting a debug message into a table in the database at the start of the script task and it's not there). The SSIS project is also set to run in 64-bit mode under Project->Properties->Debugging, and it runs fine that way in VS2017. – Brandon May 20 '20 at 19:21
  • It sure smells like the package isn't finding the file in the GAC. Other things to confirm before going too far down that road. You're using VS 2017, which version of SQL Server does your project target (right click on the project node and select properties and it's toward the bottom)? Do any packages from the project that don't use the assembly work on the server? This can help rule out oddities in deployment/setup. How about virus scanner on the server, any chance it can be disabled before an execution just to see if it's getting overzealous? – billinkc May 20 '20 at 19:30
  • The project is targeting SQL Server 2017, which is the version it's running on. The server doesn't have an antivirus application installed. All other packages in the project work fine and some reference third party packages as well, like ExcelDataReader, Newtonsoft.JSON, etc. Is there something I can check in the GAC? – Brandon May 20 '20 at 19:51
  • If you can remote onto the box, you could try some trivial [powershell](https://stackoverflow.com/questions/12923074/how-to-load-assemblies-in-powershell) to see if you can import the assembly. If the import fails, then you know something's fouled up with the assembly in the gac. – billinkc May 20 '20 at 20:10
  • I did remote into the server to add the assembly to the GAC to begin with using gacutil under the Developer Command Prompt for VS 2017. It says it was added successfully and I can see the files in C:\Windows\Microsoft.NET\assembly\GAC_MSIL (and I copied them into C:\Windows\assembly\GAC_MSIL as well). – Brandon May 20 '20 at 20:19
  • I'm punching beyond my weight here so hopefully someone else can think of something I'm not. – billinkc May 20 '20 at 20:48
  • 2
    I have implemented a workaround for the time being. I created a Windows Forms app that would pull the data from Google and scheduled in the Windows Task Scheduler instead. The app allows me to include the Google libraries in the executable. – Brandon May 28 '20 at 16:54

0 Answers0