I can speak SQL

Fluent SQL speaker

Building SSIS 2012 using MSBuild – A Journey to DB deployment automation

Posted by kulmam92 on June 7, 2013

This post is the first in a series of entries I am planning to write detailing my experience while attempting to create a process in deploying a DB schema, SSIS and SSRS.

SQL Server Data Tools – Visual Studio Shell

SQL Server Data Tools (SSDT) is an extension of the environment provided by Visual Studio 2010 Shell.

VisualStudioShell_thumb

Wikipedia describes Visual Studio Shell

Visual Studio 2008 introduced the Visual Studio Shell that allows for development of a customized version of the IDE. The Visual Studio Shell defines a set of VSPackages that provide the functionality required in any IDE. On top of that, other packages can be added to customize the installation. The Isolated mode of the shell creates a new AppId where the packages are installed. These are to be started with a different executable. It is aimed for development of custom development environments, either for a specific language or a specific scenario. The Integrated mode installs the packages into the AppId of the Professional/Standard/Team System editions, so that the tools integrate into these editions.

The binary of the SQL Server Data Tools is devenv.exe typically found in C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE.

image_thumb2

DevenvLocation_thumb1

SSIS Deployment model in 2012

To give an explanation as to why I decided to use the Project Deployment model with EncryptSensitiveWithPassword as the protection level, I will briefly describe it here. I will cover the topic at a greater depth in a later series.

Project Deployment Model

The 2012 version of Integration Services supports two deployment models: the Project Deployment model and the Package Deployment model. The Project Deployment model enables you to deploy your projects to the Integration Services server. The Project Deployment model has many benefits compared to the Package Deployment model. You can read Deployment of Projects and Packages and SSIS Package Deployment Model in SQL Server 2012 to get more information about the differences between the two deployment methods.

image_thumb5

If you build an SSIS project file using the project deployment model, a project deployment file will be created (.ispac extension). The project deployment file is a self-contained package that includes only essential information about the packages and parameters in the project. You can check it contents using a file archiver like 7zip.

image_thumb9

If you double click the created (.ispac) file, the Integration Service Deployment Wizard will be launched. The Integration Service Deployment Wizard has a command line interface (ISDeploymentWizard.exe).  You can get the created command in the summary screen.

image_thumb11

SSIS Catalog

The SSIS catalog is a new type of storage for SSIS. It’s a user database so you can query tables and easily backup/restore the whole repository. You may refer to SSIS Catalog and SSIS 2012 Configuration Guide – Part 1: Introduction for more information.

The SSISDB catalog is central to working with Integration Services (SSIS) projects that you’ve deployed to the Integration Services server.In an example case, you would need to set project and package parameters, configure environments to specify runtime values for packages, execute and troubleshoot packages, and manage Integration Services server operations.

image_thumb12

Security for SSIS Packages

To protect the data in an Integration Services package, you can set the protection level.

image_thumb13

Access Control for Sensitive Data in Packages give us an example of protection level settings based on the package life cycle.

Typically, you change the protection level as listed through the following steps as requirements change:

  1. During development, leave the protection level of packages set to the default value of EncryptSensitiveWithUserKey. This setting helps ensure that only the developer is able to see sensitive values in the package. You can also consider using EncryptAllWithUserKey, or DontSaveSensitive.
  2. When it is time to deploy your packages, the protection level should be changed to one that does not depend on the developer’s user key. Therefore you would typically have to select EncryptSensitiveWithPassword, or EncryptAllWithPassword. Encrypt the packages by assigning a temporary strong password that is also known to the operations team for the production environment.
  3. After the packages have been deployed to the production environment, the operations team can re-encrypt the deployed packages by assigning a strong password that is known only to them. Alternatively, they can encrypt the deployed packages by selecting EncryptSensitiveWithUserKey orEncryptAllWithUserKey and using the local credentials of the account that will run the packages.

Command line build of SSIS packages

