Azure SSIS Deploying in SQL Azure

Here it is: the failing login in question was a SQL Server login. Here is how we configured the SQL connection manager properties for old, new, and developer environments:

• ServerName - MyServer
• InitialCatalog - MyDB
• UserName - MyUser
• Password - ***********
• ConnectionString - Data Source=MyServer;User Id=MyUser;Initial Catalog=MyDB;Provider=SQLNCLI11;Persist Security Info=True;Auto Translate=False;

While this worked perfectly on the old SQL 2008 environment, and it was no problem from Visual Studio, it failed during simultaneous database operations from the SQL 2012 Integration Services Catalog, but started working when we added Password=****** to the connection string.

• ConnectionString - Data Source=MyServer;User Id=MyUser;Password=********;Initial Catalog=MyDB;Provider=SQLNCLI11;Persist Security Info=True;Auto Translate=False;

It would seem that if a second simultaneous connection is being opened while in the SQL 2012 Integration Services Catalog execution environment, only the connection string itself (of the initial connection?) is consulted, not Password or other constituent properties of the connection manager. Therefore, Password has to be in the connection string.

DTSInstaller.exe consumes a file called with an extension of SSISDeploymentManifest

The contents of it look like

<?xml version="1.0"?> 
<DTSDeploymentManifest 
    GeneratedBy="HOME\administrator"
    GeneratedFromProjectName="SSISHackAndSlash"
    GeneratedDate="2010-04-30T21:19:08.6336677-05:00"
    AllowConfigurationChanges="true">
        <Package>Package.dtsx</Package>
        <Package>Package2.dtsx</Package>
        <Package>Package7.dtsx</Package>
</DTSDeploymentManifest>

That file defines the universe of SSIS packages the DTSInstaller will operate upon.

It sounds like the manifest file you are using does not have match the list of packages in your project. You can right click on the project itself and select Properties, Deployment tab and change the value of CreateDeploymentUtility from false to true.

Now when you right click on your project to build, or deploy, your bin\Deployment folder will contain a new version of the manifest file. Double click on that and you should have all the desired packages ready for installation.

An alternate approach, would be to use your manifest file but have PowerShell generate the deploy commands. Advantage of that is you can audit what's being deployed versus the black box of DTSInstaller.exe PowerShell, dtutil, SSISDeployManifest - How to deploy SSIS packages with PowerShell