Installing Custom SSIS Components into GAC Using PowerShell

In one of my previous posts SSIS 2008 Columns To XML Data Flow Transformation I have presented a custom SSIS Data Flow Transformation.

As mentioned in the post, to be able to use the component, it has to be put into PipelineComponents folder of the SSIS Installation (For SQL Server 2008 common location is C:Program FilesMicrosoft SQL Server100DTSPipelineComponents) and also it needs to be installed into GAC(Global Assembly Cache). The location in the PipelineComponents is important for the BIDS designer and the GAC installation is important for SSIS execution.

Placing the component assembly into the PipelineComponents folder is very straightforward as you simply copy/past the assembly .dll file into this directory.

On the other side, installation into the GAC can be a little bit tricky and depend on the OS and .Net Framework version for which the assembly is built.

Components for SQL Server 2005 and 2008 (.Net < 4.0)

If the component is build for SQL Server 2005 or 2008, then yo use .Net framework lower than 4.0 and the installation relatively easy.

Installation on Windows Server 2003 and Windows XP

Installation of the .Net 2.0 or .Net 3.5 assemblies on the Windows Server or Windows XP is relatively easy as the GAC is located in C:Windowsassembly directory and can be easily managed by the windows Explorer. Simply navigate into this directory and Drag-Drop the assembly .dll file(s) here.

<img class="alignnone size-full wp-image-634" title="Sever 2003 GAC (.Net

If you need to uninstall the assembly, simply find your assembly in the GAC, right-click on it and choose Uninstall. You will need to uninstall the assembly if you would like to deploy an updated version (eg. because of bug fix or for whatever reason). In that case you have to uninstall such assembly first and the install it again.

Installation on Windows Server 2008, Vista ans Windows 7

Installation of assembly into GAC on the Windows Server 2008, Vista and Windows 7 can be a little bit trickier. However the assemtrickierbly has the same location as on Windows Server 2003 and Windows XP (C:Windowsassembly), those systems are missing the explorer integration for Installation purposes (you cannot Drag-Drop assemblies into that directory).

For that purpose you can use the gacutil.exe but this is not part of the standard installation. You have to install the Windows SDK. Once installed, then this utility is located in C:Program Files (x86)Microsoft SDKsWindowsv7.0ABin directory. You do not need to install the SDK on all the machines where you need to deploy the assembly. It is enough to copy the gacutil.exe and gacutil.exe.config files to the target machines and use it to install/un-install the assembly. You have to execute it from command prompt with administrative privileges, so in case of UAC enabled, you have to run the command prompt AS Administrator.

To install use the gacutil.exe with the /i switch:

gacutil.exe /i "C:Program FilesMicrosoft SQL Server100DTSTE.SSIS.DataFlow.dll"

To list all the assemblies in GAC you can use /l switch:

gacutil.exe /l

Eventually you can put assembly name filter after the /l switch (it will list all installed versions of the assembly, as GAC support multiple versions of the same assembly to be installed at the same time):

gacutil.exe /l TE.SSIS.DataFlow

To uninstall an assembly from GAC use the /u switch:

gacutil.exe /u TE.SSIS.DataFlow

Components for SQL Server 2012 (.Net  4.0) and .Net 4.0 GAC Structure

With SQL Server 2012 the things start to be a little more tricky than with previous versions as components needs to be compiled under .Net framework 4.0.

In .Net 4.0 the GAC location has been changed and is located under C:WindowsMicrosoft.NETassembly directory.

As we can see, the structure has changed and there are separate directories for each platform. When building the components for SSIS we build them for MSIL (as it is platform independed and allow us to run the packages in both x32 and x64 runtime). In fact the structure is the same as for c:windowsassembly, but for the c:windowsassembly it is hidden in explorer. If you list the c:windowsassembly directory by dir command, you should see the same three directories on x64 system.

Each assembly has another directory in the GAC corresponding to the assembly name and under this directory there are directories for each version of the assembly and are named using the assembly strong name. Then  under this strong name directory are located the assembly .dlls

Installing .Net 4.0 assembly into GAC

As mentioned above, the GAC location and structure has changed in .Net 4.0. Also it is not possible to use the tools for .Net 3.5 and lower (I men gacutil.exe for older framework) and for security reason, you cannot also use simply Copy-Paste into GAC directory as it was for .Net < 4.0 on Server 2003 and Windows XP.

Again in you install Windows SDK and go through the directory structure, you will find tools for .Net 4.0 under the C:Program Files (x86)Microsoft SDKsWindowsv7.0ABinNETFX 4.0 Tools