You may assume that MSBuild will be able to support an SSIS project file (..dtproj extension) by default, though this is not the case. You may see the following error message when attempting to build.

image_thumb15

DEVENV

As I have mentioned earlier, SSDT functionality can be utilized by running devenv.exe, which also provides access to a command line interface. You may refer to Devenv Command Line Switches for the available options. Building using devenv.exe works fine as long as your project’s protection level is not EncryptSensitiveWithPassword, or EncryptAllWithPassword. You can get devenv.exe by installing SQL Data Tools from the SQL2012.ISO. Just a note, The free SSDT tools from the web don’t have BI functionality. You can read Richard Fennell’s Getting SQL 2012 SSIS packages built on TFS 2012.2 for additional information.

D:\App_temp\SSIS\DeployDemo>”C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv” DeployDemo.sln /build “Development|Default”

Microsoft (R) Visual Studio Version 10.0.40219.1.
Copyright (C) Microsoft Corp. All rights reserved.
—— Build started: Project: DeployDemo, Configuration: Development ——
Build started: SQL Server Integration Services project: Incremental …
Starting project consistency check …
Project consistency check completed. The project is consistent.
File ‘D:\App_temp\SSIS\DeployDemo\DeployDemo\obj\Development\DeployDemo.dtproj’get updated.
File ‘D:\App_temp\SSIS\DeployDemo\DeployDemo\obj\Development\Project.params’ get updated.
File ‘D:\App_temp\SSIS\DeployDemo\DeployDemo\obj\Development\LEN-062_JY2012.nxa_repository.conmgr’ get updated.
Applied active configuration to ‘Project.params’.
Applied active configuration to ‘Package.dtsx’.
DeployDemo -> D:\App_temp\SSIS\DeployDemo\DeployDemo\bin\Development\DeployDemo.ispac
Build complete — 0 errors, 0 warnings
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

However, if you change the protection level to either EncryptSensitiveWithPassword or EncryptAllWithPassword, a window will pop up and ask you to enter the protection password. There’s no switch that allows you to provide a password from the command line.

image_thumb19

It was due to this issue that I explored using MSBuild for the build process.

MSBuild

It’s important to know what MSBuild is in order to understand how the new solution works. Let’s read the description from MSDN’s MSBuild page:

The Microsoft Build Engine is a platform for building applications. This engine, which is also known as MSBuild, provides an XML schema for a project file that controls how the build platform processes and builds software. Visual Studio uses MSBuild, but it doesn’t depend on Visual Studio. By invoking msbuild.exe on your project or solution file, you can orchestrate and build products in environments where Visual Studio isn’t installed.

Visual Studio uses MSBuild to load and build managed projects. The project files in Visual Studio (.csproj, .vbproj, .vcxproj, and others) contain MSBuild XML code that executes when you build a project by using the IDE. Visual Studio projects import all the necessary settings and build processes to do typical development work, but you can extend or modify them from within Visual Studio or by using an XML editor.

In short MSBuild uses an XML-based project file format to retrieve the necessary configuration:

MSBuild.exe MyProj.proj /property:Configuration=Debug

You may consider reading Walkthrough: Creating an MSBuild Project File from Scratch to understand more about the MSBuild Project file (AKA MSBuild script). The error message I got when I tried to build an SSIS project file (.dtproj extension) earlier indicated that the XML format of an SSIS project file is not recognized by MSBuild.

SSISMSBuild and MSBuild script

There’s a codeplex project (Microsoft SQL Server Community Samples: Integration Services) where you can create Microsoft.SqlServer.IntegrationServices.Build.dll. You can also get an MSBuild script that links Microsoft.SqlServer.IntegrationServices.Build.dll from here: msbuild script for building and deploying a SSIS .ispac file. Below is the MSBuild script from that link.

