SSIS – Consuming Microsoft Access or Microsoft Excel Data Sources in 64-bit Environments

October 19, 2011 2 Comments

The problems discussed in this post are far from new however they are problems still discussed frequently on various forums and problems which, for some reason or another, are still difficult to debug. 

Please Note: The content of this post applies to developing and deploying in a 64-bit environment therefore some of the workarounds and recommendations in this post will not apply to 32-bit environments.  This post does however explain how Business Intelligence Development Studio (BIDS), Integration Services and SQL Server Agent interact with SSIS packages which need to consume 32-bit and 64-bit providers so it may still answer some of your questions.

Problem

The main problem is that there is no 64-bit version of the Microsoft Jet 4.0 OLE DB Provider therefore SSIS packages which use this provider to consume Microsoft Access or Microsoft Excel data sources fail to execute when:

  1. Developed and debugged in a 64-bit environment
  2. Deployed to a 64-bit environment

There are also two other issues related to using BIDS in a 64-bit environment which add to the confusion and frustration of developing and debugging SSIS packages that consume Microsoft Access and Microsoft Excel data sources:

  1. BIDS is a 32-bit application.  What this means is that when BIDS is installed in a 64-bit environment and used to develop SSIS packages, BIDS will not recognise any 64-bit providers installed in the environment therefore you will not be able to consume such providers in your packages. 
  2. Although BIDS is a 32-bit application, it uses the 64-bit SSIS Runtime (by default) to execute and debug packages therefore packages that consume 32-bit only providers fail.

Solution

The solution would be for Microsoft to develop a 64-bit version of the Microsoft Jet 4.0 OLE DB Provider and to create a 64-bit version of BIDS.  Well they have (sort of) in relation to creating a 64-bit provider and this post provides two examples of using it as an alternative to using the Microsoft Jet 4.0 OLE DB Provider.

The Microsoft Office 12.0 Access Database Engine OLE DB Provider (a.k.a. Microsoft ACE OLE DB 12.0 Provider) is a 64-bit (and 32-bit) provider which can be used to consume Microsoft Access and Microsoft Excel data sources in SSIS packages executing on 64-bit environments.  Don’t get too excited though as using this provider can be just as frustrating as using the Microsoft Jet 4.0 OLE DB Provider (You’ll see why shortly).

There are four solutions (I’ve tried) that can be implemented to get around development issues relating to consuming Microsoft Access or Microsoft Excel data sources in SSIS packages:

  1. Use the 32-bit Microsoft Jet 4.0 OLE DB provider in both development and deployment environments
  2. Use the 32-bit Microsoft ACE OLE DB 12.0 Provider in both development and deployment environments
  3. Use the 32-bit Microsoft Jet 4.0 OLE DB provider in development and the 64-bit Microsoft ACE OLE DB 12.0 in deployment (My recommendation)
  4. Use the 32-bit Microsoft ACE OLE DB 12.0 provider in development and the 64-bit Microsoft ACE OLE DB 12.0 in deployment

Each of these solutions have their own pros and cons so hopefully this post will assist you with debugging and help you determine which solution to implement for your requirement.

Setting Up BIDS

Please Note: The following steps are required for all solutions discussed in the post.

As stated above, BIDS is a 32-bit application so it will not recognise any 64-bit providers therefore you cannot use any 64-bit providers during development.  That said, when you execute (debug) a package in BIDS on a 64-bit OS, BIDS will use the 64-bit SSIS runtime by default, not the 32-bit runtime.  Given this, if a package uses the Microsoft Jet 4.0 OLE DB Provider or the 32-bit Microsoft ACE OLE DB 12.0 Provider to consume a Microsoft Access or Microsoft Excel data source, it will fail with an error similar to the following:

Microsoft Jet 4.0 OLE DB Provider

[Connection manager "Cruises"] Error: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR.  The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered — perhaps no 64-bit provider is available.  Error code: 0×00000000.

An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0×80040154  Description: "Class not registered".

Microsoft ACE OLE DB 12.0 Provider

[Connection manager "Cruises"] Error: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider Microsoft ACE OLE DB 12.0 is not registered — perhaps no 64-bit provider is available. Error code: 0×00000000.

An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0×80040154 Description: "Class not registered".

