I encountered a situation on my last SSIS project in which I needed to be able to populate the query in lookup with a where clause that referenced a project parameter. This wasn’t something I had ever needed to do in the past, so I had to do a bit of digging to figure it out. Luckily, I found this post by John Welch, which led me in the right direction. I’m posting my steps in detail here both to help anyone else trying to figure this out and also so I have it for reference should I need to do this again.
The Situation
I had warranty registration data that contained addresses. Earlier in the project, we used Melissa Data to do some address cleansing and to retrieve MSA and county FIPS codes for each address. Melissa Data returns results with a confidence score that reflects how well the address matched. We determined that we needed a confidence score of .8 in order to use the data. The client asked that we make an environment variable for the required confidence score so it could be easily changed without having to re-deploy the SSIS project. Later in the project, we had some warranty registration data for which we needed to look up the MSA. But we only wanted to return the MSA if the confidence score tied to the address was at or above the required confidence score we had set in the project parameter.
I already had a project parameter for the required confidence score.
So I made a package-level variable to hold the query for the lookup that referenced the project parameter. Then I set the lookup property to use that query.
The SSIS Setup
I created a variable in my package to hold the query for the SSIS lookup which referenced the project parameter in the where clause. (The scope was the package and the data type was String.)
I already had my data flow populated with the lookup for MSA. I set it to full cache and entered a query in the connection to initially populate the fields that would be returned (simply my lookup query without the where clause).
Next, I opened the data flow properties, located Expressions and clicked on the ellipses to open the Property Expression Editor. I found the SQLCommand property for my MSA lookup and set it to my package variable that contained my query.
Since I had BIDS Helper installed, I could see that I had an expression set for the lookup as denoted by the pink glyph.
And that’s all there is to it.
I could have avoided the extra variable and put the lookup query directly into the property expression, but I prefer having the variable to reference instead of digging through the properties.
Data Savvy 
I was wondering if you have figured out how to do this same thing in BIML? I have been using the same method for the Attunity Drivers (Oracle Source) data flows and while I have a BIML that should work, it does not create the [LKP_MSA].[SqlCommand] as an available option (Expression).
Hi, Jason. It’s just an expression inside of the data flow. I verified this by taking my example package for this blog post and reverse engineering the BIML using Mist. It ends up looking something like this: (WordPress kept stripping out my XML and I’m too tired to figure it out right now so it’s an image. Sorry!)
Hope that helps!
That is what I originally had. This post explains the issue a little better. There is a problem with BIML not creating the appropriate ExternalProperty options in the generated code.
https://codecooper.wordpress.com/2016/01/08/attunity-and-biml/
The expression works just fine with an ADO source. Here’s an example. I don’t have an Oracle install to test the Attunity driver.
<Biml xmlns="http://schemas.varigence.com/biml.xsd"> <Connections> <AdoNetConnection Name="CM_ADO_DB" ConnectionString="Data Source=.\sql2014;Integrated Security=SSPI;Connect Timeout=30;Database=DEMO;" Provider="SQL" /> </Connections> <Packages> <Package Name="ADONET Source"> <Variables> <Variable DataType="String" Name="QuerySource">SELECT 1 AS foo;</Variable> </Variables> <Tasks> <Dataflow Name="DFT Source"> <Transformations> <!-- This name matters --> <AdoNetSource Name="Oracle Source" ConnectionName="CM_ADO_DB" > <DirectInput>SELECT 2 AS foo;</DirectInput> </AdoNetSource> <DerivedColumns Name="DER Placeholder"></DerivedColumns> </Transformations> <Expressions> <!-- The name here of [Oracle Source] must match exactly what's above --> <Expression ExternalProperty="[Oracle Source].[SqlCommand]">@[User::QuerySource]</Expression> </Expressions> </Dataflow> </Tasks> </Package> </Packages> </Biml>Any ideas on how to do something like a custom property? It looks like when they added Attunity as a datasource they forgot to add some of the CustomProperties that were there for the original workaround.
“HR”.”EMPLOYEES”
SELECT * FROM “HR”.”EMPLOYEES”
100
0
32768
1252
1
well the code won’t post, you will have to look at the here.
http://bimlscript.com/Walkthrough/Details/67
I’ll take a look at it in a bit. FYI, the code won’t post because you have to do html encoding so the XML tags will show up. I learned this morning that you can use the code formatter on Manoli.NET for this.
Lets try that again.
<CustomProperties> <CustomProperty Name="TableName" DataType="String" SupportsExpression="true" Description="The name of the table to be fetched.">"HR"."EMPLOYEES"</CustomProperty> <CustomProperty Name="SqlCommand" DataType="String" SupportsExpression="true" Description="The SQL command to be executed.">SELECT * FROM "HR"."EMPLOYEES"</CustomProperty> <CustomProperty Name="BatchSize" DataType="Int32" SupportsExpression="true" Description="The number of rows fetched in a batch.">100</CustomProperty> <CustomProperty Name="PrefetchCount" DataType="Int32" SupportsExpression="true" Description="Number of pre-fetched rows.">0</CustomProperty> <CustomProperty Name="LobChunkSize" DataType="Int32" SupportsExpression="true" Description="Determines the chunk size allocation for LOB columns">32768</CustomProperty> <CustomProperty Name="DefaultCodePage" DataType="Int32" SupportsExpression="true" Description="The code page to use when code page information is unavailable from the data source.">1252</CustomProperty> <CustomProperty Name="AccessMode" DataType="Int32" TypeConverter="AccessMode" Description="The mode used to access the database.">1</CustomProperty> </CustomProperties>Thanks for this! Really saved a lot of trouble!
Just saved my life! Thanks a lot … Cheers!