<?xml version="1.0" encoding="Windows-1252"?>
<Project  xmlns="http://schemas.microsoft.com/developer/msbuild/2003"
          DefaultTargets="SSISBuild;SSISDeploy">
  <!--Requires a property called $(SSISProj) to be defined when this script is called-->
  <UsingTask TaskName="DeploymentFileCompilerTask" AssemblyFile="C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\Microsoft.SqlServer.IntegrationServices.Build.dll" />
  <Target Name="SSISBuild" Condition="'$(SSISProj)' != ''">
    <PropertyGroup>
      <SSISProjPath>..\$(SSISProj)\$(SSISProj).dtproj</SSISProjPath>
    </PropertyGroup>
    <Message Text="**************Building SSIS project: $(SSISProjPath) for configuration: $(CONFIGURATION)**************" />
    <DeploymentFileCompilerTask
      InputProject="$(SSISProjPath)"
      Configuration="$(CONFIGURATION)"
      ProtectionLevel="DontSaveSensitive">
    </DeploymentFileCompilerTask>
  </Target>

  <UsingTask TaskName="DeployProjectToCatalogTask" AssemblyFile="C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\Microsoft.SqlServer.IntegrationServices.Build.dll" />
  <Target Name="SSISDeploy" Condition="'$(SSISProj)' != ''">
    <Message Text="**************Publishing SSIS project: $(SSISProj) to: $(SSISServer) to folder: $(PROJECTNAME)**************" />
    <PropertyGroup>
      <ISPac>..\$(SSISProj)\bin\$(CONFIGURATION)\$(SSISProj).ispac</ISPac>
    </PropertyGroup>
    <DeployProjectToCatalogTask
          DeploymentFile="$(ISPac)"
          Instance="$(SSISServer)"
          Folder="$(PROJECTNAME)"
          CreateFolder="true"/>
  </Target>
</Project>

This MSBuild script performs two functions: SSISBuild and SSISDeploy. I saved the code to a file called “SSIS.MSBuild.proj” and executed MSBuild.exe illustrated below.

D:\App_temp\SSIS\DeployDemo\DeployDemo>MSBuild SSIS.MSBuild.proj /t:SSISBuild,SSISDeploy /p:SSISProj=”DeployDemo”,Configuration=”Development”,ProtectionLevel=”EncryptSensitiveWithPassword”,ProjectPassword=”1234″,SSISServer=”LEN-062\JY2012″,ProjectName=”DeployDemo”

image_thumb21

How to build Microsoft.SqlServer.IntegrationServices.Build.dll

I will explain how to compile Microsoft.SqlServer.IntegrationServices.Build.dll using the source code from the codeplex project (Microsoft SQL Server Community Samples: Integration Services). I’d like to mention the small challenges that I faced while trying to compile the source code.

  1. Prerequisites: The following should be installed on a machine that you are going to use.
    1. MS SQL Server 2012
    2. Visual studio 2010
  2. Download source code
    http://sqlsrvintegrationsrv.codeplex.com/SourceControl/latest
    image_thumb23
  3. Unzip downloaded file
  4. Go to SSISMSBuild project folder (sqlsrvintegrationsrv-100610\main\SSISMSBuild).
    There are two projects under SSISMDBuild, 2008 and Project. Project is for 2012 is C:\sqlsrvintegrationsrv-100610\main\SSISMSBuild\Project
    SSIS Junkie mentioned SSISMSBuild is included in MSBuild extension pack. However, that integration is done only for a 2008 project.
  5. Reference hint path modification
    Open C:\sqlsrvintegrationsrv-100610\main\SSISMSBuild\Project\Microsoft.SqlServer.IntegrationServices.Build.csproj from the text editor.image_thumb26Change the highlighted part to full path.image_thumb29
  6. Open C:\sqlsrvintegrationsrv-100610\main\SSISMSBuild\Project\Microsoft.SqlServer.IntegrationServices.Build.csproj using visual studio.
    If you try to compile it, it will generate an error due to a missing key file. You can see the following line from the project file.
    image_thumb30
  7. Sign an AssemblyWith the project node selected in Solution Explorer, from the Project menu, click Properties (or right-click the project node in Solution Explorer, and click Properties). In the Project Designer, click the Signing tab. Then you will see the following screen.
  8. Choose new and provide file name and uncheck Protect my key file with a password.

    Check Signing an Assembly in Visual Studio for more details
  9. Compile
  10. Copy created Microsoft.SqlServer.IntegrationServices.Build.dll file to the following folder
    C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies

Summary

It took me quite a while to figure this out and I hope this can save you time when setting up an automated build process using MSBuild. I think it would be great if MSBuild adds support for SSIS projects by default in the future.

Advertisements

49 Responses to “Building SSIS 2012 using MSBuild – A Journey to DB deployment automation”

  1. Kumar said

    Great work …

    • Rama said

      Hi Indeed a great article. But I am facing one issue with downloaded code C:\sqlsrvintegrationsrv-100610\main\SSISMSBuild\Project\Microsoft.SqlServer.IntegrationServices.Build.csproj is not opening in Visual Studio 2010. It is giving Unavailable error event after multiple reloads. Please help if you can.

  2. […] Building SSIS 2012 using MSBuild […]

  3. Simon Cho said

    It’s really great!!. I was looking for this kind of issue for a while.

  4. wsalzman said

    This is great work. Any insight in what I would need to do to tie this together with a TFS Build Server (project build definition help)?

    • kulmam92 said

      Hi Wsalzman

      I implemented using Jenkins. I used the following two steps to create a build. I think TFS will be similar.

      1. copy SSIS.MSBuild.proj file(from the “SSISMSBuild and MSBuild script” section) to the build folder
      copy /y c:\build\scripts\SSISMSBuild\SSIS.MSBuild.proj .\

      I keep SSIS.MSBuild.proj file in a folder(c:\build\scripts\SSISMSBuild) and copy it to the project folder since that same build script will be used to create a SSIS build.

      2. build a visual studio project or solution using MSBuild
      * MSBuild Version –> MSBuild32
      * MSBuild Build File –> SSIS.MSBuild.proj
      * Command Line Arguments –> /t:SSISBuild /p:SSISProj=%SSIS_project_name%,Configuration=Development,ProtectionLevel=EncryptSensitiveWithPassword,ProjectPassword=%SSIS_protection_password%,DeployPackageName=%JOB_NAME%

      I hope this helps

  5. […] I mentioned in my previous post (Building SSIS 2012 using MSBuild), MSBuild doesn’t build SSIS by default. So you need to install SQL Data Tools from the […]

  6. […] I mentioned in my previous post (Building SSIS 2012 using MSBuild), MSBuild doesn’t build SSIS by default. So you need to install SQL Data Tools from the […]

  7. Mark said

    Thank you for compiling this informative post. It saved me a lot of time with my automated SSIS 2012 build!

  8. […] I mentioned in my previous post (Building SSIS 2012 using MSBuild), MSBuild doesn’t build SSIS by default. So you need to install SQL Data Tools from the […]

  9. feat said

    Please let me know if you’re looking for a author for your weblog.
    You have some really great articles and I feel I would be a good asset.
    If you ever want to take some of the load off, I’d love to write
    some material for your blog in exchange for a link back to mine.

    Please blast me an email if interested. Thank you!

  10. David said

    Thanks so much. Great article. I found the referenced DLLs under C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\PrivateAssemblies rather 10.0.

  11. I’m now not sure where you are getting your information, but good topic.
    I needs to spend a while studying much more or working out more.

    Thank you for magnificent info I used to be on the lookout for
    this information for my mission.

  12. marvin said

    i’m trying to build this in TFS i’m getting:

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

    any ideas

  13. marvin said

    when trying to build in TFS, i get:

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

  14. marvin said

    I’m trying to build the project in TFS using MSBuild but I’m getting the following error:
    Could not load file or assembly ‘Microsoft.SqlServer.DTSRuntimeWrap, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified
    at Microsoft.SqlServer.Dts.Runtime.Project..ctor(IProjectStorage storage)
    at Microsoft.SqlServer.Dts.Runtime.Project.CreateProject(IProjectStorage storage)
    at Microsoft.SqlServer.IntegrationServices.Build.DeploymentFileCompilerTask.Execute()

    Does this mean that SSDT wasn’t installed or not installed correctly? I’m using SSIS 2012 on VS 2012. I’ve placed the *.build.DLL in C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\PrivateAssemblies

    • kulmam92 said

      Hi Marvin

      Can you explain more about your set up and environment?
      1. Are you trying to build SSIS project on build machine using MSBuild using the Microsoft.SqlServer.IntegrationServices.Build.dll?
      1) Can you list up components that you installed on a build server?
      2. Are you trying to build SSIS project on your local dev machine?

      • marvin said

        1.) Yes, I had to build the SSISMSBUILD project on the remote TFS Server first. To get the project to build I had to copy the DLLS pointing to the VS Private assemblies folder into the project and reference them there to get it to build on the server. Once TFS outputted the *.build dll, i placed it in C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\PrivateAssemblies on the TFS server.
        1) VS Ultimate 2010 SP1,
        VS 2012 Update 4,
        VS 2013 Update 4
        Microsoft SQL Server Data Tools – enu (11.1.41025.0
        Microsoft SQL Server Data Tools – enu (12.0.50512.0)
        Microsoft SQL Server Data Tools 2012 11.4.410250.0
        Microsoft SQL Server Data Tools 2013 12.050512.0
        Microsoft SQL Server Data Tools Build Utilities enu (11.1.20627.00)
        Microsoft SQL Server Data Tools Build Utilities enu (12.0.30919.1)
        2.) I’m trying to build the SSIS project on a remote machine. Locally i can run the command line and it builds fine.

      • kulmam92 said

        Marvin

        It seems like you were able to create Microsoft.SqlServer.IntegrationServices.Build.dll and placed it in C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\PrivateAssemblies of your build server. However, you had issue when you are trying to build SSIS project using MSBuild script.
        When install Data Tools, did you use SQL Data Tools from the SQL2012.ISO? Did you verify the ddls reference in the sqlsrvintegrationsrv-100610\main\SSISMSBuild exist in the build server? Can you share your build script?

      • marvin said

        -We used visual studio’s update mechanism to do all updates.

        Here is the build script portion for building the *build.dll. Actually VS was installed in the D drive instead of the C drive. The *build.dll was place in the path in the D drive.

        False
        D:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\PrivateAssemblies\Microsoft.AnalysisServices.Project.DLL

        False
        D:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\PrivateAssemblies\Microsoft.DataTransformationServices.VsIntegration.DLL

        False
        D:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\PrivateAssemblies\Microsoft.DataWarehouse.VsIntegration.DLL

        False
        C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\v4.0_11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ManagedDTS.dll

        An interesting note. The error about “Microsoft.SqlServer.DTSRuntimeWrap.dll” from the original post. I’ve found that it exists in both GAC_32 and GAC_64 folder paths in my local machine (where SSIS building works) while one the remote tfs/build server its only on GAC_32. Do i need to resolve that? If so, how?

      • marvin said

        looks like the xml was removed from my comment, however it left in place the paths to the DLLs. Is this okay?

      • kulmam92 said

        Based on you last comment It seems like you have issue when building sqlsrvintegrationsrv-100610\main\SSISMSBuild project in build server. Am I correct? If so, did you try to build that project using visual studio installed on a build server? If that doesn’t work, I think you need to find out what’s the difference between build server and your local dev machine. The GAC_32 and GAC_64 can be a good starting point. However I don’t know the solution. Based on explanation from this link (http://discuss.joelonsoftware.com/default.asp?dotnet.12.383883.5), it seems like both should exist on 64bit OS.
        FYI, when I set up a build server, I created Microsoft.SqlServer.IntegrationServices.Build.dll on my desktop, copied to the build server and created base image for the build server.

      • marvin said

        sorry for the confusion. I don’t have a problem with building Microsoft.SqlServer.IntegrationServices.Build.dll. I have a problem building the SSIS Project. Visual Studio is installed on the build server. I’ll see if i need to run the ISO to installed Data Tools instead of using Visual Studio update mechanism.

      • kulmam92 said

        I installed the following on the build server.
        1. SQL Data Tools from the SQL2012.ISO
        2. VS 2012 with SSDT
        3. Jenkins
        4. MSBuild Extension
        5. SSISMSBuild.dll

  15. Great article. I followed all steps and created the package – without auto deploy. but when trying to deploy it manually to our server running SQL2012, i get the following error”

    TITLE: SQL Server Integration Services
    ——————————

    The package failed to load due to error 0xC0011008 “Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.”. This occurs when CPackage::LoadFromXML fails.
    (CBS_Extract)

    I checked all existing solutions on internet but none of them actually worked. Anyone has had this issue before? any solution?

    Thanks
    Ethan

  16. […] involves using a community MSBuild extension and a bunch of other bits, you can read more about it here or here, you can find the MSBuild extension on CodePlex. I tried this method, but didn’t […]

  17. delish1204 said

    This is great. It has simplified my deployments considerably. Unfortunately it appears that the build does not use the configuration switch/value. It seems that my projects are being built with the last configuration the project was saved with and not the value I’m passing to MSBuild. I was hoping to use the configuration functionality to appropriately set my SSIS project parameter values. Have you been able to confirm that the MSBuild project and assemblies actually use the configuration value? My project definition is below:

    %(AllFiles.FileName)

  18. ujwal said

    Ethanferdosi

    I also encountered the same problem as your. Did you find something that solves the “LoadFromXML fails” problem?

    Thanks
    ujwal

    • InkZ said

      Hi,

      I had this error and I think it’s because I had a SQL 2014 project but the references are all for older versions of SQL. I made the following changes and had to compile in VS2015:

      $(VS140COMNTOOLS)\..\IDE\PrivateAssemblies\Microsoft.AnalysisServices.Project.DLL
      $(VS140COMNTOOLS)\..\IDE\PrivateAssemblies\Microsoft.DataTransformationServices.VsIntegration.DLL
      $(VS140COMNTOOLS)\..\IDE\PrivateAssemblies\Microsoft.DataWarehouse.VsIntegration.DLL
      C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\v4.0_13.0.0.0__89845dcd8080cc91

      I also had to change the target framework to 4.6.1.

      • rkibbe said

        Thanks so much. Great article but somewhat painful to implement. I had to do the same with VS2015. Finally changing target framework to 4.6.1 worked for me. I wish someone would post the compiled dll somewhere.

  19. I am working in a mixed environment. TFS2012 on the server. My workstation only has VS2015 and SSDT for SQL Server 2014. The references section needs DLLs delivered with VS2015 on my machine, which in turn require .NET Framework 2.6.

    My question is If I compile against .NET Framework 2.6 and install that same version .NET Framework on the TFS2012 Server, will it work? I don’t want to install the updated version of .NET Framework on the server if someone already knows it won’t work.

  20. […] Building SSIS 2012 using MSBuild – A Journey to DB deployment automation […]

  21. Karthick said

    When I use a third party connector, this won’t work. Can anyone help

    • Karthick said

      Here is the error I get in that case.
      Loading ConnectionManager ‘C:\MSD_CRM\Workspace\ETL\DemoSSISBuild\DemoSSISBuild\Dynamics CRM Connection Manager.conmgr’
      C:\MSD_CRM\Workspace\SSIS.MSBuild.proj(11,5): error : Unable to create the type with the name ‘DynamicsCRM’.\r

  22. […] could do a lot too (SSIS, SSRS, […]

  23. […] I mentioned in my previous post (Building SSIS 2012 using MSBuild), MSBuild doesn’t build SSIS by default. So you need to install SQL Data Tools from the […]

  24. Meera said

    i am getting while doing msbuild.exe i use same thing which you did. getting error like “deployProjectToCatalogtask” task wasnot given a value for the required parameter “Folder”

    C:\demo_deployemnt_teamcity\Demo_getdate\Demo_getdate\Demo_getdate.dtproj

    ..\$(SSISProj)\bin\$(CONFIGURATION)\$(SSISProj).ispac

    and msbuild

    C:\Demo_getdate\Demo-getdate>MSBuild SSISBUILD.proj /t:SSISBuild,SSISDeploy /p:SSISProj=”Demo_getdate”,Configuration=”Development”,ProtectionLevel=”DontSaveSensitive”,SSISServer=”LocalHost″,ProjectName=”Demo_getdate”

  25. Meera said

    When we do msbuild it create ispac file right?
    i use following file but didnt create ispac file

    C:\demo_deployemnt_teamcity\Demo_getdate\Demo_getdate\Demo_getdate.dtproj

    MSBUILD SSISBUILD.proj \tSSISBuild \p:SSISProj=”Demo_getdate”,Configuration=”Developement”

  26. Nice write up; helped me get this working quicker than if I’d just been going at it from scratch myself.

  27. Fiodar Hulin said

    Hi
    I have SQL 2014 on localhost ( W10 )
    ( All SSAS-SSIS-SSRS run OK )

    VS 2017 where I build Microsoft.SqlServer.IntegrationServices.Build.dll OK
    (change refs to dll of SQL 2014 )

    I added in GAC 4 refs :
    “C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6.1 Tools\gacutil.exe” -I “C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\PrivateAssemblies\Microsoft.DataWarehouse.DLL”
    “C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6.1 Tools\gacutil.exe” -I “C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\PrivateAssemblies\Microsoft.DataTransformationServices.VsIntegration.DLL”

    “C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6.1 Tools\gacutil.exe” -i “C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\PrivateAssemblies\Microsoft.DataWarehouse.VsIntegration.DLL”
    “C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6.1 Tools\gacutil.exe” -i “C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\PrivateAssemblies\Microsoft.AnalysisServices.Project.DLL”

    “C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6.1 Tools\gacutil.exe” -i “C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\PrivateAssemblies\Microsoft.SqlServer.IntegrationServices.Build.dll”

    but still get error :
    Could not load file or assembly ‘Microsoft.SqlServer.DTSRuntimeWrap, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its depe
    ndencies

    though “C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6.1 Tools\gacutil.exe” /l > a_cache.txt
    I see
    Microsoft.SqlServer.DTSRuntimeWrap, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=x86
    This is 32 bit

    I wonder if it can be connected with 32-64 bit
    Should I build Microsoft.SqlServer.IntegrationServices.Build.dll Any CPU like it in project
    or may be to x86 ?

  28. Fiodar Hulin said

    Solution : I need to build C# project on .Net 4.5 ( Not latest one on my PC) but that version on which dlls in project were built

    The Key was Warning :

    Warning The primary reference “Microsoft.AnalysisServices.Project” could not be resolved because it was built against the “.NETFramework,Version=v4.5” framework. This is a higher version than the currently targeted framework “.NETFramework,Version=v4.0”. Microsoft.SqlServer.IntegrationServices.Build

    I need to choose exact version of .Net : 4.5 ( not latest ones 4.6.1 which I did at first – because dll was built OK but failed when running MS build )

  29. Fiodar Hulin said

    Upd : Skip previous message
    .Net version does’not matter here

    C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.ManagedDTS.dll
    version 13

    Other used Dlls should be from \130\SDK folder ( version 14 ) !!!

  30. […] could do a lot too (SSIS, SSRS, […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: