Before you continue: if you're already familiar with Biml, the Attunity connector, and using SSIS variables for SQL commands, you can skip to the "Custom Properties" section. Everything else is background information.
This post covers the intersection of a few common challenges in SSIS development:
- Managing source queries when implementing ETL design patterns
- Using an Oracle data source in SSIS
- Using Biml to create packages that can handle both of the above
I'll briefly cover each point and how a common solution to each problem gets in the way of solutions to the other problems - then, I'll demonstrate how you can solve all three problems: you can write Biml to create a repeatable pattern that uses parameterized queries with an Oracle source.
Managing source queries when implementing ETL design patterns
If you aren't using Biml, one way to reduce the amount of tedious drag-and-drop work you do in SSIS is by creating a "template" package that defines the control flow of an ETL process, and swapping in sources, destinations, and intermediate queries. Generalizing your ETL processes makes it easier to update those processes as new requirements come in.
For incremental loads - that is, a source query that only returns "new" rows - an effective solution is to use the "SQL command from variable" access mode for an OLE DB source.
You can add a variable to your package or project, which queries the source (SELECT EmployeeId, EmployeeName FROM HR.Employee) and accepts another variable to filter your result set down to only the new rows (WHERE HireDate > @[User::Date]).
By using a variable in your WHERE clause and setting that variable at runtime, you can ensure that you're only returning the minimum number of rows that you need - and making your source query a variable in the first place is what lets you add another variable to the WHERE clause.
Using an Oracle data source in SSIS
If your package uses an Oracle data source, people generally recommend using the Attunity connector, simply because it's faster. Much faster.
One problem with the Attunity Oracle Source component is that it doesn't natively let you use a SQL command from a variable. Fortunately, there's a relatively easy workaround: you add an expression to the Oracle Source component's parent Data Flow task, and this expression overrides the source component's SqlCommand property. In order to define the query's WHERE clause at runtime, you pass a variable into the expression. For even more flexibility, you can set that variable to be evaluated as an expression, and pass another variable (e.g. the most recent value for MODIFY_DATE) into the query variable.
Biml lets you expressively and concisely define a package pattern. The latest version even supports the Attunity connector with the OracleSource and OracleDestination components.
Unfortunately, these Biml elements don't contain child elements that correspond to the "SQL command from variable" access mode. When using the OLE DB source component (OleDbSource), you can specify a VariableInput child, but attempting to generate an SSIS package containing an OracleSource with a VariableInput child will result in an error. This is expected, since you can't use that access mode when configuring the Oracle Source component using the GUI, either.
Here's the core problem:
- We want to use Biml to generate multiple packages using a pattern,
- and we want to use variables to pass queries into source components so that we have a dynamic WHERE clause,
- but Biml's Attunity Oracle connector elements don't seem to support a way to do that when using Oracle as a source.
And here's how you can work around that in Biml:
1. Add expressions to the data flow task
You want Biml to generate the data flow task with custom expressions, per the method described in Melissa Coates' post.
In the screenshot above, I'm using a variable as the expression, but you could also pass in a simple query.
2. Use CustomComponent syntax for the Oracle source
Instead of the nice OracleSource syntax, use the more verbose CustomComponent syntax. When you compile Biml, OracleSource will get expanded into a custom component anyway:
What an Oracle Source component looks like in XML form
An example of the full syntax for the custom component can be found in a walkthrough on BimlScript.com.
3. Set properties on the CustomComponent element
The ComponentClassId and ComponentTypeName attributes are required. Despite what's pictured in the screenshot of the .DTSX in step 2, you'll actually use "Attunity.SSISOraSrc" as the value of ComponentTypeName, not ComponentClassId.
I grabbed the value of ComponentClassId directly from Visual Studio; if you manually drag in an Oracle Source component, you can find its value in the properties pane.
In addition, set ValidateExternalMetadata to "false". If you don't, you may get a VS_NEEDSNEWMETADATA error at runtime.
4. Make sure the custom component has two specific custom properties
In order for the data flow task's expressions to override the Oracle Source component's SQL command correctly, specify these two CustomProperty elements and leave their content empty:
Both are required in order to generate the package successfully; I got validation errors when I left those properties out. The SqlCommand property will get overridden at runtime by the Data Flow task's Expression element.
5. Add all of the other required elements
The downside to using CustomComponent syntax is not only that basic properties are more verbose, but also that you have to explicitly declare several child elements. In order to compile this component successfully, you need to specify:
- An output path
- External columns
- Output columns on the output path, each of which needs to be mapped to an external column
- An error output path
- Output columns on the error path
- Data type properties for specified columns
- Several other custom properties, such as PrefetchCount, BatchSize, etc., pictured in the screenshot above
If you've done all of the above, your package should generate as expected. The pink triangle on the Oracle Source component below indicates that an expression is controlling its SqlCommand property.