August 18, 2015 at 8:04 am
Hello, I'll try to explain my requirement as clearly as possible. I have an ADO.NET source that pulls a result set based on a SQL command, then loads the result set to an OLEDB destination. This all works just fine. The piece that I want to make dynamic so I can automate this package completely is the following:
the SQL command text pulls from several TERADATA views where the account id is in (sometimes hundreds of customer id's that change from week to week). My first reaction was, well, let's add a SQL task prior to this step to refresh a sql table to hold the new customer id's and all that needs to be done is modify the SQL text on the ADO.NET source to say "where customer id in (select customer id from source table), but immediately I was told that Teradata doesn't communicate with SQL. So, my question for all seasoned SSIS developers is; what's the best way to automate the package so that the SQL text inside the ADO.NET source pulls the pertinent data for those ever changing customer id's. Let me know what add'l info could help.
Thanks,
John
August 18, 2015 at 9:49 am
Not sure i understand.
What is the existing sql command that works. If you could post.
and how might that command change to be dynamic. (most probably answered when existing source command posted).
August 18, 2015 at 11:14 am
I'll explain the steps.
SQL task truncates sales table in SQL db.
Data flow:
ADO.NET source connects to TERADATA source with Data access mode: SQL command.
SQL command queries several views from TERADATA source for sales activity for specific customer id's (i.e. select customer id, sales, orders, units from DW_VIEWS.CUSTOMER_SALES where customer id in (00100,00105,00107, etc) <-- these customer id's I pull manually by querying aSQL table in SSMS, then I copy/paste those customer id's inside the ADO.NET source sql statement.
OLE DB Destination sends the sales result set for those customers to the SQL table that was truncated in the SQL task at top, thus, refreshing the table for the new week.
I'm trying to figure out how to pass the new customer id's dynamically to the sql statement inside the ADO.NET source task. My first thought was to add a SQL task as the first step to refresh another table with the new customer id's, so that table could be leveraged/called inside the SQL statement and dynamically look up the sales for those customers in that table. That way, i won't have to open the SSIS package every week to type/copy/paste the new customers for that week before executing the package
I believe this is very detailed now, but let me know if any add'l will help.
August 18, 2015 at 11:18 am
i do something like that, where truncate and insert a bunch of ID's from an AS400 connection, which then gets stuffed into a staging table on my Source SQL Server.
then i can join the SQL query to the staging table., in preparation for getting data to send to my Target source.
that avoids me trying to do any dynamic sql or other hoops to jump through.
Lowell
August 18, 2015 at 11:33 am
I apologize but I forgot to mention in my reply that the Teradata connection does not communicate with SQL tables, so if I join the staging table w/the customer id's inside the SQL statement, it will error out.
August 18, 2015 at 11:39 am
Perhaps the following:
Create a string variable to hold the SQL command, and have it's value derived from a dynamic Expression. On the OLE DB Source, set Data Access Mode to "SQL command from variable".
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 18, 2015 at 11:50 am
latingntlman (8/18/2015)
I apologize but I forgot to mention in my reply that the Teradata connection does not communicate with SQL tables, so if I join the staging table w/the customer id's inside the SQL statement, it will error out.
no what i meant was to use an SSIS package to pull the ID's from a Teradata OLEDB data source, and insert then into a Staging table on your SQL Server.
then you can have a different SQL OleDB datasource join the table and the staging table featuring ID's together, all in the SQL environment.
Lowell
August 19, 2015 at 2:42 am
Easiest way would be select customerid to a object variable the iterate through the variable with a foreach loop container with your ado.net source sql being select customer id, sales, orders, units from DW_VIEWS.CUSTOMER_SALES where customer id = {customerid variable}.
August 19, 2015 at 2:15 pm
Here's what I've done so far and it works:
- Added sql task which execs stored proc which refreshes sql table with new customer #'s.
- Created a variable "CustomerList" data type Object which will store the new customer #'s from the table refreshed in the first SQL task.
- Added 2nd sql task which has sql statement: select CustomerNo from dbo.CustomerTable; Result Set is set to Full Result Set and in Result Set window, Result Name is 0 and VariableName is the variable I created above.
- Enclosed these tasks inside a sequence container and when I execute the container all pieces run successfully.
Now I need to retrieve the result set from the variable to use it in my sql statement inside the data flow task, and this is where I'm getting stuck! How do I to tell my sql code inside the ADO.NET datasource step to return data for those customer id's that went into the variable from the previous sql task??
August 20, 2015 at 3:38 am
Firstly can you not use an oledb source instead of an ado.net source in data flow. i say this as an oledb sql command will allow you to easily set parameters on sql command.
secondly because you cannot join the result sets the easiest way qould be to iterate through you object variables stripping out the row variables on each for each to a parameter to use in your oledb source.
so for each loop container > enumerator = "foreach from variable Enumerator" with variable being customerlist variable.
on variable mappings say column 1 is CustomerID in customerlist RS so map CustomerID variable to column 1 (i think would be 0 as zero is first)
in foreach loop place data flow task with oledb source.
sql command is Select * from somecustomertable where customerID = ? where "? mark denotes a parameter"
in parameters map parameter0 to variable CustomerID.
this is the best i can think of to iterate through the list rather than creating one sql stament for the whole list due to join constraints.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply