Wednesday 31 December 2014

Add trailing zeros

Case
I have a currency €25,10 but when I put it in a Flat File destination it removes all trailing zero's after the decimal symbol: 25,10 becomes 25,1 and 24,00 becomes 24. How can I prevent that?

Solution
The solution is easy. Just cast it to a numeric with the scale set to 2. Other datatypes remove the trailing zeros, but numeric seems to do the trick: (DT_NUMERIC,5,2)myNumber
CAST to get trailing zeros

























For adding leading zero check this post.

Friday 14 November 2014

Create Windows Service to watch files for SSIS

Case
I want to use the WMI Event Watcher Task to watch for new files in SSIS, but it doesn't allow me to watch subfolders and after catching an event it continues with the next task, but meanwhile it doesn't watch for new files until in starts again. Is there an alternative?
WMI Event Watcher Task






















Solution
An alternative could be to create a Windows Service that does the watching part and then executes a package when a new file arrives. For this example I used the full version of Visual Studio 2010 (SSDT BI is not enough). The example is in C#, but you can easily convert that to VB.NET.

1) Windows Service Project
Start Visual Studio and create a new Windows Service Project. I used the 4.0 framework since I want to execute SSIS 2012 packages. For 2008 you can use 3.5.
Windows Service 4.0 C#




















2) App.Config
To avoid hardcoded connection strings, paths and filters, we first have to add an Application Configuration File (App.config). Right Click the project in the solution explorer, choose Add, New Item... and then Application Configuration File. After adding the app.config we need to edit it. You can copy the XML below to start with and add extra parameters at the end. Make sure the values are correct.
app.config


















<?xml version="1.0" encoding="utf-8" ?>
<configuration>
 <appSettings>
  <add key="WatcherPath" value="d:\MySourceFiles"/>
  <add key="WatcherFilter" value="*.csv"/>
  
  <add key="SqlConnectionString" value="Data Source=.\SQL2012;Initial Catalog=master;Integrated Security=SSPI;"/>
  <add key="Catalog" value="SSISDB"/>
  <add key="Folder" value="MyFolder"/>
  <add key="Project" value="MyProject"/>
  <add key="Package" value="MyPackage.dtsx"/>

  <add key="LogFile" value="d:\FileWatcherForSSIS\log.txt"/>
 </appSettings>
</configuration>
MyCatalogStructure












3) Add References
Right click references in the Solution Explorer and choose Add Reference... For the configuration part we need to add two extra references that can be found in the .NET tab:
- System.Configuration.dll
- System.Configuration.Install.dll
Add References























For executing the SSIS packages we need four extra references that can be found in the GAC (MSIL) folder. The exact place could vary, but here are mine for SSIS 2012. Use the browse tab in the Add Reference Window to add them.
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfo.dll
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Sdk.Sfc\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Sdk.Sfc.dll
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.IntegrationServices.dll


4) Services1.cs
Now open the source code of Services1.cs to add the actual Windows Services code. In the grey designer surface press F7 or click on the link to switch to code view. Copy the code below. If you don't want to use the Project Deployment Model then you must slightly change the code in the watcher_Created method.
// C# code
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel; // Added
using System.ComponentModel;
using System.Configuration;           // Added
using System.Data;
using System.Data.SqlClient;          // Added
using System.Diagnostics;
using System.IO;                      // Added
using System.Linq;
using System.ServiceProcess;
using System.Text;
using Microsoft.SqlServer.Management.IntegrationServices;  // Added

namespace FileWatcherForSSIS
{
  public partial class Service1 : ServiceBase
  {
    public Service1()
    {
      InitializeComponent();
    }

    /// <summary>
    /// Method that executes when the services starts
    /// </summary>
    /// <param name="args"></param>
    protected override void OnStart(string[] args)
    {
      // Create FileSystemWatcher dynamically instead of via the designer
      FileSystemWatcher watcher = new FileSystemWatcher();

      // Determine the folder you are watching
      watcher.Path = ConfigurationManager.AppSettings["WatcherPath"];

      // Determine for which files to watch
      watcher.Filter = ConfigurationManager.AppSettings["WatcherFilter"];

      // Determine whether you also want to watch subfolders
      watcher.IncludeSubdirectories = true;

      // Determine for which changes to watch (multiple notify
      // filters should be seperated by a pipeline |
      watcher.EnableRaisingEvents = true;
      //watcher.NotifyFilter = NotifyFilters.CreationTime;

      // Determine what to do for which events
      watcher.Created += new FileSystemEventHandler(watcher_Created);

      // Log start of service
      logText("FileWatcher started watching " + watcher.Path);
    }

    /// <summary>
    /// Method that executes when the services stops
    /// </summary>
    protected override void OnStop()
    {
      // Log start of service
      logText("FileWatcher stopped watching ");
    }

    // Define the event handlers. 
    private static void watcher_Created(object sender, FileSystemEventArgs e)
    {
      // Log start of service
      logText("FileWatcher catched " + e.FullPath);

      // Connection to the database server where the packages are located
      using (SqlConnection ssisConnection = new SqlConnection(ConfigurationManager.AppSettings["SqlConnectionString"]))
      {
        try
        {
          // SSIS server object with sql connection
          IntegrationServices ssisServer = new IntegrationServices(ssisConnection);

          // Get values from app.config
          string catalog = ConfigurationManager.AppSettings["Catalog"];
          string folder = ConfigurationManager.AppSettings["Folder"];
          string project = ConfigurationManager.AppSettings["Project"];
          string package = ConfigurationManager.AppSettings["Package"];

          // The reference to the package which you want to execute
          PackageInfo ssisPackage = ssisServer.Catalogs[catalog].Folders[folder].Projects[project].Packages[package];

          // Create collection of parameters
          Collection<PackageInfo.ExecutionValueParameterSet> executionParameter = new Collection<PackageInfo.ExecutionValueParameterSet>();

          // Add a package parameter
          executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 30, ParameterName = "MyFilePathParameter", ParameterValue = e.FullPath });

          // Add a logging level parameter: 1=basic, 2=performance
          executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "LOGGING_LEVEL", ParameterValue = 1 });

          // Execute package asynchronized and get
          // the identifier of the execution
          long executionIdentifier = ssisPackage.Execute(false, null, executionParameter);

          // Prevent 30 second time-out by getting the execution
          // by its ID and then wait until it's completed
          ExecutionOperation executionOperation = ssisServer.Catalogs[catalog].Executions[executionIdentifier];

          // Wait while package is busy
          while (!(executionOperation.Completed))
          {
            // Refresh and wait 5 seconds before retesting
            executionOperation.Refresh();
            System.Threading.Thread.Sleep(5000);
          }

          // Log package completion
          logText("Package completed " + package);
        }
        catch (Exception ex)
        {
          logText("FileWatcher error " + ex.Message);
        }
      }
    }

    /// <summary>
    /// Write message to log
    /// </summary>
    /// <param name="logText">the log message</param>
    private static void logText(string logText)
    {
      // Simple log version. You can replace it by for example an eventlog writer
      using (FileStream fs = new FileStream(ConfigurationManager.AppSettings["LogFile"], FileMode.Append, FileAccess.Write))
      using (StreamWriter sw = new StreamWriter(fs))
      {
        sw.WriteLine(DateTime.Now.ToString() + " - " + logText);
      }
    }
  }
}


5) Installer.cs
To make the Windows Services installable we need to add an Installer Class by right clicking the Solution Explorer and choose Add, New Item..
Installer.cs


















6) Add Service(Process)Installer
Next you can drag ServiceInstaller and ServiceProcessInstaller from the toolbox to the designer surface of install.cs. If they are not available in the toolbox then you need to add them manually by right clicking the toolbox and choose Choose Items... (just like in SSIS 2008 when adding Third Party tasks).
Service(Process)Installer


















7) Edit Service(Process)Installer
In the properties of serviceInstaller1 you can set values for properties like ServiceName, DisplayName and the Description of the service.
Service Properties


























8) Building & Deploying
Now build the project in release mode (via build menu or Ctrl + Shft + B) and go to the bin release folder of your project. Copy these files to a suitable location for the service.
Bin Release Folder
















9) InstallUtil
Now open the Visual Studio 201X command prompt as Administrator (otherwise you don't have rights to add a new service). In the command prompt go to the folder where the service is located.
Then enter the following command: installutil FileWatcherForSSIS.exe. During installation it will ask for the account that runs the service. The user must be able to read the folder and to execute the package (just like the user in a SQL Server Agent jobstep). For your production server without Visual Studio (and without installutil.exe) you need to create a setup project in Visual Studio to install your new Windows Services.
Visual Studio Command Prompt (run as Administrator)















10) Start Service and test
Now go to your local services and start the new Windows Service. Then start watch the log while adding a new file in the folder that you are watching.
The new Windows Service

















Log file











Download Visual Studio 2010 Example Project

Note: This Windows Service must be installed on the same machine as SSIS.

Saturday 11 October 2014

Method not found: IsVisualStudio2012ProInstalled()

Case
I just installed SSDT 2012 on top of SSIS 2012 with SSDT 2010, but when I want to run a package I get an error:

Method not found: 'Boolean Microsoft.SqlServer.Dts.Design.VisualStudio2012Utils.IsVisualStudio2012ProInstalled()'.
















Solution
Apparently the Microsoft.SqlServer.Dts.Design dll belonging to SSDT 2012 is not installed in the GAC during installation. The version belonging to SSDT 2010 is still in the GAC. On the left side the assembly before fixing this bug and on the right side the assembly after fixing this bug. Ironically the assembly from 2012 has a lower version than 2010. Dave found a solution for it.
Microsoft.SqlServer.Dts.Design dll before and after fixing the bug.

















1)  Close SSDT 2012
Close Visual Studio (SSDT) 2012.

2) Visual Studio Command Prompt
Open the Visual Studio Command Prompt as administrator (otherwise the gacutil will return an error). You can do this by right clicking the shortcut and then choose Run as Administrator.

3) Change Directory
Enter the following command to change the direcory to the PrivateAssemblies folder of SSDT 2012:
cd C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\PrivateAssemblies

4) Run GACUTIL
Run GACUTIL with the following parameters to add the assembly from SSDT 2012 to the GAC:
gacutil/if Microsoft.SqlServer.Dts.Design.dll
Parameter /if will force the installation of this assembly regardsless of any existing versions of the assembly
gacutil/if Microsoft.SqlServer.Dts.Design.dll

















5) Open SSDT 2012
Now open SSDT 2012 and try running the package again to check the fix.


*UPDATE (see comment Alejandro Bello)*
If you didn't install SSDT 2010 then the GAC Util is located in a different folder. Start the regular Command Prompt as Administrator and then execute the following commands:

cd "C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0a\bin\NETFX 4.0 Tools"

gacutil.exe /if "C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\PrivateAssemblies\Microsoft.SqlServer.Dts.Design.dll"

Different location GacUtil





Sunday 5 October 2014

SQL Saturday #336 Holland - Powerpointslides


Had a nice day at SQL Saturday #336 in Utrecht! The PowerPoint slides of my SSIS Development Best Practices session are available for download. I added some screens, text and URL's for additional information (see notes in PowerPoint)

Thursday 25 September 2014

'Auto Layout - Diagram' missing in Layout Toolbar

Case
I often use the 'Auto Layout - Diagram' option in the Format-menu to auto arrange my tasks or transformations, but it costs me three clicks (/moves). All other options from the Format-menu can be found in the Layout toolbar (one click only), except the Auto Layout - Diagram option. Is there a way to solve that for 'lazy' developers?
Three clicks instead of one









Solution
Yes there is! Screens are from VS2010, but it works the same in newer versions.

1) Show Layout toolbar
First make sure the Layout toolbar is visible. If it's not visible, rightclick the toolbar and select the Layout toolbar.
Show Layout toolbar



















2) Add Button
Click on the little triangle on the right site of the toolbar and choose 'Add or Remove Buttons'. After that choose 'Customize...'. Now the Toolbar Customize window will show.
Customize window














3) Add Command
Click on the Add Command button and choose Format as category and then locate the Diagram command. Click OK to add it and click Close to close the customize window.
Add command















4) The Result
Now the new button is available in the Layout toolbar and with one click you can auto arrange your package.
One click only :-)










Friday 12 September 2014

Foreach loop with *.xls wildcard also returns *.xlsx files

Case
I have a Foreach Loop Container with a file enumerator. The wildcard is *.xls, but it also returns *.xlsx files. How do I prevent that?

Loop through *.xls also includes xlsx files





















My xls loop includes xlsx and xlsm files


















Solution
This is actually similar to the DIR command in a DOS/Command Prompt.
All xls files? (/b is to remove my Dutch header/footer)











The workaround is simple. And if you don't like the solution then you could use my Sorted File Enumerator that also supports regular expression wildcards.

1) Dummy
Add an empty/dummy task or Sequence Container in your Foreach Loop Container. And connect it to your first task.

Empty/collapsed Sequence Container added

























2) Precedence Constraint Expression
Add an expression on the Precedence Constraint between the dummy and your first task. It should look something like LOWER(RIGHT(@[User::FilePath], 4)) == ".xls" (replace the variablename and/or file extension).

Expression with LOWER and RIGHT to check the file extension

















3) The result
Now test the package (Replace my example Script Task with your own tasks).
The result: only two xls files and no xlsx or xlsm files





















Monday 1 September 2014

XSD location hardcoded in XML source

Case
The path of the XSD file in the XML Source component is hardcoded, but the path on my production environment is different than my development environment. The XML source doesn't have expressions. How do I make this XSD path configurable?
XML Source Connection Manager Page
without Connection Managers



























Solution
Unfortunately the XML source component does lack some very basic functionalities like the use of a Connection Manager (although ironically it's called the Connection Manager page). The source can use variables as input, but there isn't such option for the XSD file.
XML Task can use Connection Managers


























1) Find Expressions
The XML Source Component doesn't support expressions, but the Data Flow Task itself does. Go to the properties of the Data Flow Task and locate the expressions and click on the ... button

Select Data Flow Task and press F4 to get properties


























2) Select Property
Locate the XMLSchemaDefinition property of your XML Source in the Property Expression Editor and add an expression on it by clicking on the ... button.
Property Expression Editor
















3) Add Expression
Now you can either replace its value by a variable or a parameter if you use SSIS 2012 and above.
Expression Builder
























That's it. Now you have a workaround for the absence of a real Connection Manager. An alternative could be to use a Script Component as XML source or an XML file with an inline XSD schema.

Friday 1 August 2014

Add Expression Builder to custom task

Case
I have created a custom task, but I would like to add the built-in Expression Builder to it. How do you do that?

Solution
It is possible, but it's an unsupported feature, which means you have no guarantees that it will still work after the next update of SSIS. For this example I used my Custom Task example and added two references and a couple of code lines. You can download that code and add the code below to the UI project.
Custom Task with built-in Expression Builder















1) References
For the Expression Builder window you need to add a reference to Microsoft.DataTransformationServices.Controls which is located in the GAC:
C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.DataTransformationServices.Controls\v4.0_11.0.0.0__89845dcd8080cc91\Microsoft.DataTransformationServices.Controls.DLL
And for validating the expression you need a reference to Microsoft.SqlServer.DTSRuntimeWrap which is also located in the GAC (however not in MSIL):
C:\Windows\Microsoft.NET\assembly\GAC_32\Microsoft.SqlServer.DTSRuntimeWrap\v4.0_11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.DTSRuntimeWrap.dll
 C:\Windows\Microsoft.NET\assembly\GAC_64\Microsoft.SqlServer.DTSRuntimeWrap\v4.0_11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.DTSRuntimeWrap.dll


2) Usings
I added two extra usings for the Expression Builder.

Two extra usings


















3) Controls
I added a button (for opening expression builder) a readonly textbox (for showing the expression) and a label (for showing the evaluated expression) to my editor.
Extra controls in the editor








4) The code
I added an onclick event on my button and added the following code (simplified version).
// C# code
private void btnExpression_Click(object sender, EventArgs e)
{
 try
 {
  // Create an expression builder popup and make sure the expressions can be evaluated as a string:
  // Or change it if you want boolean to System.Boolean, etc. Last property is the textbox containing
  // the expression that you want to edit.
  using (var expressionBuilder = ExpressionBuilder.Instantiate(_taskHost.Variables,
                 _taskHost.VariableDispenser,
                 Type.GetType("System.String"),
                 txtExpression.Text))
  {
   // Open the window / dialog with expression builder
   if (expressionBuilder.ShowDialog() == DialogResult.OK)
   {
    // If pressed OK then get the created expression
    // and put it in a textbox.
    txtExpression.Text = expressionBuilder.Expression;
    lblExpressionEvaluated.Text = "";

    // Create object to evaluate the expression
    Wrapper.ExpressionEvaluator evalutor = new Wrapper.ExpressionEvaluator();

    // Add the expression
    evalutor.Expression = txtExpression.Text;

    // Object for storing the evaluated expression
    object result = null;

    try
    {
     // Evalute the expression and store it in the result object
     evalutor.Evaluate(DtsConvert.GetExtendedInterface(_taskHost.VariableDispenser), out result, false);
    }
    catch (Exception ex)
    {
     // Store error message in label
     // Perhaps a little useless in this example because the expression builder window
     // already validated the expression. But you could also make the textbox readable
     // and change the expression there (without opening the expression builder window)
     lblExpressionEvaluated.Text = ex.Message;
    }

    // If the Expression contains some error, the "result" will be <null>.
    if (result != null)
    {
     // Add evaluated expression to label
     lblExpressionEvaluated.Text = result.ToString();
    }
   }
  }
 }
 catch (Exception ex)
 {
  MessageBox.Show(ex.Message);
 }
}

5) Runtime
Now you can store that expression in a property and retrieve in on runtime. On runtime you can evaluate the expression with the same code as above.

Tuesday 1 July 2014

Prevent events executing multiple times

Case
I have a log task in an OnPreExecute and OnPostExecute event handler, but it executes multiple times. Why is that and how can I prevent it?

Solution
This is because all tasks and containers fire events and these events are propagated to their parent container and then to their parent container and so on. This means that if you have a package with a Sequence Container with and with an Execute SQL Task in it, that each of them fires events. Let's test that.

For testing purposes I added an Execute SQL Task in each event handler with an insert query to show which events are fired. Each task inserts the name of the event and the value of the System Variable SourceName in a log table with an identity column LogId.
Log all events, executable is package



















When you run the package you can see that for example the OnPreExecute event has three records:
1) the Package
2) the Sequence Container
3) the Execute SQL Task
Event handlers executing multiple times



















The trick
The trick to execute the Execute SQL Task in the event handler(s) only once, is to check whether the source of the event is the package and not one of it's children (containers/tasks).

Add a dummy Script Task or an empty Sequence Container in front of the Execute SQL Task and add a Precedence Constrain expression between them: @[System::SourceName] ==  @[System::PackageName]
Expression to filter events that are not from the package




















Thursday 26 June 2014

Nested includes in BIML Script

Case
I want to use nested includes in a BIML Script (an include in an include), but the second level isn't working. It seems to skip it without giving an error.
No second Sequence Container













Solution
First be careful with (too many) nested includes! It could make your BIML script obscure. There are two tricks to solve this problem. They came to me via twitter from @cathrinew and @AndreKamman.

Solution A:
Use a full path in the include tag instead of only the name:
Using the fullpath













Big downside is of course the full path in your BIML Script. In a multi-user environment with for example TFS that could be an issue because everybody needs the same project path.

Solution B:
A better option is to use CallBimlScript instead of include:
Using CallBimlScript












And you could also pass parameters to the included file and use relative path and then reuse the file in multiple projects.

Friday 20 June 2014

SSIS 2012 Execute Package Task: External Reference does not work with SSISDB

Case
I have two SSIS projects (both project deployment) and I within project 'A' I want to execute a package from project 'B' with the Execute Package Task. Project reference won't work because there are two different projects, but External reference won't work either because it doesn't support packages from the SSISDB (only file or MSDB).
Pointing to MSDB instead of SSISDB























Solution
See (/vote) this MsConnect item. They are still considering to address this issue. Here is a workaround, but it requires a little coding. I while a go I did a post on executing an SSIS 2012 package from a .Net application and I thought that it would also be possible within an SSIS Script Task.

1) Connection Manager
Create an ADO.Net connection manager that points to the SSISDB on your server.
ADO.Net connection (don't use OLEDB)























2) String variable
Create a string variable and add the path from the package that you want to execute. Format is /SSISDB/folder/project/package.dtsx
String variable filled with package path
















3) Script Task
Add a Script Task to the Control Flow and give it a suitable name. Then edit the Script Task and add the string variable from step 2 as readonly variable.
Script Task - ReadOnlyVariables


























