none
Microsoft.SqlServer.Management.IntegrationServices dll is missing

    Question

  • Hi,

    I need to load a package programatically that is deployed to the SSIS catalog with the project deployment model. All the doc points to doing this using Microsoft.SqlServer.Management.IntegrationServices namespace; file: Microsoft.SqlServer.Management.IntegrationServices.dll.
    I cannot find this file. I have installed SqlServer developer version. Integration Services, SqlServer Data Tools, Client Tools SDK and Management Tools are selected and show installed.

    The Microsoft.SqlServer.Management.IntegrationServices.dll is not in the gac or anywhere on my machine.

    What I want to do is documented here: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.integrationservices.aspx.

    Can you tell me what I need install to have this dll available?

    Thanks,
    Suzanne
    Wednesday, June 11, 2014 6:59 PM

Answers

  • Hi Suzanne,

    By default, it is not supported to browse the GAC folders or search for files within the GAC folders via Windows Explorer. To view the GAC folders in Windows Explorer, you can use the subst command line which associates a path with a virtual drive letter. For example, we can run the following command line in Prompt Command:

    Subst H: %windir%\assembly\gac_msil

    Then, we can open drive H to browse the folders within the GAC_MSIL folder, and find the Microsoft.SqlServer.Management.IntegrationServices folder.

    To delete the virtual driver H, we can run the following command:

    Subst H: /d

    However, when we try to add a reference in an application project, we can navigate to the GAC folders directly. In other words, although we cannot direct browse the GAC folders in Windows Explorer, we still can add reference to the Microsoft.SqlServer.Management.IntegrationServices.dll within the application project.

    Regards,


    Mike Yin
    TechNet Community Support


    Wednesday, June 18, 2014 1:58 AM
    Moderator

All replies

  • Suzanne hi,

    What do you mean load? I guess you want to programmatically execute a package. Right?

    You gave us namespace class names in the MSDN article.

    The DLL will be present but inside a one or more folders (depends on version) in

    %WINDIR%\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices


    Arthur My Blog

    Wednesday, June 11, 2014 7:35 PM
    Moderator
  • Hi Arthur,

    Thanks for the response. I am actually reading though the package events and pulling the lineage ids and column names for error handling.

    This dll is not in the gac on my machine.

    Microsoft.SqlServer.Management.CollectorTasks

    Microsoft.SqlServer.MSMQTask

    So, there is no Microsoft.Sqlserver.Management.IntegrationServices.

    If you want to see all the code to pull the lineageid and column names, I can send it. It works fine when I am coming off the file folder in dev. But once it is deployed to the catalog, I am not able to load it.

    What is supposed to put this dll on my machine? Do I need a different version of sqlserver? I loaded the dev version last night.

    Thanks,

    Suzanne

    Wednesday, June 11, 2014 9:03 PM
  • Hi,

    I see no point frankly in "reading though the package events and pulling the lineage ids and column names for error handling".

    Besides, when the package gets deployed this does not change, but it can no longer be interrogated remotely easily.

    How does it help to error handling is obscure to me.

    What are you trying to achieve?

    SQL Server Developer edition is the correct approach to developing against SSIS.

    Did you find the SSIS folder in %WINDIR%\assembly\GAC_MSIL?

    It must be there.


    Arthur My Blog

    Thursday, June 12, 2014 12:45 AM
    Moderator
  • When an error is routed, the column number (lineage id) is available, but the column name is not. The lineage id is not helpful. I guess before 2012, the lineageid was available in the package xml and could be gotten that way. I found numerous examples from folks, but with 2012, the lineageids changed to text.

    So, what I am doing in a script task is retrieving the lineageids and column names (along with the executable task name) and storing them in a variable that is then available to the errors. (Also getting the error description, but that is easy).

    What is the recommended method of getting the column name when there is an error?

    I found some form of this code somewhere and added to it. It is working when I can load the package, which is what I am trying to do with the IntegrationServices.dll

    pkg = application.LoadPackage(pkgPath, null);

     Executables pExecs = pkg.Executables;
                StringBuilder sb = new StringBuilder();
                string d1 = ";";
                string d = ":";

    foreach (Executable pExec in pExecs)
                {

                    TaskHost taskHost = (TaskHost)pExec;
                    
                    string taskhostName = taskHost.Name;
                    

                    //Test if the task is a data flow
                    if (taskHost.InnerObject is Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe)
                    {

                        //Cast the Executable as a data flow
                        MainPipe pipe = (MainPipe)taskHost.InnerObject;
                                        
                        // Loop over each object in the dataflow
                        foreach (IDTSComponentMetaData100 comp in pipe.ComponentMetaDataCollection)
                        {
                            foreach (IDTSOutputColumn100 column in comp.OutputCollection[0].OutputColumnCollection)
                            {
                                sb.Append(string.Concat(taskhostName, d, column.LineageID.ToString(), d, column.Name, d1));
                            }
                        }
                    }
                }

    Dts.Variables["User::colList"].Value = sb.ToString();

    So, this is in a script task that runs before any of the DFTs. The error paths all have access to the colList variable via a script component. It gets split, loaded to a class list and queried with linq based on the lineageid of the column that errored. If you have something better, I would be glad to know about it. My requirements is to provide the column name and error description when there is an error.

    I DO NOT have the Microsoft.Sqlserver.Management.IntegrationServices.dll on my box. I have searched from the c:. As I said in the previous post, sorted by name, I see

    Microsoft.SqlServer.Management.CollectorTasks

    Microsoft.Sqlserver.Management.IntegrationServices should be the next folder. It isn't there!!

    The next thing I see is Microsoft.SqlServer.MSMQTask.

    I do see 4 Microsoft.SqlServer.IntegrationServices. folders, but the Management.IntegrationServices isn't there.

    Thanks for your help,

    Suzanne

    Thursday, June 12, 2014 4:55 AM
  • Suzanne, when I need to know what column erred out I do it from within the package as in http://sqlage.blogspot.ca/2013/12/ssis-how-to-get-error-column-name-in.html

    Do you see folder(s) under %WINDIR%\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices?


    Arthur My Blog

    Thursday, June 12, 2014 1:37 PM
    Moderator
  • Thanks Arthur,

    This is what I was talking about that changed with SSIS 2012. This is from your link:

    " Read Package File to Get LineageIDs 

    Our goal is to get the column name, to do that we have to read the package file(.dtsx) and get the Lineage IDs for columns. The lineageID can be same for different columns in different Tasks. We will also read the Task Name so each record will be unique with combination of Task Name."

    This is what the xml USED TO look like with the lineageids being available in the xml.

    <outputColumn id="73" name="LookAtMe" description="" lineageId="73" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Computation" errorRowDisposition="RedirectRow" truncationRowDisposition="RedirectRow" externalMetadataColumnId="0" mappedColumnId="0"><properties>

    This is what they look like now:

     lineageId="Package\FBXErrorEventData ODS_EventDetail child\OLE DB Source FBXErrorEventData.Outputs[OLE DB Source Output].Columns[DeviceEventId]"

    So, previous to 2012, the lineageid was available in the xml. With 2012, it is no longer available in the as the column number.

    Here is some additional info documenting this:

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6425e61a-a7fe-4ce4-88fc-f90f544b25e9/lineageid-of-an-input-column-has-an-another-value-in-runtime?forum=sqlintegrationservices

    No, I am not seeing that folder in the gac.

    Thursday, June 12, 2014 4:41 PM
  • Yes, in SSIS 2012 GUIDs became strings as the package format has changed, also changed in SSIS 2014 again.

    Not being able to seeing the assembly makes me think the SSIS installation is invalid.

    This is what I see on my machine:

    I suggest you re-install SSIS


    Arthur My Blog

    Thursday, June 12, 2014 5:43 PM
    Moderator
  • Is there a separate install for SSIS? what I installed was sqlserver developer version and made sure "Integration Services" was selected as an option.

    Thursday, June 12, 2014 6:17 PM
  • Nope, it is part of the SQL Server installation media.

    Also choose shared components.


    Arthur My Blog

    Thursday, June 12, 2014 6:19 PM
    Moderator
  • One would think MS would have made this a whole lot easier. when running in debug and inspecting the com object, the column name is visible, but they didn't define a "getter" for it, so it is not accessible.  :(
    Thursday, June 12, 2014 6:20 PM
  • yes, that is installed as well. Do I need to install a different version? Is there a way to find out what versions it comes with. I have the developer version installed.
    Thursday, June 12, 2014 6:36 PM
  • The developer edition is what it should be.

    I think you still need to reinstall, I am curious if you are local admin on your machine?


    Arthur My Blog

    Friday, June 13, 2014 1:29 PM
    Moderator
  • Hi Suzanne,

    By default, it is not supported to browse the GAC folders or search for files within the GAC folders via Windows Explorer. To view the GAC folders in Windows Explorer, you can use the subst command line which associates a path with a virtual drive letter. For example, we can run the following command line in Prompt Command:

    Subst H: %windir%\assembly\gac_msil

    Then, we can open drive H to browse the folders within the GAC_MSIL folder, and find the Microsoft.SqlServer.Management.IntegrationServices folder.

    To delete the virtual driver H, we can run the following command:

    Subst H: /d

    However, when we try to add a reference in an application project, we can navigate to the GAC folders directly. In other words, although we cannot direct browse the GAC folders in Windows Explorer, we still can add reference to the Microsoft.SqlServer.Management.IntegrationServices.dll within the application project.

    Regards,


    Mike Yin
    TechNet Community Support


    Wednesday, June 18, 2014 1:58 AM
    Moderator