Fixing this error is very simple.  All you need to do is tell BIDS to use the 32-bit SSIS Runtime during debugging by:

  1. Right clicking on your project in BIDS and selecting Properties
  2. When the Property Page dialog launches, select the Debugging configuration property page (on the left).  BTW, notice that the title of this dialog is “32-bit Runtime Property Pages”.  A clear indication that BIDS is a 32-bit application. 
  3. Set the Run64BitRuntime property to false

image

Now your packages will execute from within BIDS without 64-bit provider errors.  That said, there are a couple of things you need to note about this setting:

  1. The Run64BitRuntime property is project specific therefore you need to set it for every project
  2. This setting is not stored in a SSIS package therefore changing this in your development environment does not affect the deployment environment and will not guarantee that your package will execute on a 64-bit OS.

Solution 1:  32-bit Microsoft Jet 4.0 OLE DB Provider in Both Environments

This solution is the one implement most frequently.  It involves telling BIDS to use the 32-bit SSIS Runtime during development (see “Setting Up Bids” above) and telling Integration Services on the deployment machine to use the 32-bit SSIS runtime during execution.  This is where things can get a little messy.

As stated above, setting the Run64BitRuntime to false during development does not affect how the package is executed when deployed.  Given this, a package run on a 64-bit OS may fail if:

  1. The package is executed using the DTEXEC Utility via a command prompt.
  2. The package is executed via a SQL Server Agent job

Please Note: The DTEXECUI Utility (not to be confused with the DTEXEC Utility discussed in this post) is a 32-bit graphical tool therefore packages run using this utility in a 64-bit environment run in Windows on Win32 (WOW).  Therefore, packages that consume the Microsoft Jet 4.0 OLE DB Provider or the 32-bit Microsoft ACE OLE DB 12.0 Provider (not the 64-bit provider) will execute correctly using this tool as long as the required providers have been installed.

By default, Integration Services installs the 64-bit version of the DTEXEC utility on a 64-bit OS during setup. If you need to execute packages in 32-bit mode, you will need to install the 32-bit version of the DTEXEC utility. This can be achieved by installing the Client Tools or BIDS during setup (or by running setup again at a later stage).  This is where some people come unstuck.

If you work for an organisation that doesn’t allow the Client Tools or BIDS to be installed on deployment servers,  you will need to look at using Solution 3 or 4.  If you can install Client Tools or BIDS, this still doesn’t mean all is sweet and that you’ll experience error free deployments.

By default, a 64-bit OS that has both the 64-bit and 32-bit versions of an Integration Services command prompt utility installed will run the 32-bit version at the command prompt by default. This is because the directory path for the 32-bit version appears in the PATH environment variable before the directory path for the 64-bit version. 

If you are executing a package via a command prompt and nothing has been done to change the order of the directory paths within the PATH environment variable, your package will run just fine.  If the order of the directory paths within the PATH environment variable have changed, your package will fail unless you specify the full path to the 32-bit DTEXEC Utility.

If a package is executed via a SQL Server Agent job, it will execute using the 64-bit runtime (by default) and fail.  This is because the SQL Server Agent uses the registry, not the PATH environment variable, to locate the correct executable for the utility which results in the 64-bit version being selected.  To get around this issue, you can:

  1. Check the “Use 32 bit runtime” option of the SQL Server Integration Services Package job step (found on the Execution Options tab) , or
  2. Use an Operating System (CmdExec) job step (instead of a SQL Server Integration Services Package job step) to execute your package as this enables you to specify the location of the 32-bit DTEXEC Utility (This blog post on BI Polar provides the steps required to do this), or
  3. Change the registry to point to the 32-bit version of DTEXEC (Not!),  Do not do this as it will affect all packages executed on the machine and make you Public Enemy No. 1 within your organisation, or
  4. Use solution 3 or 4.

If you want to use this solution, I recommend that you implement Step 1 as Step 2 requires you to configure command line parameters to execute your package which just adds complexity to the situation.

image

Solution 2:  32-bit Microsoft ACE OLE DB 12.0 Provider in Both Environments

This solution is similar to Solution 1 therefore all the problems (and workarounds) associated with development and deployment in Solution 1 apply to this solution as well.  There is however an additional problem associated with using this provider:

You cannot install the 32-bit version of the Microsoft Access Database Engine 2010 Redistributable on a 64-bit OS if the 64-bit version of Microsoft Office or Microsoft ACE OLE DB 12.0 Provider are installed.

image

If your development and/or deployment environments are configured as above (i.e. you have the 64-bit version of Microsoft Office or Microsoft ACE OLE DB 12.0 Provider installed on your machine), you can either:

  1. Uninstall the 64-bit versions of Microsoft Office and Microsoft ACE OLE DB 12.0 and install their equivalent 32-bit versions, or 
  2. Use Solution 1, 3 or 4

The reason why Step 2 states use Solution 1 is because I don’t see the point of uninstalling the 64-bit versions of Microsoft Office and/or Microsoft ACE OLE DB 12.0 Provider just so you can install the 32-bit version of the Microsoft ACE OLE DB 12.0 Provider.  It would be far easier to use the Microsoft Jet 4.0 OLE DB Provider instead.

Solution 3:  32-bit Microsoft Jet 4.0 OLE DB Provider in Development and the 64-bit Microsoft ACE OLE DB 12.0 in Deployment

This solution is the one I use and recommend.  It involves telling BIDS to use the 32-bit SSIS runtime during development (see “Setting Up Bids” above) and changing the provider used to consume Microsoft Access and Microsoft Excel data sources at runtime using a SSIS Package Configuration file.

So how do we achieve this?

Once you have configured BIDS to use the 32-bit SSIS Runtime, use the Microsoft Jet 4.0 OLE DB Provider to create a connection to your Microsoft Access or Microsoft Excel data source.  Once this is done, create a package configuration file by following these steps:

  1. Launch the Package Configurations Organizer Wizard and add a XML Configuration File
  2. Follow the steps in the wizard until you reach the Select Properties to Export page. 
  3. Locate the Microsoft Jet 4.0 OLE DB connection in the Connection Managers node.  Notice that the value property contains the text Microsoft.Jet.OLEDB.4.0.  This is the text that gets modified at deployment.
  4. Check the box next to the ConnectionString property of your connection
  5. Finish the rest of the wizard.

image

The next step is to tell BIDS to create a Deployment Utility for your package.

  1. Right click on your project in BIDS and select Properties
  2. When the Property Page Dialog launches, select the Deployment Utility configuration property page (on the left)
  3. Set the CreateDeploymentUtility property to true
  4. Build your project

image

Now that you’ve created a deployment utility for your package, you can change the provider setting of your package connection during deployment by following these steps:

  1. Ensure that the 64-bit Microsoft.ACE.OLEDB.12.0 provider has been installed in your deployment environment.
  2. Copy the files from the bin\Deployment directory of your SSIS Project to the deployment machine
  3. Execute the deployment utility by double clicking on the Integration Services Deployment Manifest file
  4. Follow the wizard steps until you reach the Package Configurations page
  5. Expand the nodes in the Configurations list (at the bottom on the page) until you find the connection you need to modify
  6. Locate the Value column of the connection properties and replace Microsoft.Jet.OLEDB.4.0 with Microsoft.ACE.OLEDB.12.0 (Remember to include the full-stops)
  7. Complete the rest of the deployment steps

image

That’s it.  When the package is executed, it will use the 64-bit version of the Microsoft ACE OLE DB 12.0 provider instead of trying to use the non-existent 64-bit Microsoft Jet 4.0 OLE DB Provider.

Please Note: The DTEXEC issues identified in Solution 1 may cause the package to fail if the version of DTEXEC being used to execute the package is the 32-bit version.  Please follow the steps outlined in the DTEXEC Utility documentation to ensure that the correct version of the utility is used to execute your package.

Please Also Note: Using the DTEXECUI graphical tool to execute your package will cause the package to fail.  This is because this utility is a 32-bit only utility and no 32-bit Microsoft.ACE.OLEDB.12.0 provider exists in the deployment environment (Refer to Solution 2 for details on Microsoft.ACE.OLEDB.12.0 provider installation requirements.)

Solution 4:  32-bit Microsoft ACE OLE DB 12.0 Provider in Development and the 64-bit Microsoft ACE OLE DB 12.0 in Deployment

At first glance this solution seems better than all the rest however this solution will experience the same problems as Solution 2 i.e.

You cannot install the 32-bit version of the Microsoft Access Database Engine 2010 Redistributable on a 64-bit OS if the 64-bit version of Microsoft Office or the Microsoft ACE OLE DB 12.0 Provider are installed.

If your development environment has the 64-bit version of the Microsoft Office or Microsoft ACE OLE DB 12.0 Provider installed, you will not be able to install the 32-bit version of the Microsoft ACE OLE DB 12.0 Provider unless you uninstall these products first.  This is why I don’t use this solution as I have the 64-bit version of Microsoft Office installed on my development machine.

If your development machine doesn’t have the 64-bit version of the Microsoft Office or Microsoft ACE OLE DB 12.0 Provider installed, this is the solution you should implement.

Conclusion

As you can see, there are a number of variables that can affect the successful development and deployment of SSIS packages in a 64-bit environment.  Hopefully some of the issues and workarounds I’ve identified will assist with debugging your solutions.

Filed under SSIS

Calling a SSIS Package from a Stored Procedure

July 4, 2011 2 Comments

Problem

Calling a SQL Server Integration Services (SSIS) package from s stored procedure should be an easy task however Microsoft has provided no direct method to do so.

A quick search on Google found this CodePlex example which uses the xp_cmdshell extended stored procedure to execute dtexec via the Windows Command Shell and this blog post (Rick Weyenberg) which describes how to use a stored procedure to create a SQL Server Job which executes a SSIS package via a Job Step.

Although the above examples work quite well, I wasn’t too keen on using xp_cmdshell and I wanted a solution that provided the ability to:

  • Notify of execution failure
  • Use dynamic package declaration
  • Use dynamic variable assignment

Solution

This solution is based on the SQL Server Job example provided by Rick Weyenberg however it uses XML to implement my additional requirements.

It has been been tested on SQL Server 2005 – 2008(R2) and is configured to execute SSIS packages that reside on the same server as the executing stored procedure.

MSDN Gallery: http://code.msdn.microsoft.com/Calling-a-SSIS-Package-a35afefb

XML Structure

The structure of the XML used in this solution is as follows:

<package name="PackageName" notifyMethod="Method" notifyOperator="Operator"
        owner="Owner" runAs="RunAs">
    <variable name="Variable1" value="Value"/>
    <variable name="Variable2" value="Value"/>
</package>

Package Element

  • name: Name of the SSIS package to execute.  You need to specify the full package name and path i.e. if a package called SSISTest resides in a folder called Test within MSDB, the package name would be \Test\SSISTest
  • notifyMethod: The method used to notify of job failure.  The value for this attribute corresponds with SQL Server Agent notification types (Except for “Automatically Delete Job” which is always set to “When the job completes” and “Write to the Windows Application Event Log” which is always set to “When the job fails”) .  Leave blank for no notification.
  • notifyOperator: The name of the SQL Server Operator to notify when notifyMethod is provided.
  • owner: An account in SQL Server that has sufficient permissions to execute jobs
  • runAs: An proxy account that has sufficient permissions to execute any tasks within the SSIS package.  Leave blank to run under the SQL Server Agent Service Account.

I have only added the owner and runAs attributes to demonstrate that this can be done.  I recommend that you do not allow the caller of the stored procedure to nominate which accounts to use.

Variable Element

  • name: Name of the SSIS variable you want to assign a value to
  • value: Value you want to assign to the variable

Stored Procedure

The stored procedure used to implement this solution is quite lengthy so I’ll break it down into chunks and provide explanations where required.

1) Create the stored procedure shell which accepts a parameter of type XML and implements some basic error handling.

The error handling implemented here only relates to job creation. Any errors that occur during job execution will be logged in the Windows Application Event Log.

CREATE PROCEDURE [dbo].[usp_ExecuteSSISPackage]
(
  @xVar XML
)
AS
  BEGIN
    -- Step 2 code goes here

    BEGIN TRY
      -- Step 3 code goes here

      -- Step 4 code goes here

      -- Step 5 code goes here

      -- Step 6 code goes here

      -- Step 7 code goes here

      -- Return successfull job creation
      RETURN 1
    END TRY
    BEGIN CATCH
      -- Retrieve error details
      SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;

      -- Return failed job creation
      RETURN 0
    END CATCH
  END

2) Create some local variables.

@VariableTable is used to hold the shredded XML variable data (@xVar parameter)

    -- Job specific variables
    DECLARE @Server NVARCHAR(100)
    DECLARE @JobName NVARCHAR(128)
    DECLARE @JobId BINARY(16)
    DECLARE @StepCmd VARCHAR(MAX)

    -- Used to create SSIS variable mappings
    DECLARE @MaxID BIGINT
    DECLARE @CurrentID BIGINT
    DECLARE @VariableList VARCHAR(MAX)
    DECLARE @VariableTable TABLE
      (
        ID BIGINT IDENTITY(1,1),
        Name VARCHAR(50),
        Value VARCHAR(255)
      )

    -- Attribute Variables
    DECLARE @PackageName VARCHAR(255)
    DECLARE @NotifyMethod VARCHAR(10)
    DECLARE @NotifyOperator VARCHAR(255)
    -- ***********************************************
    -- Please Note: I have only added the owner
    -- and runAs attributes to demonstrate that this
    -- can be done.  I recommend that you DO NOT allow
    -- the caller of the stored procedure to assign
    -- which accounts to use.
    DECLARE @Owner VARCHAR(100)
    DECLARE @RunAsAccount VARCHAR(100)
    -- ***********************************************

3) Shred the XML contained in the @xVar parameter and store the resulting data in local variables

      -- Get package attributes
      SELECT @PackageName = nref.value('@name[1]','varchar(255)'),
             @NotifyMethod = nref.value('@notifyMethod[1]', 'varchar(10)'),
             @NotifyOperator = nref.value('@notifyOperator[1]', 'varchar(255)'),
             @Owner = nref.value('@owner[1]','varchar(100)'),
             @RunAsAccount = nref.value('@runAs[1]', 'varchar(100)')
      FROM   @xVar.nodes('/package') AS R(nref)

      -- Store SSIS variable details in local table variable
      INSERT INTO @VariableTable
      SELECT nref.value('@name[1]', 'varchar(50)'),
             nref.value('@value[1]', 'varchar(50)')
      FROM   @xVar.nodes('/package/variable') AS R(nref)

4) Create the job.

The job name (@JobName) is made up of the combination of the package name and a GUID.  This enables multiple copies of the same package to be executed at the same time.

@delete_level = 3 causes the job to be deleted regardless of whether job execution succeeded

      -- Get the current server name
      SET @Server = CONVERT(sysname, SERVERPROPERTY(N'servername'))

      -- Create a unique job name
      SET @JobName = @PackageName + '_' + CAST(NEWID() AS CHAR(36))

      -- Create the job
      IF @NotifyMethod = 'E-mail'
        BEGIN
          EXEC  msdb.dbo.sp_add_job @job_name = @JobName, @enabled = 1,
            @notify_level_email = 2, -- When the job fails
            @notify_email_operator_name = @NotifyOperator,
            @notify_level_eventlog = 2, -- When the job fails
            @Owner_login_name = @Owner,
            @delete_level = 3, -- Delete job regardless of execution state
            @job_id = @JobId OUTPUT
        END
      ELSE IF @NotifyMethod = 'Page'
        BEGIN
          EXEC  msdb.dbo.sp_add_job @job_name = @JobName, @enabled = 1,
            @notify_level_page = 2, -- When the job fails
            @notify_page_operator_name = @NotifyOperator,
            @notify_level_eventlog = 2, -- When the job fails
            @Owner_login_name = @Owner,
            @delete_level = 3, -- Delete job regardless of executionstate,
            @job_id = @JobId OUTPUT
        END
      ELSE IF @NotifyMethod = 'Net send'
        BEGIN
          EXEC  msdb.dbo.sp_add_job @job_name = @JobName, @enabled = 1,
            @notify_level_netsend = 2, -- When the job fails
            @notify_netsend_operator_name = @NotifyOperator,
            @notify_level_eventlog = 2, -- When the job fails
            @Owner_login_name = @Owner,
            @delete_level = 3, -- Delete job regardless of execution state,
            @job_id = @JobId OUTPUT
        END
      ELSE
        BEGIN
          EXEC  msdb.dbo.sp_add_job @job_name = @JobName, @enabled = 1,
            @notify_level_eventlog = 2, -- When the job fails
            @Owner_login_name = @Owner,
            @delete_level = 3, -- Delete job regardless of execution state,
            @job_id = @JobId OUTPUT
        END

      -- Add the job to the server
      EXEC msdb.dbo.sp_add_jobserver @job_name = @JobName, @server_name = @Server

5) Get the package variables and their associated values.

This code portion loops through the rows in the @VariableTable and builds a string which is used to add the variables and their associated values to the job step.  The syntax used to set package variables is:

/SET \Package.Variables[User::VariableName].Value;VariableValue

e.g.

/SET \Package.Variables[User::ProjectID].Value;666

Actual stored procedure code.


      -- Build the SSIS variable assignment list
      SELECT @MaxID = MAX(ID) FROM @VariableTable
      SET @VariableList = ''      SET @CurrentID = 0
      WHILE (@CurrentID <= @MaxID)
        BEGIN
          SELECT @VariableList = @VariableList
            + ' /SET \Package.Variables[User::' + Name + '].Value;' + Value
          FROM @VariableTable
          WHERE ID = @CurrentID

          SET @CurrentID = @CurrentID + 1
        END

6) Create the job step

        -- Create the job step
        SET @StepCmd = '/SQL "' + @PackageName + '" /SERVER '
          + @Server + ' /CHECKPOINTING OFF ' + @VariableList + ' /REPORTING E'

        EXEC msdb.dbo.sp_add_jobstep @job_name = @JobName,
          @step_name = N'SSISExecuteStep', @step_id = 1,
          @cmdexec_success_code = 0, @on_success_action = 1,
          @on_fail_action = 2, @retry_attempts = 0,
          @retry_interval = 0, @os_run_priority = 0,
          @subsystem = N'SSIS', @command = @StepCmd,
          @database_name = N'master', @flags=0, @proxy_name=@RunAsAccount

7) Start the job

        -- Start job
        EXEC msdb.dbo.sp_start_job @job_id = @JobId

That’s it.  To execute the stored procedure in SSMS use:

DECLARE @XML XML
SET @XML =
'<package name="\SSISTest\SSISTest" notifyMethod="E-mail" notifyOperator="ITSupport"
        owner="Machine\Administrator" runAs="">
    <variable name="TestID" value="2"/>
    <variable name="ModifiedBy" value="Jeff"/>
</package>'

exec [dbo].[usp_ExecuteSSISPackage] @XML

To call the stored procedure from .Net is very straight forward.

I’m using the Microsoft Enterprise Library 5.0 as it handles the majority of the database code required to connect to SQL Server and execute the stored procedure.


using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using System;
using System.Data;

class SSIS
{
    public int ExecuteSSISPackage()
    {
        SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();

        // This is the stored procedure we created earlier
        DbCommand dbCommand =
            db.GetStoredProcCommand("[dbo].[usp_ExecuteSSISPackage]");

        // Add a paramter of type XML and assign the
        String xmlData = "<Your XML Data Goes Here>";


        db.AddInParameter(dbCommand, "@xVar", DbType.Xml, xmlData);
        // Add a ReturnValue paramter of type INT for error checking
        db.AddParameter(dbCommand, "@RETURN_VALUE", DbType.Int16, 0,
            ParameterDirection.ReturnValue, false, 0, 0, "",
            DataRowVersion.Current, 0);

        // Execute the stored procedure
        DataSet ds = db.ExecuteQuery(dbCommand);

        //Get error indicator
        int erroCode =
            Convert.ToInt32(db.GetParameterValue(dbCommand, "@RETURN_VALUE"));

        return erroCode;
    }
}

Conclusion

In this post I have demonstrated how to call a SSIS package from a SQL Server stored procedure.

Shortly I will demonstrate how to achieve the same result in Denali.  In the meantime, have a read of the following post by Jamie Thomson (SSIS Junkie) – Synchronous execution of SSIS packages using T-SQL : Denali

Filed under Links, SSIS

Fun with SSIS and {GUID’s}

June 28, 2011 Leave a Comment

Task

