Add DLL to GAC for Microsoft Dynamics CRM SSIS packages

I recently developed a SSIS package for Microsoft Dynamics CRM and after deploying it on the production SQL server I got this Error:

 Data Flow Task:Error: System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.Xrm.Sdk, Version=6.0.0.0,
 Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.
 File name: 'Microsoft.Xrm.Sdk, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
 at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
 at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PreExecute()
 at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper)

Since multiple CRM SDK  assemblies has been referenced in my SSIS project, the package execution failed with file not found error. In order to fix the issue,  those DLLs should be added to GAC in production server and here are the steps  to do it:

  1. My production environment is Windows server 2012, SQL server 2012, .net framework 4.0.
  2. In order to register DLLs I used Gacutil tool. The tool is part of Visual Studio tool set and in order to have it in production: Copy these two Gacutil files from dev environment
    • C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\NETFX 4.0 Tools\gacutil.exe
    • C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\NETFX 4.0 Tools\gacutil.exe.config
  3. Paste two files to “C:\Windows\Microsoft.NET\Framework\v4.0.30319” folder in production (.net 4.0):
  4. Run the command prompt as administrator and execute below command for each one of the CRM DLLs. for instance:
    • “C:\Windows\Microsoft.NET\Framework\v4.0.30319\gacutil.exe” /i “C:\Microsoft.Xrm.Sdk.dll”

now the package should run successfully on production server.

Increase SQL server Maximum Memory from Command Prompt

You can change the min and max amount of memory allocation for each instance in SQL Server Management Studio, the defaults are:

server properties

If you accidentally set the “Maximum Server Memory” to below the amount which could run the services, you might not be able to start the SQL server service from Services or SQL Server Configuration Manager console and you will get this error if you want to log in to SSMS :

Failed to connect to server myServer. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

 

Here are the steps to increase the SQL server memory:

1. If SQL server service is stopped, open the command prompt navigate to your SQL server instance folder (mine is C:\Program Files\Microsoft SQL  Server\MSSQL11.MSSQLSERVER\MSSQL\Binn) and run this command in order to start the service with minimum requirement and single user mode :

 sqlservr -f -m -s nameofyourinstance

command1

2.while keeping previous command prompt open, open a new command prompt as an administrator and navigate to your SQL server instance                folder (mine is: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn) and run this command to increase instance  memory to 4GB:

 sqlcmd 
 EXEC sys.sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sys.sp_configure 'min server memory', 1024;
GO
EXEC sys.sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO 

sql command2

3. Now SQL server memory is increased. close those cmd windows and make sure the SQL server service is running by going to SQL Server  Configuration Manager

sscm

 

You should be able to connect to your instance through SSMS.