SSIS Deployment

Add-in Express™ Support Service
That's what is more important than anything else

SSIS Deployment
Installing a Package 
Darren Dickerson




Posts: 17
Joined: 2014-09-24
I need just a little bit of help trying to figure out how to run some code in my setup that deploys and SSIS package. Can somebody give me a place to start? I have code that does it. I just need to know how to kick it off.
Posted 10 Oct, 2014 09:03:26 Top
Andrei Smolin


Add-in Express team


Posts: 14115
Joined: 2006-05-11
Hello Darren,

Sorry, I don't know what is SSIS. Could you please explain?

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 10 Oct, 2014 11:07:50 Top
Darren Dickerson




Posts: 17
Joined: 2014-09-24
Sorry, I should not have been so specific. I just need to run some custom code as part of the install. I probably need to run custom code as part of a roll back or uninstall. Is there an example somewhere of running custom code as part of an install?
Posted 10 Oct, 2014 11:16:09 Top
Dmitry Kostochko


Add-in Express team


Posts: 2731
Joined: 2004-04-05
Hi Darren,

I think you can use .NET custom actions. Please have a look at the following MSDN articles:
Custom Actions
Walkthrough: Creating a Custom Action
Posted 10 Oct, 2014 11:21:05 Top
Darren Dickerson




Posts: 17
Joined: 2014-09-24
Thanks Dimitry.

Is there a walkthough using the custom action editor in Designer for Wix Toolset?
Posted 10 Oct, 2014 11:34:55 Top
Dmitry Kostochko


Add-in Express team


Posts: 2731
Joined: 2004-04-05
Hi Darren,

The visual editors of the "Designer for WiX Toolset" product are very similar to the old good Visual Studio editors. I think that the steps described the above mentioned walkthrough can be used in our product without any modifications.
Posted 10 Oct, 2014 11:53:43 Top
Darren Dickerson




Posts: 17
Joined: 2014-09-24
Thanks Dimitry,

I ended up going with this as an example: http://blogs.msdn.com/b/jschaffe/archive/2012/10/23/creating-wix-custom-actions-in-c-and-passing-parameters.aspx

My setup won't install an EXE or anything. How do I add my Custom Actions project without it installing the DLL?

Edit:
I added the project output of my Custom Actions Project. The DLLs come over fine but the SSIS Package files and my SQL Scripts don't come over to the setup. I have them defined as content and they end up in the output folder.

Hmmm... My Custom Action DLL didn't come across either.
Posted 14 Oct, 2014 09:12:43 Top
Dmitry Kostochko


Add-in Express team


Posts: 2731
Joined: 2004-04-05
Hi Darren,

Sorry, I am not familiar with that MSDN example and I have never tried using Custom Actions of that type.

I believe I can create an example for you if you give me a list of your main requirements including:
- Versions of Visual Studio and WiX.
- Setup project type: per-machine or per-user.
- What shall be included in the setup: simple files, executables, libraries, icons, shortcuts, etc.
- What UI screens/dialogs shall be included in the setup package.
- Custom actions: dll or exe? on which steps they shall be executed (Install/Commit/Rollback/Uninstall)?
- Registry keys and values?
- Something else?
Posted 14 Oct, 2014 11:24:12 Top
Darren Dickerson




Posts: 17
Joined: 2014-09-24
Oh really? Awesome! Thank you!

Visual Studio 2012
Wix 3.8
Per Machine

Nothing will be installed like an exe or a shortcut or anything. Two databases will be installed optionally, a maintenance plan optionally and a service optionally.

I think I need a UI screen for the SQL server and login.

Options:

SQL Server Databases (2 of them)
Maintenance Plans (Custom Action)
Title Archive Folder (Create and Share a folder)
Service
.Net 4.5 Required if not installed (Only if installing the Service)

The service depends on a couple of Entity Frameworks 6 DLLs. DotNet 4.5 would be required if the service is installed.


I think the Custom Action for the SSIS package looks like this:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using Microsoft.Deployment.WindowsInstaller;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.IO;
namespace FloorPlanServerCustomAction
{
    public class CustomActions
    {
        [CustomAction]
        public static ActionResult InstallMaintenancePlan(Session session)
        {
            try
            {
                session.Log("Begin InstallMaintenancePlan");

                //session["DB_SERVER"]
                //session["DB_DATABASE"]
                //session["DB_USER"]
                //session["DB_PASSWORD"]
                //session["INSTALL_DIR"]

                Package p = null;
                Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

                p = app.LoadPackage(Path.Combine(session["INSTALL_DIR"], "FloorPlanServerSQLDiffBackup.dtsx"), null);
                app.SaveToSqlServerAs(p, null, "\Maintenance Plans\" + p.Name, session["DB_SERVER"], null, null);

                p = app.LoadPackage(Path.Combine(session["INSTALL_DIR"], "FloorPlanServerSQLFullBackup.dtsx"), null);
                app.SaveToSqlServerAs(p, null, "\Maintenance Plans\" + p.Name, session["DB_SERVER"], null, null);

                p = app.LoadPackage(Path.Combine(session["INSTALL_DIR"], "FloorPlanServerSQLXLogBackup.dtsx"), null);
                app.SaveToSqlServerAs(p, null, "\Maintenance Plans\" + p.Name, session["DB_SERVER"], null, null);

                string script = String.Empty;

                ServerConnection serverConnection = new ServerConnection();
                serverConnection.ServerInstance = session["DB_SERVER"];
                serverConnection.LoginSecure = false;

                serverConnection.Login = session["DB_USER"];
                serverConnection.Password = session["DB_PASSWORD"];

                Server sqlserver = new Server(serverConnection);

                script = File.ReadAllText(Path.Combine(session["INSTALL_DIR"], "FloorPlanServerSQLDiffBackup.Subplan_1.sql"));
                sqlserver.ConnectionContext.ExecuteNonQuery(script);
                script = File.ReadAllText(Path.Combine(session["INSTALL_DIR"], "FloorPlanServerSQLFullBackup.Subplan_1.sql"));
                sqlserver.ConnectionContext.ExecuteNonQuery(script);
                script = File.ReadAllText(Path.Combine(session["INSTALL_DIR"], "FloorPlanServerSQLXLogBackup.Subplan_1.sql"));
                sqlserver.ConnectionContext.ExecuteNonQuery(script);

                return ActionResult.Success;
            }
            catch (Exception ex)
            {
                session.Log("ERROR in custom action InstallMaintenancePlan {0}", 
                    ex.ToString());
                return ActionResult.Failure;
            }
        }
    }
}


I think I am finally starting to figure some of this out.

I figured out the content problem.
Posted 14 Oct, 2014 11:35:27 Top
Dmitry Kostochko


Add-in Express team


Posts: 2731
Joined: 2004-04-05
Hi Darren,

Thank you for the details, I got the main idea and will try to create a sample for you. Naturally, my sample will not create SQL databases or maintenance plan, but will contain custom actions and methods where you will be able to put your own code.

This will take some time, I will let you know when the sample is ready.
Posted 15 Oct, 2014 08:26:44 Top