In this directory you will find also the gacutil.exe for .Net 4.0. I was able to use it and install the assembly into GAC without problems on Windows 7 in the same way as it was mentioned above.

But when I try to copy it and use it on Windows Server 2008, I was not able to get the gacutil.exe for .net 40 working on it. gacutil.exe only prints a copyright information and that’s all.

 PowerShell the ultimate GAC installation solution

As I was not able to install the assemblies using the gacutil.exe I was starting to search for alternate solution of GAC installation and found the PowerShell.

You are able to install the assemblies to GAC on all above mentioned systems using a simple PowerShell Script.  For this purposes PowerShell will utilize System.EnterpriseServices.Internal.Publishh class which is able to install and uninstall the assemblies from GAC.

Only note to the Publish class. When you use it and make some typo or something, the GacInstall and GacRemove methods do not print any error message. So you have to be careful when typing the paths etc. To verify that the assembly was successfully installed /uninstalled to/ from GAC, simply browse to the GAC directory using Explorer and search for your assembly.

Install script we will use GacInstall method of the Publish class.

#Load System.EnterpriseServices assembly as it contain classes to handle GAC
[Reflection.Assembly]::LoadWithPartialName("System.EnterpriseServices")

#Create instance of Publish class which can handle GAC Installation and/or removal
[System.EnterpriseServices.Internal.Publish] $publish = new-object System.EnterpriseServices.Internal.Publish;

#Install dll into GAC using GacInstall method (Provide full path to the assembly)
$publish.GacInstall("C:Program FilesMicrosoft SQL Server110DTSTE.SSIS.DataFlow.dlll");

To uninstall an assembly you can use the GacRemove method of the Publish class. When uninstalling, you have to provide the complete path to the assembly dll.

#Remove from GAC using GacRemove method (Provide full path to the assembly in GAC)
$publish.GacRemove("C:WindowsMicrosoft.NETassemblyGAC_MSILTE.SSIS.DataFlowv4.0_1.0.0.0__0eedace497e8d607TE.SSIS.DataFlow.dll")

You can save the script into a script file e.g. GACInstall.ps1 and execute it from command line:

powershell.exe -noprofile -executionpolicy RemoteSigned -file c:scriptsGACInstall.ps1

Alternatively you can run the PowerShell console and run the command by command. Again PowerShell needs to be executed with Administrative privileges so take UAC in mind.

If you would like to use the PowerShell ISE (Integrated Scripting Environment) and execute the script form there, don’t forget set the Execution policy appropriately:

#Enable Local not signed Script Execution
Set-ExecutionPolicy RemoteSigned;

If you want to use the ISE also on Windows Server 2008, you have to enable that feature on the server first, as the PowerShell ISE is not installed and enabled by default on Windows Server 2008. So go to Server Manager,  choose Add Features and add the PowerShell ISE Feature.

Using the PowerShell, you are independed of any tools like gacutil.exe etc. and you are able to install whatever assembly on whatever system which has PowerShell available.

Hope this post will help you saving troubles when deploying custom SSIS packages to server.

Advertisements

SSIS 2008 Columns To XML Data Flow Transformation

This article represents a custom components for transformation of columns inside SSIS Data Flow into XML.

If you are writing ETL processes using SSIS, you many times comes to situation, when you need log errors or other problems which can occur during ETL process. An example can be a failure of data conversion during the ETL process. In that situation you mostly would like to know that record exactly caused that error and log such conversion errors into particular error table.

Problem occurs when you have a lot of tables and want to see what data were converted and caused that particular error. As SSIS requires that the sources and destinations needs to be strongly mapped and you have different sources with different structures, this leads to a situation where you have a lot of different error tables – generally one per the source structure at which an error can occur.

This situation can be easily solved by a custom Data Flow component which will convert all source columns into an XML. In that situation you can only have one Error Table, which will contain e.g. Error Code, Error Column, Description and an XML Field in which will be all the data of particular row which caused error encoded in XML Form. This rapidly simplifies error logging and also error log monitoring as you need to monitor only a single table.

So the SSIS Data Flow with the Custom Columns to XML Transformation could look like the one below.

Columns To Xml Data FLow

ColumnsToXML PropertiesColumnsToXML Input Columns

You can provide a SourceID and SourceName properties which are then part of the produced XML so you can easily identify the source. In the Input Columns you select columns you want to be written to the XML. You can also specify an OutpuAlias and the the column names written into the XML will be the one specified in the OutputAlias property.

