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...
...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:
- Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
- Try
- connMgr = Me.Connections.PEEPADO
- sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)
- Catch ex As Exception
- Me.Log("AcquireConnections: " + ex.Message, 0, Nothing)
- End Try
- 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:
- 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.
- SELECT *
- FROM Account
- WHERE Name like ?
However, anything vaguely complicated like a hierarchical query and SSIS throws its little toys out of the cot. Do try this:
- WITH accountRecursion(Name, ParentId, Id, AccountLevel) AS (
- SELECT Name, ParentId, Id, 0 AS AccountLevel
- FROM Account
- WHERE ParentId is NULL
- UNION ALL
- SELECT a1.Name, a1.ParentId, a1.Id, AccountLevel + 1
- FROM Account a1
- inner join accountRecursion a2
- ON a1.parentId = a2.Id
- )
- SELECT Name, ParentId, Id, AccountLevel
- FROM accountRecursion
- WHERE (AccountLevel = ?)
There is only one way to get such a select in an OLE DB Source to work:
- Write a stored procedure:
- CREATE PROCEDURE sp_MDM_SelectAccountsAtLevel @Id VARCHAR(18)
- AS
- BEGIN
- WITH accountRecursion(Name, ParentId, Id, AccountLevel) AS (
- SELECT Name, ParentId, Id, 0 AS AccountLevel
- FROM Account
- WHERE ParentId is NULL
- UNION ALL
- SELECT a1.Name, a1.ParentId, a1.Id, AccountLevel + 1
- FROM Account a1
- inner join accountRecursion a2
- ON a1.parentId = a2.Id
- )
- SELECT Name, ParentId, Id, AccountLevel
- FROM accountRecursion
- WHERE (AccountLevel = @Id)
- END
- Call the stored procedure in the OLE DB Source:
- exec sp_MDM_SelectAccountsAtLevel ?
- Assign the SSIS Variable to the parameters