Calling SSIS package Asynchronously

Question

Hi,

I'm calling a SSIS package using LoadPackage(...).

Is it possible to make this call an Asynchronous call?

Answer 1

If you just want it to run in the background then yes, you can either spool up a thread or call some T-SQL to dynamically create a job (and remove it again afterwards). If you want to run it asynchronously and want a callback when it's done, then I think you're out of luck unfortunately.

Answer 2

Yes, use an asynchronous delegate, as demostrated here:

http://msdn.microsoft.com/en-us/library/h80ttd5f.aspx

Answer 3

Are you asking if it's 1) legal to call LoadPackage on a background thread or 2) is it possible. For #1 I can't give a definitive answer because I don't use the SSIS framework.

However #2 (as long as #1 is true) is definately doable. IMHO, you're better off using an existing framework which has API's designed to calling API's async and waiting for the results. For instance with Parellel Extensions June 08 CTP, the following code will do.

using System.Threading.Tasks;
...
var future = Future.Create(()=>LoadPackage); // Starts loading the package
// Do other stuff
var package = future.Value;  // Wait for package load to complete and get the value

Answer 4

I'm calling an SSIS package from my UI (WPF) via an async WCF service call. Service code is:

public string ImportMarriageXML(bool isWakeUp, bool clearExistingMarriage)
{
    try
    {
        var dts = new Microsoft.SqlServer.Dts.Runtime.Application();

        using (var package = dts.LoadFromSqlServer(
            ServiceSettings.Settings.SSIS.ImportMarriages,
            ServiceSettings.Settings.SSIS.ServerIP,
            ServiceSettings.Settings.SSIS.UserID,
            ServiceSettings.Settings.SSIS.Password,
            null))
        {
            package.InteractiveMode = false;
            package.Connections["DB.STAGING"].ConnectionString = String.Format("{0};Provider={1};", DBSettings.ConnectionString(Core.Database.Staging), ServiceSettings.Settings.SSIS.Provider);

            var variables = package.Variables;
            variables["IsWakeUp"].Value = isWakeUp;
            variables["ClearExistingMarriage"].Value = clearExistingMarriage;
            variables["XmlDirectory"].Value = ServiceSettings.Settings.SSIS.Properties.XmlDirectory;

            if (package.Execute() == DTSExecResult.Failure)
            {
                // HACK: Need to refactor this at some point. Will do for now.
                var errors = new System.Text.StringBuilder();
                foreach (var error in package.Errors)
                    errors.AppendFormat("SubComponent: {0}; Description: {1}{2}", error.SubComponent, error.Description, Environment.NewLine);
                throw new ApplicationException(errors.ToString());
            }

            return package.Connections["Text Logging"].ConnectionString;
        }
    }
}

And (part of) the client-side code is as follows:

private void InvokeLoadMarriages()
{
    integrationServicesServiceClient.BeginImportMarriageXML(false, OnEndImportMarriageXML, null);
}

private void OnEndImportMarriageXML(IAsyncResult asyncResult)
{
    view.InvokeDisplayResults(integrationServicesServiceClient.EndImportMarriageXML(asyncResult));
}

Where BeginImportMarriageXML & EndImportMarriageXML are the generated async operations in the proxy class.