Friday 28 September 2012

Call SSIS 2012 Package within .Net application - Part 1

Case
I want to start a SSIS 2012 package from a .Net application. How do you do that?

Solution
For this example I will use Visual Studio 2010 (same as for SSIS 2012) and a C# Windows Form Application for this example. I will add the VB.Net code as well.

Note: this example cannot handle (project) parameters that are available in 2012. See this example for project deployed packages.

1) Create project
Start Visual Studio 2010 and create a new C# Windows Form Application project.
C# Windows Form Application


















2) Change framework version
Changed the framework version to 4 in the newley created project. Right click the project and select properties. A new window will open and there you can change the Target framework to 4. Confirm the question about changing the framework version
Changing framework version


















3) Adding reference to SSIS
We need a reference to Microsoft.SqlServer.ManagedDts.dll to call a package. Right click References in the Solution Explorer and click "Add Reference".  Now browse to the 2012 version of ManagedDts.dll. See screendump for location.
Add reference to SSIS


















4) Edit app.Config
Because apparently this new referenced dll is some kind of mixed mode compiled dll, you need to add useLegacyV2RuntimeActivationPolicy="true" to the app.Config. If you don't do this you will get the following error in SSIS:
The Execute method on the task returned error code 0x80131621 (Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.). The Execute method must succeed, and indicate the result using an "out" parameter.
app.Config


















5) Hello World form
I created a very simple form with a start button (btnStart) and a label (lblStatus) to show the execution result.
My Windows application


















6) Code for start button
This is the code for the start button.
//C# code
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Dts.Runtime;  // Added

namespace CallSSIS
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnStart_Click(object sender, EventArgs e)
        {
            // Instantiate SSIS application object
            Microsoft.SqlServer.Dts.Runtime.Application myApplication = new Microsoft.SqlServer.Dts.Runtime.Application();

            // Load package from file system (use LoadFromSqlServer for SQL Server based packages)
            lblStatus.Text = "Loading package from file system.";
            Package myPackage = myApplication.LoadPackage(@"D:\SSIS\MyPackage.dtsx", null);

            // Optional set the value from one of the SSIS package variables
            myPackage.Variables["User::myVar"].Value = "test123";


            // Execute package
            lblStatus.Text = "Executing package";
            DTSExecResult myResult = myPackage.Execute();

            // Show the execution result
            lblStatus.Text = "Package result: " + myResult.ToString();
        }
    }
}


'VB.Net code
Imports Microsoft.SqlServer.Dts.Runtime

Public Class Form1

    Private Sub btnStart_Click(sender As System.Object, e As System.EventArgs) Handles btnStart.Click
        ' Instantiate SSIS application object
        Dim myApplication As New Microsoft.SqlServer.Dts.Runtime.Application()

        ' Load package from file system (use LoadFromSqlServer for SQL Server based packages)
        lblStatus.Text = "Loading package from file system."
        Dim myPackage As Package = myApplication.LoadPackage("D:\SSIS\MyPackage.dtsx", Nothing)

        ' Optional set the value from one of the SSIS package variables
        myPackage.Variables("User::myVar").Value = "test123"

        ' Execute package
        lblStatus.Text = "Executing package"
        Dim myResult As DTSExecResult = myPackage.Execute()

        ' Show the execution result
        lblStatus.Text = "Package result: " & myResult.ToString()
    End Sub
End Class

The result
Build and run the application.

Thursday 13 September 2012

Who is running the package?

Case
In the SSIS MSDN forum it's a recurring question when a package works in BIDS (Visual Studio), but doesn't work when it is running as a job within SQL Server Agent: Who is running the package?

Solution
I will show you a couple of ways to start a package and let you see which account is actually running the package. For this experiment I created two extra users on my VPC. Besides Joost (the one I use for login to Windows), there are now Joost2 and Joost3.
Extra users








And I created a very simple package with one Execute SQL Task that inserts the system variable System::UserName into a table. This system variable shows the account of the user who started the package.
The insert query

















The parameter for the query


















Now I'm starting this package in various ways.


1) Running the package in Visual Studio / BIDS
Starting the package in BIDS shows that my user (which I used to login) is running the package.
Running in BIDS
















2) Running the package as a job under SQL Server Agent Service Account
I created a job in SQL Server Agent with one jobstep that executes the SSIS package. I'm using the default SQL Server Agent Service Account to run this step (see screenshot).
Job step running under Service Account



















If you go to services (run services.msc) and search for SQL Server Agent, you can see the actual account that is used. For testing purposes I changed the user that runs the Windows Service SQL Server Agent, to Joost2.
Windows Service now running under Joost2








Second run of this package and now via the job. When I excute the job, it shows that Joost2 is running the package.
Joost2 is running the package


