4) The Script - References
Choose the Scripting Language and hit the Edit Script button. We need to reference 4 assemblies, but they are not in the SQL Server folder. They are only available in the GAC. The path varies a little per computer. These are mine:
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfo.dll
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Sdk.Sfc\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Sdk.Sfc.dll
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.IntegrationServices.dll


Right click references in the Solution Explorer and choose Add Reference... Then browse these four dll files and add them one by one.
Add references

























IMPORTANT: After adding the references you should press the Save All button to save the reference changes!

5) The Script - Code
Now copy the usings(/imports) from my code and copy the content of my Main method to your main method. The example is in C#, but you can use this translator to get VB.Net code
// C# Code
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
// Added:
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.IntegrationServices;
using System.Collections.ObjectModel;
#endregion

namespace ST_e71fdb73f68c4a3f9595ea5d37464a62
{
    /// <summary>
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
 public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 {
        public void Main()
  {
            // Boolean variable for firing event messages
            bool fireAgain = true;

            // Execution of child package starting
            Dts.Events.FireInformation(0, "Child Package - " + Dts.Variables["User::SSISDBPackagePath"].Value.ToString(), "Starting", string.Empty, 0, ref fireAgain);

            try
            {
                // Connection to the database server where the packages are located
                // SqlConnection ssisConnection = new SqlConnection(@"Data Source=.\SQL2012;Initial Catalog=SSISDB;Integrated Security=SSPI;");
                SqlConnection ssisConnection = new SqlConnection(Dts.Connections["mySqlServerAdoNet"].ConnectionString);
                                
                // SSIS server object with connection
                IntegrationServices ssisServer = new IntegrationServices(ssisConnection);
                
                // Split the variable containing the package path in smaller
                // parts: /SSISDB/Folder/Project/Package.Dtsx 
                string[] SSISDBPackagePath = Dts.Variables["User::SSISDBPackagePath"].Value.ToString().Split('/');

                // The reference to the package which you want to execute
                // Microsoft.SqlServer.Management.IntegrationServices.PackageInfo ssisPackage = ssisServer.Catalogs["SSISDB"].Folders["folder"].Projects["project"].Packages["package.dtsx"];
                Microsoft.SqlServer.Management.IntegrationServices.PackageInfo ssisPackage = ssisServer.Catalogs[SSISDBPackagePath[1]].Folders[SSISDBPackagePath[2]].Projects[SSISDBPackagePath[3]].Packages[SSISDBPackagePath[4]];

                // Add execution parameter to override the default asynchronized execution. If you leave this out the package is executed asynchronized
                Collection<Microsoft.SqlServer.Management.IntegrationServices.PackageInfo.ExecutionValueParameterSet> executionParameter = new Collection<Microsoft.SqlServer.Management.IntegrationServices.PackageInfo.ExecutionValueParameterSet>();
                executionParameter.Add(new Microsoft.SqlServer.Management.IntegrationServices.PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "SYNCHRONIZED", ParameterValue = 1 });

                // For adding more parameters go to my WIKI post on MSDN:
                // http://social.technet.microsoft.com/wiki/contents/articles/21978.execute-ssis-2012-package-with-parameters-via-net.aspx

                // Get the identifier of the execution to get the log
                long executionIdentifier = ssisPackage.Execute(false, null, executionParameter);

                // If you want to catch the events from the package you are executing then you can add this
                // foreach loop. It reads the events and fires them as events. You can remove this loop if
                // you're not interested in them.

                // Loop through the log and fire events
                foreach (OperationMessage message in ssisServer.Catalogs["SSISDB"].Executions[executionIdentifier].Messages)
                {
                    // Translate Message Source Type code and Message Type code to description. See
                    // MSDN for the complete list http://msdn.microsoft.com/en-us/library/ff877994.aspx

                    string messageSourceType = "";
                    switch (message.MessageSourceType)
                    {
                        case 10:
                            messageSourceType = "Entry APIs, such as T-SQL and CLR Stored procedures";
                            break;
                        case 20:
                            messageSourceType = "External process used to run package (ISServerExec.exe)";
                            break;
                        case 30:
                            messageSourceType = "Package-level objects";
                            break;
                        case 40:
                            messageSourceType = "Control Flow tasks";
                            break;
                        case 50:
                            messageSourceType = "Control Flow containers";
                            break;
                        case 60:
                            messageSourceType = "Data Flow task";
                            break;
                    }

                    // Translate Message Type (=event)
                    string messageType = "";
                    switch (message.MessageType)
                    {
                        case -1:
                            messageType = "Unknown";
                            break;
                        case 120:
                            messageType = "Error";
                            break;
                        case 110:
                            messageType = "Warning";
                            break;
                        case 70:
                            messageType = "Information";
                            break;
                        case 10:
                            messageType = "Pre-validate";
                            break;
                        case 20:
                            messageType = "Post-validate";
                            break;
                        case 30:
                            messageType = "Pre-execute";
                            break;
                        case 40:
                            messageType = "Post-execute";
                            break;
                        case 60:
                            messageType = "Progress";
                            break;
                        case 50:
                            messageType = "StatusChange";
                            break;
                        case 100:
                            messageType = "QueryCancel";
                            break;
                        case 130:
                            messageType = "TaskFailed";
                            break;
                        case 90:
                            messageType = "Diagnostic";
                            break;
                        case 200:
                            messageType = "Custom";
                            break;
                        case 140:
                            messageType = "DiagnosticEx";
                            break;
                        case 400:
                            messageType = "NonDiagnostic";
                            break;
                        case 80:
                            messageType = "VariableValueChanged";
                            break;
                    }

                    // Fire event depending on the message type (event) in the child package. Since there are event types that you
                    // can't fire from a Script Task, we need to 'translate' them. For example a TaskFailed event is fired as an
                    // error event. More info see: http://microsoft-ssis.blogspot.com/2011/02/script-task-and-component-logging.html
                    switch (message.MessageType)
                    {
                        case -1:    // Unknown
                        case 120:   // Error
                        case 130:   // TaskFailed
                            Dts.Events.FireError(Convert.ToInt32(message.MessageType), "Child Package - " + messageSourceType, messageType + " : " + message.Message, string.Empty, 0);
                            break;
                        case 110:   // Warning
                            Dts.Events.FireWarning(Convert.ToInt32(message.MessageType), "Child Package - " + messageSourceType, messageType + " : " + message.Message, string.Empty, 0);
                            break;
                        default:
                            Dts.Events.FireInformation(Convert.ToInt32(message.MessageType), "Child Package - " + messageSourceType, messageType + " : " + message.Message, string.Empty, 0, ref fireAgain);
                            break;
                    }
                } // END FOREACH LOOP

                if (ssisServer.Catalogs["SSISDB"].Executions[executionIdentifier].Status == Operation.ServerOperationStatus.Success)
                {
                    // Execution of child package succeeded
                    Dts.Events.FireInformation(0, "Child Package - " + Dts.Variables["User::SSISDBPackagePath"].Value.ToString(), "Succeeded", string.Empty, 0, ref fireAgain);
                    Dts.TaskResult = (int)ScriptResults.Success;
                }
                else
                {
                    // Execution of child package failed
                    Dts.Events.FireError(0, "Child Package - " + Dts.Variables["User::SSISDBPackagePath"].Value.ToString(), "There may be error messages posted before this with more information about the failure.", string.Empty, 0);
                    Dts.TaskResult = (int)ScriptResults.Failure;
                }

            }
            catch (Exception ex)
            {
                // Execution of child package failed (server timeout, can't find package, etc.)
                Dts.Events.FireError(0, "Child Package - " + Dts.Variables["User::SSISDBPackagePath"].Value.ToString(), "Failed: " + ex.Message, string.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
  }

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

 }
}


6) The result
Now you can execute the package and see the result. You could add some filtering in the message loop to reduce the number of messages.
The result in Visual Studio



















Note 1: The other project already needs to be deployed to the SSIS Catalog
Note 2: You get two executions with both their own ServerExecutionId
Two executions






If you want to pass parameters to the child package then you could check out my WIKI example on parameters.

There are alternatives like executing a SQL Server Agent job via an Execute SQL Task or Calling DTExec via an Execute Process Task.

* update: 30 second timeout workaround *
Related Posts Plugin for WordPress, Blogger...