How to Parameterize a Source Query in SSIS Using Attunity Connector for Oracle

The steps we are going to follow include the following:

 

The Attunity connector for Oracle used inside of the SSIS data flow looks a little different than the typical OLE DB connector we most commonly use. Specifically, the Attunity source has two options: “Table Name” and “SQL command.” What the Attunity Oracle Source doesn’t have in this dialog box is “SQL command from variable” (like we see for an OLE DB source).

In SSIS, in order to utilize a parameterized query in the data flow source, we typically use a variable that contains the SQL statement and the parameter reference(s). Just because the Attunity Oracle Source dialog box doesn’t show “SQL command from variable” doesn’t mean we can’t get the job done though. It’s just a slightly different process.

Prerequisites: The Attunity connector is already set up in your SSDT environment.

This process references using SQL Server 2012+ and the SSIS project deployment mode.

 

 

Step 1. Create a Project Parameter

Since the source query will be parameterized, first we need a place to store this parameter value. The best choice if the value is to be shared across packages is a project-level parameter. You could also use a package-level parameter if it’s not going to be shared across more than one package, and still get the benefit of being able to change the value at runtime if necessary (editing the value at runtime is why we like using a parameter better than a variable for the value of the parameter).

In this example the project parameter is called IncrementalDays_TaskDate which, in our case, means we are going to bring in a 3-day window of time.

 

Step 2. Create a Variable for the Parameterized Source Query

Here’s a simple query example where I’ve pulled in the project parameter created in step 1. This is inside of the expression builder of a variable. Note that in the expression builder, the text is surrounded by double quotes ( " ) and plus signs ( + ) concatenate the text with the parameterized portion. By clicking the "Evaluate Expression" button, we see the 3 day value as per the value in our project parameter.

Also, double check the EvaluateAsExpression is set to True in the properties of your variable.

Step 3. Add Attunity Source to Data Flow

Add the Oracle Source to the data flow. Next we want to change its data access mode to “SQL command” and add a temporary query (with the same columns as the real query) to get the metadata passed to the SSIS package. Based on the where clause I used, I know that no rows will return but that’s ok -- the purpose here is (a) to make sure Table is *not* selected anymore, and (b) to get the column metadata picked up by SSIS. 

This might seem like somewhat a silly step since it includes a temporary query we won’t keep, but things really worked out more smoothly for me when I didn’t try to skip around it. 

Now we have the pieces in place & we just need to connect up our parameterized query in the variable to the Attunity source so that temporary query gets overwritten by the real one.

Step 4. Set Data Flow Expressions

Click anywhere on the Data Flow canvas (i.e., so the context is not on any single object). The Properties pane should contextually show the Data Flow properties.

Locate the Expressions section in the properties pane. Click the ellipses next to Expressions.

In the Property Expressions Editor, click the drop-down in the Property. Locate the SqlCommand property for your Attunity source (the first part of the name will differ based on what you named it). Set the expression to be your variable. Be careful not to re-type the SQL statement here or anything – we really just want this expression to be a connector between the SQL command in the Attunity source and the variable that’s parameterized.

Step 5. Verify SQL Query Changed in the Attunity Source

Now go back to the Attunity Oracle Source and edit it. Your ‘fake’ SQL query from Step 3 should be replaced with the results of the parameterized one. In the screen shot below how we see the 3-day window of time we originally set in the project parameter. You can click Preview if you like as well, to check the query parses and returns a resultset properly.

At this point, I recommend putting an annotation next to the Attunity source as a reminder that this is parameterized. It’s a little too easy to forget and just edit the source query directly in the SQL window – which will just be overwritten by the expression thus potentially causing a bit of frustration later.

So that’s it. Now you can continue on and finish the package as you normally would. 

Updating Metadata in SSIS

This is just a quick tip re: updating metadata in SSIS. A few days ago I was speaking with an SSIS developer who wasn't aware of this change so I thought I'd share it.

Let's say a column is deleted in the underlying table or view that feeds an SSIS package. Or, maybe you're starting with a 'template' SSIS package that has metadata for a different table than the one you are building...once you adjust the source query, you see red X's on the precedence constraints (pipeline paths) in the data flow:

Prior to SQL Server 2012, updating metadata changes of this nature was a very tedious process. It's now *much* easier than it was before. First, you right-click on the precedence constraint and choose Resolve References:

 

Then you want to check the box on the bottom left (it's not checked by default) then OK. It will then delete the downstream references in the package for the column(s) which no longer exist:

 

And voila, the references no longer exist for the transformations down below in the data flow. You might have to do this in a couple of places if the data flow splits off (like mine does in the screen shot). Depending on what else changed, you may also need to 'click through' some of the pages of some transformations to update metadata (ex: a data type change), but the Resolve References functionality is a huge time-saver.

My BlueGranite colleague and friend Meagan Longoria has been convincing me recently that these are the kind of changes that BIML is well-equipped to take care of quickly & efficiently when it regenerates a package. I will learn BIML soon (really I will, I will), but in the meantime, Resolve References sure is a handy thing to know. It certainly delighted the SSIS developer I was speaking with last week!

You Might Also Like...

Getting Started with Parameters, Variables & Configurations

Parameterizing Connections and Values at Runtime Using SSIS Environment Variables

Documenting Precedence Constraints in SSIS

Documenting Precedence Constraints in SSIS

Just sharing a quick tip about showing the names of precedence constraints within an SSIS package. This is something I tend to do when the precedence constraint is based on something besides a normal "success."

Here's an example of where I have 4 precedence constraints in my SSIS package. Each precedence constraint references the value of a different variable in order for it to be allowed to proceed:

It's a bit easier to see what the package is doing at a glance if the precedence constraint annotations are displayed. 

The first precedence constraint contains an expression and a constraint. It looks like this:

 

Basically it is saying that, not only does the control flow task have to succeed, but a variable (being populated by the same task) also needs to have a count > 0 in order to be able to proceed. 

By default, the "ShowAnnotation" property on a precedence constraint is set to "As Needed." The canvas does show the fx symbol which means there's an expression placed on the precedence constraint. 

SSISPC_4.jpg
 

Since I wanted the package to be a bit more self-documenting at a glance, I changed the "ShowAnnotation" property to ConstraintName. And then I gave the constraint a good name like "If RowCount_ContractorEntity > 0" that would make sense when displayed on the canvas.

 

You might also like the output from the "ShowAnnotation" property called "ConstraintOptions."  It's even more specific.

 

There's also a "ShowAnnotation" property for "ConstraintDescription" but it doesn't work for me like name does, so I don't use that one currently.

Parameterizing Connections and Values at Runtime Using SSIS Environment Variables

This post discusses how to parameterize connection strings and values at runtime using SSIS environment variables. Although it may seem like a lot of steps, it's a one-time setup per environment and makes your SSIS projects very portable and allows for easier manageability of dynamic values that change routinely.

Applicable to: SQL Server 2012 or 2014. Screen shots shown are from SQL Server 2014.

What are SSIS Environment Variables?

SSIS Environment Variables provide the mechanism to set values at the time a package is executed. This functionality is useful for any number of things, frequently for specifying different values between Dev, QA, and Prod environments.

Note that these SSIS environment variables are a different thing than Windows environment variables, although similarly named. They're also different from "regular" variables inside an SSIS package. 

This technique is applicable to SQL Server 2012 and up, and only to the project deployment mode (i.e., not package deployment mode). The concept of SSIS Configurations is no longer applicable with the project deployment mode as they've been replaced with SSIS Environment Variables. If you're just getting familiar with these concepts, please check out this overview: Getting Started with Parameters, Variables, and Configurations in SSIS 2012 (the overview blog entry has gotten to be one of my most visited pages so I figure that's a clue that I should write more on the subject, eh?).

Steps for Using SSIS Environment Variables to Parameterize Connection Strings and Values When the Package Executes

SQL Server Data Tools (SSDT) - Integration Services Project
Step 1:  Create Parameters (Project or Package level as appropriate) and associate expressions, source queries, etc to these Parameters as appropriate.
Step 2:  Parameterize connection strings.
Step 3:  Deploy Project to the SSIS Catalog once package executes as desired within SSDT.

SQL Server Management Studio (SSMS) - Integration Services Catalogs>SSISDB
Step 4:  Create Environment & set up Variables within the Environment. These should coincide with the Parameters from the SSIS project.
Step 5:  Configure each Project (and Packages individually, if needed).
Step 6:  Execute package in SSMS via the Catalog. Will need to manually specify which Environment to use at execution time when running in this on-demand manner.
Step 7:  Create Agent Job to execute on an ongoing basis. Associate the job to the appropriate Environment so it automatically detects values which have been set.

Details for each step are below.

Step 1: Create Parameters and Associate Expressions to the Parameters (SSDT)

First thing to do is determine what elements of the package you wish to be able to vary at runtime. Or, in other words, what may need to be changed by the DBA or system admin without the SSIS developer needing to make a change in SSDT and redeploy the package.

In the example below I have 2 project-level parameters that signify a date range. Since this date range would apply to multiple packages, I made these project-level parameters rather than at the package-level:

Sidenote: in the real world, this generic name of "data extract..." would be too vague. As you'll see below, the SSIS environment variables on the server are shared across all projects, so that may influence your naming conventions if you have numerous projects to manage.

And here's an example of how a source query in my data flow has been associated to the project parameters:

Usually an SSIS project of any size ends up with at least a few project/package parameters, but it is possible that you don't have any.

Step 2: Parameterize Connection Strings (SSDT)

Note that there's a couple of different ways to handle changes to connection strings as you migrate from Dev to QA to Production. This describes one way to handle connections by grouping them with parameters.

I ensure that all connections are project level (not package level) and then I like to parameterize each of my connection strings:

In this example I've parameterized the connecting string itself. You can parameterize individual properties like server name, user name, catalog, if you prefer. This connection string is using Windows authentization so it's not set to sensitive (if it were a password you'd want to set it to sensitive so that it's encrypted).

I do like to set it to be Required which ensures it will be set in the SSIS Catalog before the package can be executed on the server.

Once you have parameterized your connections, you'll see them in the parameters pane. 

Step 3:  Deploy Project to SSIS Catalog

For deploying to Dev, I usually just right-click the project in SSDT and deploy to the Catalog from there.

 

For deploying to other environments like Prod, where you may need to hand off the deployment to someone else, I typically make use of the ISDeploymentWizard tool instead. Details on this can be found here: http://msdn.microsoft.com/en-us/library/hh231102.aspx.

Step 4:  Create Environment & Set Up Each Variable (SSMS)

Once you've deployed your project to the SSIS Catalog, you'll be able to see it in SQL Server Management Studio.

 

Next we want to create a new SSIS Environment (assuming it doesn't already exist). Environments are usually called Dev, QA, Prod, that sort of thing, but they could of course be used differently in some situations.

For setting up new variables within the SSIS Environment, you want to have SSDT open as well as SSMS. Copy and paste the name & value for each parameter from the SSDT parameters window into the SSIS environment variable window (note: I tend to use an EV prefix but that's just a personal preference). Modify any of the values if different on the server than were used in SSDT.

Step 5:  Configure Each Project (SSMS)

At this point we have an SSIS environment available with variables specified that coincide with our parameters. Now we're ready to let the project know that the environment exists (and which one to use, if there are multiple environments on the same server).

 

On the parameters page, we can associate each parameter for the project to the environment variable that was set up. In this example I kept the scope at the project level, but you can set them at the individual package level when appropriate.

In the screen shot above, you'll see there's one package-level parameter that is just set to "Yes." When you see this, you might be tempted to just set the connection values here too and not bother with referencing them to the SSIS environment variables. If you only have one or two projects, that method would be ok. However, if you have a larger number of SSIS projects that all make use of shared parameters and/or database connections, you'll find great benefit in changing the values of an SSIS environment variable once and allowing it to affect all projects.

Step 6:  Execute in SSMS Via the Catalog (SSMS)

At this point, everything is set up. Let's run it in SSMS to see if it works. 

 

Initially, you see alerts because SSIS doesn't presume to know which environment to use (even if only one exists). So, you want to check the box and choose the correct environment - this will always be true when a package is run on-demand like this.

Step 7: Create Agent Job and Associate Job to the Environment (SSMS)

Our last step is to set up an Agent job to run the package on a schedule. 

 

I won't go into detail on settings for the Agent job, but the key to bringing it all together is on the Configuration page where you specify which Environment the job should use.

Note that you can also specify a parameter value directly within the Agent job. In most situations I wouldn't do this since it could be confusing if different than the environment variable or the project configuration, but it is nice flexibility to have if used with caution.  

You Might Also Like...

Getting Started With Parameters, Variables & Configurations in SSIS 2012