The Log written using that component and the XML produced will look like the ones below.

ColumnsToXML Log

<row sourceID="1" sourceName="Test Data Conversion">
  <Column name="Name" id="235" lineageId="72">Joseph</Column>
  <Column name="ValueInt" id="236" lineageId="75">2</Column>
  <Column name="ValueDate" id="237" lineageId="78">2012/01/35</Column>
</row>

Using the .value method of XML data type we can easily querying necessary information from the log table for whatever source in the table. So for our sample scenario we would like to query the Name, ValueInt and ValueDate columns stored as XML. This can be easily achieved by below SQL query:

SELECT
    [RowID]
    ,[ErrorCode]
    ,[ErrorColumn]
    ,[ColumnsXMLData].value('data((/row/Column[@name="Name"])[1])', 'varchar(128)') AS Name
    ,[ColumnsXMLData].value('data((/row/Column[@name="ValueInt"])[1])', 'varchar(10)') AS ValueInt
    ,[ColumnsXMLData].value('data((/row/Column[@name="ValueDate"])[1])', 'varchar(10)') AS ValueDate
FROM [TestDB].[dbo].[ETLErrorLog]
WHERE [ColumnsXMLData].value('data((/row/@sourceID)[1])', 'int') = 1

As you can see from the samples above, you can create easily create single Error table for whatever ETL logging and simplify the logging rapidly as you#160; do not need to create the error tables with separate structures for each source.

ColumnsToXML Implementation

The ColumnsToXmlTransform is a custom implementation of the PipelineComponent. It is developed in C# for .NET framework 3.5 and uses Linq to XML for creating the XML output.

The component processes selected input columns, generates the XML and stores it in the output column called ColumnsXMLData.

Except the ColumsXMLDataSourceID and SourceName) to allow you to identify the source row source once it is written as XML into the database and simplifies querying for data coming from particular sources as showed above.

Once the component is built, it is necessary to install it into the Global Assembly Cache and to the PipelineComponents folder in the SQL Server Integration services installation path on the machine on which the SSIS using this component will be running.

To install it into a GAC you can use the gacutil.exe which is part of the Windows SDK.

Component is now available publicly with other components on GitHub as projects for SSIS 2008 – SSIS 2016.

If you run the development environment as with elevated Administrator privileges, the project contains Post Build events, which will install the assembly into the GAC (it assumes you have Windows SDK 7.0 installed – if you have newer version, modify the path to the gacutil.exe accordingly) and also copies the assembly into the codePipelineComponents/code folder of the default SQL Server Integration services installation. If you installation directory differ, please modify the paths in Post Build events accordingly.

Once you have compiled the code, installed in GAC and copied to the PipelineComponents folder, you can include the component to the data flow items palette. Right-click on the palette and select Choose Items

Choose Data Flow Transformation Items Pop-upChoose SSIS Data Flow Items

Once you select the component, it will appear on the toolbox and you can start using it.

Columns To XML On Palette

Once I will have more time, I will try to create an installer for the component. Anyway, any comments are welcome.

Reading SharePoint List and Survey data using Integration Services package

Sometimes you can come to situation when you need to read data from SharePoint and store them in database on SQL Server.

If you need to read a SharePoint List, then you can use SharePoint List Source and Destination components from CodePlex. There is a quite good presentation how to use it on MSDN site Extracting and Loading SharePoint Data in SQL Server Integration Services

These components work very well for the SharePoint List. Unfortunately you will have no success using them for reading data from the SharePoint Survey, although the structure of the data are nearly the same as for list. Simply these components doesn’t support Survey data. Also it can happen, that you cannot install any third party component you your server and therefore you cannot use the SharePoint List Source and Destination components.

In that case you can use a script which will extract the XML data out of the SharePoint and you have two possibilities how to handle the XML.

  1. You can save the XML file to some temporary location and then process the XML file using the XML Source in the Data Flow task to read data from it.
  2. You can process the XML inside the Script Component in the Data Flow task and direct records to corresponding output.

