February 9, 2010 at 3:11 pm
I need some help finding a way to use the results of a query against a SQL Server 2005 table (containing 1 column with 5 records) as a parameter in the where clause for a data flow task pulling data in from a Teradata table. I've found a lot of examples online of different parts of this problem, but nothing that brings it all together. I'm having a really difficult time figuring this out on my own. Any help would be appreciated.
February 9, 2010 at 3:27 pm
I found a blog post that sounds like the way I have to go, but without any code examples, I'm finding it impossible to visualize how it's supposed to work. http://microsoftdw.blogspot.com/2005/11/parameterized-queries-against-oracle.html
Basically the blogger states to create a string variable that houses the query to use in the OLE DB source adapter. While I understand the concept, I can't seem to find examples of how to do this properly, including the parameter (which has to support multiple char values).
Again, any help would be appreciated.
February 9, 2010 at 4:48 pm
OK, here's what the blog is referring to.
First, set up a package level string variable for your sql command and a variable for each of the parameters that you'll be using. Use the expression builder to set up your SQL command variable to build out the SQL statement. Here's an example of a working expression:
"SELECT COUNT(*) as cnt FROM E2E.factContacts WHERE ReceivedDateKey BETWEEN " + (DT_STR, 10, 1252) @[User::BeginDate] + " AND " + (DT_STR, 10, 1252) @[User::EndDate]
Don't forget to set the variable's EvaluateAsExpression property to 'True'.
Next, on the Control Flow, you'll want an ExecuteSQL task that queries your SQL box and gets the values that you want to use in your Teradata query. Congfigure the ExecuteSQL task to return the values into package level variables. There's a great article out on sqlis.com on how to do that here. This will get the values into the variables that will be used by the expression for your SQL command variable.
Then, you need your Data Flow task. Set the OLE DB Source Data Access Mode property to 'SQL command from variable' and set the Variable Name property to your SQL command variable.
Make sure you keep your variable data types straight and use the proper casting syntax in the expression.
February 10, 2010 at 12:09 pm
Thanks so much! I eventually figured out how to get the expression up and running, but I was running into a problem with my variable. It was an object-type and the expression can't use an object variable. So I had to build in a For Each loop and a script task to read through the recordset and store the values into a string variable.
Public Sub Main()
Dim rgn_list As String
rgn_list = Dts.Variables("rgn_val_out").Value.ToString
If Len(rgn_list) > 0 Then
rgn_list = rgn_list + "', '" + Dts.Variables("rgn_val").Value.ToString
Else
rgn_list = Dts.Variables("rgn_val").Value.ToString
End If
Dts.Variables("rgn_val_out").Value = rgn_list
Dts.TaskResult = Dts.Results.Success
End Sub
My expression was set up so that the variable would populate inside a pair of parentheses and single quotes. That way I only needed another pair of single quotes surrounding a comma in the event that there is more than one value in the recordset.
This ended up working beautifully.
February 10, 2010 at 1:05 pm
That's one way to go. An alternate solution would be to have your ExecuteSQL task call a stored procedure. You can then use the SP to return your values in the comma delimted list instead of using the script task. Either way will work, it's really a personal preference thing.
I work a lot with interfacing Teradata and SQL Server and I've developed some automated processes that use Teradata's Fast Export to pull data down into my SQL box. I have situations where I need to limit the Teradata query based off of results from the SQL Server side. I have been toying around with a # of ways to make this work. For me, using variables and the expression builder are not an option because I am using Fast Export. The OLE DB connection is way to slow for Teradata, especially for as busy as our system gets.
Most of the time, my queries will need to be limited by date stamps so I think I am going to create a dates paramter table on Teradata that looks something like this:
Queryname varchar(256),
BeginDate date,
EndDate date
The table will be pre-loaded with the correct date ranges for a certain query. Each of my Fast Export queries will JOIN into that table for date parameters and then delete the parameter row from the table so that the next run will pick up the next set of date parameters. This will save me from trying to dynamically update my Fast Export queries.
This approach would be a bit more challenging with what you are trying to do so I'm guess I'm just sharing so you know that there may be other options for you like using one of the Teradata utilities to load your SQL Server values up into Teradata where you can use them in your queries there. Just a thought.
February 10, 2010 at 1:16 pm
Thanks again! Yeah I thought of using a table in Teradata to control the task also, but I have limited access to the databases and the only one I can create tables in is open to everyone and their mother. Needless to say, space is becoming a commodity there. So I opted to go this route, even though the control table in question would be relatively small.
Unfortunately, I'm still pretty new to all of this. I'm largely self-taught and I haven't gotten to SPs just yet. I'm also not familiar with Teradata's Fast Export. I'll have to look into that, though something tells me I don't have the access rights to use it. While I more or less have free reign of our SQL Server, Teradata is locked down tight.
I appreciate all of your help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply