locked
Add SSIS package to Integration Services Project RRS feed

  • Question

  • I believe that what I want is not currently possible but thought I had better throw it out there first.

    I have a scenario where I need to generate many hundreds of SSIS packages which in itself is fine, where I am falling down is I want them added to an SSIS project in VS2012 automatically.

    I have gone down the route of using envDTE which is the standard (as I understand it) way to programmatically add files to projects.

    Code:

    EnvDTE.DTE dte2;
    dte2 = (EnvDTE.DTE)System.Runtime.InteropServices.Marshal.
    GetActiveObject("VisualStudio.DTE.11.0"); 
                
    Projects projy = dte2.Solution.Projects;
    foreach (Project d in projy)
    {
        Console.WriteLine(d.Name.ToString());
        if (d.Name == "Integration Services Project1")
        {
            foreach (ProjectItem g in d.ProjectItems)
            {
                Console.WriteLine("    " + g.Name.ToString());
                        
            }
            d.ProjectItems.AddFromFile(pkg); // Error Thrown here
        }
    }

    I get the error "A null reference pointer was passed to the stub. (Exception from HRESULT: 0x800706F4)" I assume at this point it has something to do with SSIS projects requiring GUIDs for files which are not created as part of the AddFromFile method.

    Is there any way to accomplish what I want to do without having to mess around with the sln??

    Tuesday, September 17, 2013 9:43 PM

All replies

  • Hi,

    First,please check the pointer defined in your code and make sure that it is not null.

    On the other hand ,you could try to " Add SSIS package to Integration Services Project " by modifying the .dtproj file.

     <SSIS:Packages>
              <SSIS:Package SSIS:Name="Package.dtsx" SSIS:EntryPoint="1" />
            </SSIS:Packages>

    The following link is about how to fix the error "System.Runtime.InteropServices.COMException (0x800706F4): A null reference pointer was passed to the stub. (Exception from HRESULT: 0x800706F4)" :

    FIX: Incorrect methods are called when you call some COM APIs that are included in a .NET Framework 2.0-based 64-bit application

    If your problem remains unresolved, please provide more details about your SSIS project for further analysis.

    Best Regards,

    Jane.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Wednesday, September 18, 2013 9:59 AM
  • Thanks for your reply Jane

    The pointer is definitely not null as I can add the file successfully to other project types (Class Library & Console App)

     <SSIS:Packages>
              <SSIS:Package SSIS:Name="Package.dtsx" SSIS:EntryPoint="1" />
            </SSIS:Packages>

    There is more to the dtproj file reference than the code above, there are a number of additional nodes required including GUID etc, adding just the above will corrupt the project file (A painful trial and error process taught me this)

    I suspect that the SSIS project type either does not have the extensibility required to achieve what I want OR there is a different way to do it which is undocumented.

    I have additionally tried to add the file at ProjectItem level by using DTE to move to he uppermost extensible object, this resulted in the same error.

    When I get home I will post a full step by step with code to demonstrate what I mean.

    Wednesday, September 18, 2013 12:19 PM
  • OK More info as promised.

    Step 1

    1. Create a new solution
    2. Add a SQL Server Integration Services project to the solution
    3. Save
    4. Open the SSIS project solution in Windows explorer and copy the Package.dtsx to C:\SSISTest

    Step 2

    1. Create a new Solution
    2. Add a C# Console Application Project to the solution
    3. Add a SQL Server Integration Services project to the solution
    4. Modify the solution properties to only start the Console Application
    5. Modify the main method in Class1.cs of the C# Console Application Project to read as per code below
    6. Debug and aside from some RETRY errors which can be skipped over you will see the error once the method attempts to create the file in the SSIS project
    using EnvDTE;
    using System;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                string pkg = @"C:\SSISTest\Package.dtsx";
    
                EnvDTE.DTE dte;
                dte = (EnvDTE.DTE)System.Runtime.InteropServices.Marshal.
                GetActiveObject("VisualStudio.DTE.11.0");
    
                Projects projy = dte.Solution.Projects;
                foreach (Project d in projy)
                {
                    d.ProjectItems.AddFromFile(pkg);
                }
            }
    
        }
    }

    The message received is "Could not load file or assembly 'Microsoft.DataWarehouse.VsIntegration, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified."

     Stack Trace " at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
       at System.Reflection.RuntimeAssembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
       at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
       at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection)
       at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
       at System.Reflection.Assembly.Load(String assemblyString)
       at System.Runtime.Serialization.FormatterServices.LoadAssemblyFromString(String assemblyName)
       at System.Reflection.MemberInfoSerializationHolder..ctor(SerializationInfo info, StreamingContext context)
       at System.Exception.GetExceptionMethodFromString()
       at System.Exception.GetTargetSiteInternal()
       at System.Exception.get_TargetSite()"
    Wednesday, September 18, 2013 8:24 PM
  • Hi,

    We should keep in mind that no matter where we put the SSIS packages manually,Visual Studio will load them from the default project file folder automatically.

    Now that you assigned the path by programming,please check if the path in your code are in accord with the physical path.

    If not, you might get the "A null reference pointer was passed to the stub. (Exception from HRESULT: 0x800706F4)"  above.

    As for the following error message:

     "Could not load file or assembly 'Microsoft.DataWarehouse.VsIntegration, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified."

    I think you may need to do some repair or update.For more information,please refer to the related topic below:

    getting error Microsoft.DataWarehouse.VsIntegration

    Best Regards,

    Jane.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Friday, September 20, 2013 12:10 PM
  • Thanks for the reply again Jane

    Creating the package in the SSIS folder location is not the issue, its the adding to the projects "include" list that is causing me the problem.

    I "*think" the null reference pointer is the return error from the project as no GUID was passed with the request.

    Can you confirm that it is programmatically possible to add a dtsx package to an SSIS project within Visual Studio by using envDTE? If it is can you provide a code sample please.

    FYI My current workaround is to directly modify the dtproj file by injecting xml which is not ideal to say the least!

    Friday, September 20, 2013 7:52 PM
  • Can you confirm that it is programmatically possible to add a dtsx package to an SSIS project within Visual Studio by using envDTE? If it is can you provide a code sample please.

    I have delivered this issue to the team members with rich experience for better response.

    Kind Regards,

    Jane.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Saturday, September 21, 2013 12:21 PM
  • Have you received any feedback yet?
    Saturday, September 28, 2013 2:17 PM
  • Hi,

    Sorry for the late reply as I had to get some info from our Visual Studio IDE team as well. envDTE does not support loading of SSIS packages out of the box, it is available for console apps (exe) and class libraries (.dll). However, there is some customization that could be done on top of envDTE to work for other file types as well but that require thorough investigation and code development effort. I f you want to proceed that way, I would request you to open a support ticket with Microsoft Customer Support.

    Thanks,


    DebarchanS - MSFT

    Tuesday, November 12, 2013 4:37 AM