To retrieve the data you can use a below class snippet (C#), which you put into your Script Component or Scrip Task.

internal class SharePointDownloader
{
    public static XmlDocument GetSharePointListXml(string listUrl)
    {
        ServicePointManager.ServerCertificateValidationCallback = new RemoteCertificateValidationCallback(CertificateOverride.RemoteCertificateValidationCallback);
        WebResponse response;
        HttpWebRequest request = (HttpWebRequest)System.Net.HttpWebRequest.Create(listUrl);
        CookieContainer cookieContainer = new CookieContainer();

        request.CookieContainer = cookieContainer;
        request.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;

        response = request.GetResponse();
        string html;
        using (StreamReader sr = new StreamReader(response.GetResponseStream()))
        {
            html = sr.ReadToEnd();
            sr.Close();
        }

        XmlDocument doc = new XmlDocument();
        doc.LoadXml(html);

        XmlDocument destXml = new XmlDocument();
        XmlDeclaration dec = destXml.CreateXmlDeclaration("1.0", null, null);
        destXml.AppendChild(dec);
        XmlNode data = destXml.CreateElement("Data");
        destXml.AppendChild(data);

        foreach (XmlNode node in doc.GetElementsByTagName("z:row"))
        {
            XmlNode row = destXml.CreateElement("row");
            foreach (XmlAttribute attr in node.Attributes)
            {
                XmlAttribute da = destXml.CreateAttribute(attr.Name);
                da.Value = attr.Value;
                row.Attributes.Append(da);
            }
            data.AppendChild(row);
        }

        return destXml;
    }

    internal class CertificateOverride
    {
        public static bool RemoteCertificateValidationCallback(object sender, X509Certificate certificate, X509Chain chaing, SslPolicyErrors sslPolicyErrors)
        {
            return true;
        }
    }
}

As mentioned above put this class as part of the Script Component or Script Task and use the static method SharePointDownloader.GetSharePointListXml method to retrieve the XML data.

The function takes as argument an URL which points to the list/survey you need to download. You can retrieve this link from the .igy file which you get, when you select Export To Spreadsheet action in the SharePoint. Instead opening the file in excel you choose to save it and retrieve the URL from this file.

It is necessary to mention the assignation of the ServerCertificateValidationCallback property of the ServicePointManager class to a newly created internal class, which simply returns true. This is necessary to avoid problems with certificates.

The code snippet also uses the DefaultNetworkCredentials which represents credentials of the account under which the SSIS package will be executed. You can eventually provide other credentials if necessary.

One very important thing related to this snipped is, that in case of downloading Survey data, the account under which the code will be executed needs admin rights to the Survey in SharePoint, otherwise it will have no access to the complete list of responses in the survey.

So finally a complete sample of a Script Task could look similar to below code:

[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

    #region VSTA generated code
    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };

    #endregion

    public void Main()
    {

        string url = "source data URL retrieved from the .igy file of the list/survey";
        string destFile = "c:tempfile.xml";
        try
        {
            XmlDocument data = SharePointDownloader.GetSharePointListXml(url);
            data.Save(destFile);
        }
        catch
        {
            Dts.TaskResult = (int)ScriptResults.Failure;
            return;
        }
        Dts.TaskResult = (int)ScriptResults.Success;
    }
}

internal class SharePointDownloader
{
    public static XmlDocument GetSharePointListXml(string listUrl)
    {
        ServicePointManager.ServerCertificateValidationCallback = new RemoteCertificateValidationCallback(CertificateOverride.RemoteCertificateValidationCallback);
        WebResponse response;
        HttpWebRequest request = (HttpWebRequest)System.Net.HttpWebRequest.Create(listUrl);
        CookieContainer cookieContainer = new CookieContainer();

        request.CookieContainer = cookieContainer;
        request.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;

        response = request.GetResponse();
        string html;
        using (StreamReader sr = new StreamReader(response.GetResponseStream()))
        {
            html = sr.ReadToEnd();
            sr.Close();
        }

        XmlDocument doc = new XmlDocument();
        doc.LoadXml(html);

        XmlDocument destXml = new XmlDocument();
        XmlDeclaration dec = destXml.CreateXmlDeclaration("1.0", null, null);
        destXml.AppendChild(dec);
        XmlNode data = destXml.CreateElement("Data");
        destXml.AppendChild(data);

        foreach (XmlNode node in doc.GetElementsByTagName("z:row"))
        {
            XmlNode row = destXml.CreateElement("row");
            foreach (XmlAttribute attr in node.Attributes)
            {
                XmlAttribute da = destXml.CreateAttribute(attr.Name);
                da.Value = attr.Value;
                row.Attributes.Append(da);
            }
            data.AppendChild(row);
        }

        return destXml;
    }

    internal class CertificateOverride
    {
        public static bool RemoteCertificateValidationCallback(object sender, X509Certificate certificate, X509Chain chaing, SslPolicyErrors sslPolicyErrors)
        {
            return true;
        }
    }
}

Once you save the data to a temporary destination, you can connect to the xml file using the XML Source in the Data Flow Task. What you will need is to generate the XSD schema from the XML source and keep that XSD schema for future use as in case of any other XML file which needs to be imported by SSIS.

If you would like to process the XML in the Script component, again you simply put the code inside the Script component, retrieve the XML and then go through the XML programmatically and direct the data to appropriate output of the script component.

Of course you can make some fine tuning for the script component to avoid double processing of the file use the core of the GetSharePointListXML and redirect the data directly to and output of the script component when processing the nodes in that part.

SSIS with Excel Source/Destination on x64 SQL Server Instance

Not all people know, that the Excel file format is not supported by the x64 version of SQL server and the same apply to the SSIS runtime. This apply not only to excel but to all data sources for which you have only 32 bit drivers (e.g. ODBC drivers of external data sources etc.

Although you can develop the SSIS package with Excel source/destination in Business Intelligence Studio (BIDS)  on x64 machine, you can design the whole ETL process, but when you try to debug such package, you will receive an error message, that Excel file source is not supported on x64.

Debugging on x64 machine

If you want to debug a package on x64 machine, you have to set the environment not to use the x64 runtime. BIDS by default uses the x64 runtime on x64 machines so it is necessary to disable it. You can do this in the SSIS project properties.

SSIS Project Properties in BIDS

Once you set the Run64BitRuntime to false, you will be able to run and debug the package in the BIDS environment.

Running package on x64 version of SQL Server

As Mentioned above, you can set the SSIS properties, so the BIDS does not use the x64 runtime for running the package containing an Excel source/destination. Unfortunately this settings is only for design time debugging in the BIDS environment. When you deploy the package to the server and try to run it directly or from within and agent job, execution will fail as the Excel is not supported by x64 version of SQL Server.

SQL Server 2008 and newer

On the SQL Server 2008 and newer the solution is easy as the Job Step Properties dialog for a SQL Server Integration Services Package type has an option to execute the package using the 32 bit runtime. The option can be found on the Execution options tab. Once you set the “Use 32 bit runtime”, the SSIS package will run correctly on the x64 version of SQL Server 2008 and above. The option simply set the /X86 argument of the DTExec utility and you can see it on the Command Line tab, once you check it.

Agent Job Step Properties

SQL Server 2005

Situation is quite different on SQL Server 2005 as the Job Step Properties dialog doesn’t have the option to run the package using the X86 runtime. Although it seems, that we are blocked and it will not be possible to run such SSIS package on the x64 version of SQL Server 2005, there is one workaround you can use to run that package. The workaround is to use Operating System (CmdExec) task and use the x86 version of DTExec utility which is also installed on the x64 instance of SQL Server. It’s located in the SQL Server installation directory under “Program Files (X86)” in DTSBinn directory.

To simplify configuration, first choose the SQL Server Integration Services Package Type and select location a SSIS package you want to run.

New Job Step

Once you have selected the package and configured everything necessary for the package execution, you can switch to the Command Line tab where you can see all the necessary arguments to execute the DTExec utility. If you know all the arguments, you do not need to do this steps and simply configure the CmdExec task, although in this way you do not need to write the arguments manually.

New Job Step Command Line

After setting all the necessary parameters, change the step Type to Operating System (CmdExec). All the arguments for the DTExec appear in the command TextBox.

New Job Step - CmdExec

Now you only add the x86 version of DTExec utility including whole path to it.

New Job Step - CmdExec after update

After this, you have a SSIS Package with Excel source/destination (or other data source/destination with only 32bit drivers) configured to run on X64 instance of SQL Server.

Denali side by side installation with SQL 2008 or SQL 2008 R2 corrupts BIDS

Today I’ve just installed a SQL Server Code Name Denali CTP1 as a named instance side by side with instance of SQL Server 2008 R2 to take a look on the new features of Denali

Everything worked fine, unless I started a BIDS and wanted to create a new SSIS project (or open existing one).

Once you try it, you will se a below message.

Corrupted Installation of BIDS cannot open project

The problem is that the CTP1 version of Denali still uses VS2008 shell as Business Intelligence Development studio. In future versions it will be replaced by the VS2010 shell. But now it uses the V2008 shell as SQL Server 2008 or 2008R2 and the Side by Side installation corrupts BIDS.

To correct this, you have to uninstall Denali, and reinstall SQL2008. Then you can install Denali without BIDS.

So to save you trouble, and you want to install Denali side by side with existing SQL 2008 or SQL 2008 R2 installation, you can install all the features of Denali, except the BIDS.