SSIS Gotchas

Remedies range from rebooting to frontal lobotomies. 

Software Detective Bignose investigates more Bizarre cases of malfeasance & malfunction in the obtuse world of SSIS 2005.

  • Over-complicated Dataflow Designs
  • Table locking
  • ADO.Net Providers vs. Native Providers
  • Declaring multiple ReadOnly/ReadWrite Variables
  • OLE DB Source SQL Parameters

 

Over-complicated Dataflow Designs

 If your design looks like this and nothing seems to happen when you debug or run it...

Image

...rationalize your ETL design by using intermediate target tables and spread the complexity over a number of data-flow tasks.

 

Table Locking

 Table locking is on by default for OLEDB Destinations. This can cause problems with complicated designs (see above). Unless really necessary, turn table locking off.

 

Acquiring Data Sources of ADO.Net Providers vs. Native Providers

 What is not obvious, even to ADO experts it seems (I was once one of them but fell into this trap), is that the following AcquireConnections code only works with ADO.Net providers:


  1. Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
  2.     Try
  3.         connMgr = Me.Connections.PEEPADO
  4.         sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)
  5.     Catch ex As Exception
  6.         Me.Log("AcquireConnections: " + ex.Message, 0, Nothing)
  7.     End Try
  8. End Sub

 

 In other words, where you need to Acquire a Data Source in VB.Net code, create a 'SqlClient Data Provider' especially for this purpose (SqlClient Data Provider' are useless for anything else, including Lookup components). Your resulting connection string should resemble something like this:

 

  1. Data Source=HD-2K3-DV-DM01;Initial Catalog=PEEPStable;Integrated Security=True

 

This one took a while to figure out...

 

Declaring multiple ReadOnly/ReadWrite Variables

Script tasks allow you to declare more than one variable that can be accessed from the VB (ugh!) script. The temptation is to neatly space the variables between commas-spaces like the VB (agh!) environment would impose on you. But not here! Any space in your list of variables completely messes things up with no useful error message. Use commas only between variables and no error message appears (by which you should not assume that the script component will work, or course)

 

OLE DB Source SQL Parameters

Passing parameters to some SQL in an OLE DB Source only works if the SQL is simple, e.g. 

  1. SELECT *
  2.   FROM Account
  3.  WHERE Name like ?

 

However, anything vaguely complicated like a hierarchical query and SSIS throws its little toys out of the cot.  Do try this: 

  1. WITH accountRecursion(Name, ParentId, Id, AccountLevel) AS (
  2.   SELECT Name, ParentId, Id, 0 AS AccountLevel
  3.     FROM Account
  4.    WHERE ParentId is NULL 
  5.    UNION ALL
  6.   SELECT a1.Name, a1.ParentId, a1.Id, AccountLevel + 1
  7.     FROM Account a1
  8.    inner join accountRecursion a2
  9.       ON a1.parentId = a2.Id
  10. )
  11. SELECT Name, ParentId, Id, AccountLevel
  12.   FROM accountRecursion
  13.  WHERE (AccountLevel = ?)

 

 There is only one way to get such a select in an OLE DB Source to work:

 

  1. Write a stored procedure:
    1. CREATE PROCEDURE sp_MDM_SelectAccountsAtLevel @Id VARCHAR(18)
    2. AS
    3. BEGIN 
    4.   WITH accountRecursion(Name, ParentId, Id, AccountLevel) AS (
    5.     SELECT Name, ParentId, Id, 0 AS AccountLevel
    6.       FROM Account
    7.      WHERE ParentId is NULL 
    8.      UNION ALL
    9.     SELECT a1.Name, a1.ParentId, a1.Id, AccountLevel + 1
    10.       FROM Account a1
    11.      inner join accountRecursion a2
    12.         ON a1.parentId = a2.Id
    13.   )
    14.   SELECT Name, ParentId, Id, AccountLevel
    15.     FROM accountRecursion
    16.    WHERE (AccountLevel = @Id)
    17. END

     

  2. Call the stored procedure in the OLE DB Source:
    1. exec sp_MDM_SelectAccountsAtLevel ?

     

  3. Assign the SSIS Variable to the parameters