Create a SQL Server Integration Services (SSIS) package that accepts a Globally Unique Identifier (GUID) as a variable and uses this variable as:

Problem

Although SSIS supports the GUID data type (DT_GUID) and although you can use a Data Conversion Transformation to transform a string representation of GUID into the DT_GUID data type, there is no GUID data type that can be assigned to SSIS variables.

“Not a problem” I hear you say, “Just use a STRING data type and SQL Server will implicitly convert it to a GUID”.  “You wish!” says SSIS

Solution

This should have been an easy solution to implement so I can’t believe I wasted an hour trying to get things working. So what caused all my pain? SSIS’s {perceived}lack of support for GUID’s variables.

Steps to implement solution:

1. Create a SSIS variable of type STRING

2. Add an Execute SQL Task to execute a SQL Server stored procedure using the SSIS variable as a parameter

3. Add a Data Flow Task

4. Add an OLE DB Source with a Data Access Mode of SQL Command and a SQL Command Text which uses the SSIS variable as a parameter

5. Add an OLE DB Destination to pipe the result from the OLE DB Source into a SQL Server table.

6. Run the package

7. Debugging

Step 1 – Create SSIS Variable

MSDN Link: How to: Add or Delete a Variable in a Package by Using the Variables Window

Here I’ve created a variable called ProjectUID of type STRING and assigned it a default value to assist with debugging.

image

Step 2 – Create Execute SQL Task

MSDN Link: Working with Parameters and Return Codes in the Execute SQL Task

You can see from the first image below that stored procedure usp_DeleteEMPImportTablesByProjectUID accepts a parameter called @ProjectUID (of data type UNIQUEIDENTIFIER) and it’s value has been set to “?”.  This tells the Execute SQL Task that a parameter mapping is being used to provide the value.

The second image shows how to map the SSIS variable created in step 1 to the @ProjectUID parameter

The connection manager created for this task is using the Native OLE DB\SQL Native Client provider.

image

image

Step 3 – Create Data Flow Task

MSND Link: Adding a Data Flow Task

Nothing add here

image

Step 4 – Add an OLE DB Source

MSDN Link: How to: Extract Data by Using the OLE DB Source

MSDN Link: How to: Map Query Parameters to Variables in a Data Flow Component

Here I add an OLE DB Source and set its Data Access Mode to SQL Command.  I have also added my SQL Statement which filters results by ProjectUID.  As with the Execute SQL Task, the “?” means that a parameter mapping is being used to provide the value.

Clicking on the Parameters button launches the Set Query Parameter dialog (image 2) where the SSIS variable can be mapped.

The connection manager used for this data source is the same as that used for the Execute SQL Task i.e. Native OLE DB\SQL Native Client provider.

image

image

Step 5 – Add an OLE DB Destination

MSDN Link: How to: Load Data by Using the OLE DB Destination

Nothing add here either

image

Step 6 – Run the Package

So I ran the package and as you can see from the images below, the OLE DD Source failed with the following error:

[OLE DB Source [5018]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available.  Source: “Microsoft SQL Native Client”  Hresult: 0x80040E21  Description: “Invalid character value for cast specification”.

Looking into this further shows that it failed on casting the GUID string value (66EF3876-E8CF-45F2-B2D5-8DDFF60B46D3) to a GUID data type.  But how is this so as the Execute SQL Task succeeded and it’s using the same value?

imageimage

Step 7 – Debugging

The first step in the debugging process was to remove the parameter mapping for the OLE DB Source and use the GUID value directly:

WHERE     (ea.ProjectUID = ’66EF3876-E8CF-45F2-B2D5-8DDFF60B46D3′)

As you can see, the package runs successfully.  This is what had me stumped for a while.

Anyway, to cut a long storey short, a STRING representation of a GUID needs to be formatted as {66EF3876-E8CF-45F2-B2D5-8DDFF60B46D3} i.e. enclose the GUID in curly brackets (which are optional by the way)

This format is accepted by both the Execute SQL Task and the OLE DB Source.

imageimage

Conclusion

I’m not sure why the curly brackets are required for an OLE DB Source and not an Execute SQL Task (especially when they’re using the exact same provider) as I haven’t researched it yet.

Filed under SSIS

Follow

Get every new post delivered to your Inbox.

Join 469 other followers