3) Running the package as a job under a proxy
Now I will execute this job a second time, but this time I will create a proxy and a credential under user Joost3 for it, so that is doesn't run under the default service account.

First creating the credential.
A credential under user Joost3



















And second the proxy that is using the newly created Credential. And the proxy can only run SSIS packages.
A proxy running under the new credential



















And third I will edit the job step and select this new proxy under "Run as:".
The job step running under proxy



















And when I now start the job you will see that the package runs under Joost3.
Third time running the package, second time running the job




















Conclusion
If for example you get authorization issues when running a package via a Job or the foreach loop doesn't return any files when running as a job... Then you should check who is actually running the package (service account or a credential via a proxy) and then check the difference between that account and your login account that is used by BIDS.

Sunday 9 September 2012

How to configure a Foreach Loop Container: ADO Enumerator

Case
I have a list of files in a database table which I want to loop through. Can I use a Foreach Loop Container for that?
My table with files













Solution
You can use the Foreach Loop Container with a Foreach ADO Enumerator for this task.


1) Variables
Create two variables. A variable named myFiles of the Object data type. This will contain the list of files. A string variable named filePath. This variable will be used in the Foreach Loop Container and will contain a single filepath.
Variables











2) Execute SQL Task - General
Add an Execute SQL Task to the Control Flow and edit it. Select Full result set as ResultSet; Select the Connection Manager to connect to the database with your table and add the SQLStatement.
Execute SQL Task - Editor - General


















3) Execute SQL Task - Result Set
Go to the Result Set pane and click add. Set the Result Name to zero and select the Object variable from step 1.
Execute SQL Task - Editor - Result Set





















4) Foreach Loop Container - Collection
Add a Foreach Loop Container and connect it to your Execute SQL Task. Edit the Foreach and go to the Collection pane. Select the Foreach ADO Enumerator and select the Object variable from step 1 as the ADO object source variable.
Foreach Loop Container - Collection























5) Foreach Loop Container - Variable Mappings
Go to the Variable Mappings pane and select the string variable from step 1. The index should be zero. It's the first column in a zero based column index (second column has index 1 and so on).

Foreach Loop Container - Variable Mappings























6) The Result
Now the construction is ready. The variable filePath will be filled with a filepath from the query. You can for example use this variable in an expression on a Flat File Connection Manager or in a File System Task. If you want to know how the expression on the connection manager works. Then go to this post about the File Enumerator and start at step 3. I added a Script Task with a messagebox to test the foreach loop.

MessageBox.Show(Dts.Variables["User::filePath"].Value.ToString());










































Note: I have an other Foreach ADO Enumerator example that uses a Script Task to fill the object variable instead of the Execute SQL Task.

Switch package from 64bit to 32bit

Case
My Data Flow Task with an Excel connection doesn't run. It throws the following error:

Error: 0xC020801C at Data Flow Task, Excel Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: component "Excel Source" (1) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS 2008 error example (gives poor hints)

Error: 0xC0209303 at GetDataFromExcel, Connection manager "Excel Connection Manager": The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.
An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".
Error: 0xC020801C at Data Flow Task, Excel Source [8]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0209303.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: Excel Source failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS 2012 error example (gives better hints)


Solution
If you have a 64bit machine, with connections that only support 32bit (like Excel, Access and old ODBC connections) you will get an error like above. The solution is to run your package in 32bit mode. This can be done within Visual Studio for debugging or within SQL Server Management Studio for scheduled packages.

This solution will also allow you to debug a Script Task, because that also doesn't work in 64bit mode.

BIDS / Visual Studio
Running in 32bit mode is a Project Property. Setting it will affect all packages within the project.
Right Click the SSIS project and select Properties in the context menu. Go to the Debugging pane and select false under Run64bitRuntime.
SSIS 2008

SSIS 2012





























SQL Server Management Studio
Edit your job and then edit the right jobstep. Go to the Execution Options pane and check "Use 32 bit runtime". This property will only effect the package (+child packages) called in this jobstep.
SSMS - Use 32 bit runtime
























32 bit problems
Besides running 64 bit problems there are also 32 bit problems. For example, Fuzzy Lookup will not run in 32 bit. It opens SQLDUMPER.EXE in a command line / dos box but with out errors. The solution for that is to run in 64 bit mode. So running an Excel source and a Fuzzy Lookup within the same package could be a bit of a challenge.

C:\Program Files (x86)\Microsoft SQL Server 100\Shared\\SQLDUMPER.EXE






















Note: For 32bit execution via dtexec.exe you need to pick the right folder:
32bit => C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe
64bit => C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe

Note 2: if you use 32bit then the process can use less memory!
Related Posts Plugin for WordPress, Blogger...