July 2, 2009 at 6:19 am
Hi
I am executing a stored procedure in an OLE DB source. This procedure contains a select query that is formed depending on the parameter values. Even the source table is a parameter value.I use the sp_executesql command to execute this select query.
But it is not working. when i try to generate the columns by clicking on the columns tab, it gives an error "Invalid object name edify._csi_calls". What is happening is that the parameter values are not being passed by SSIS at design time to generate the metadata. The table name should have been edify.OMA_csi_calls, because i am passing "OMA" as a parameter to the SP and concatenating it.
I need to use stored procedures with the dynamic table name.(orders from above). Can't use inline queries in SSIS.
Any kind of help wiould be appreciated.
July 2, 2009 at 10:08 am
Source adapters in your data flow cannot be dynamic. They must return the exact same metadata each time they run and the SSIS validation routine must be able to determine metadata from the SP code or SSIS will not work.
Your SQL code (inline or SP) inside a source adapter must not be dynamic. Anything dynamic that you need to do needs to be inside control flow.
I assume that you are attempting to use some sort of loop container to execute a data flow and dynamically generate the pipleline data from variable values in your loop? Sorry, but this won't work. You need to set up a unique data flow task for each data flow that you want to work with.
Maybe if you expand on what you are trying to do, we can help come up with a more dynamic solution, but for now, you'll want to start thinking about creating a data flow for each table or source query.
July 2, 2009 at 12:10 pm
Thanks fo replying.
I am not trying any looping.
Even the resultset structure is the same for all the tables that i want to use as source. They only differ by the table names.
My ultimate intention is to replicate this package for a number of tables with the same structure differing only in the table name. And not making any change in the SP.
So the next time i can just change the package variable name that holds the table name and is passed to the SP...so that the rest of the code will work without any other changes.
July 2, 2009 at 12:18 pm
OK. SSIS will not be able to resolve the metadata from a dynamic query so you'll need another approach. If you are trying to cut down on development time as you add tables to your package, you could come up with a way to take an existing package, copy it, and edit the package to point to the new table.
A SSIS pacakge is stored as an XML file so editing the package can be pretty easy to do. You can copy the Xml file with a new name and edit the appropriate entries in the file to point it to a different table. You'll need to look through the package XML as I'm sure there's other entries that need changed to re-point everything to the new package name.
I would encourage you to do this on a copy of your existing package so you don't mess up any work that you've already done.
July 3, 2009 at 1:16 am
Thanks for the reply. That is a valid solution but unfortunately i have a constraint..I need to keep my SQL queries in the stored procedure itself because it contains a lot of business logic. So later on, if any changes are required, i do not need to hunt down every package to change the same query hundreds of times.
So from a maintainance or change-request point of view, i need to have the queries in the SP itself.
If i can't use dynamic queries in the SP, the only option i see is using if- else blocks in the SP and have seperate code within the SP for every table.
Any other way of doing it?
July 3, 2009 at 2:10 am
Perhaps this thread is relevant to what you are trying to achieve?
http://www.windows-tech.info/15/e7e4a96178d88c7e.php
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 3, 2009 at 3:28 am
Thanks a lot Phil. That should work..by having a dummy select statement to generate the metadata.
Will try it out as soon as i get some time..priorities have kinda changed.
Thanks again
July 6, 2009 at 9:28 am
Yes, that may help, but I think you'll still have some challenges to deal with. Data flows are coupled closely to the metadata returned by a source adapter. Once you 'declare' your metadata in your SP, the data flow task will only work for that specific data definition. So this will only work if all of your tables return the same columns with the same data types and they map to the same destination columns (again with the same data types).
I would create a new pacakge or data flow for each table. You can still have your SQL code in a stored procedure.
July 7, 2009 at 3:32 am
I tried it out and it works perfectly! thanks.
I did not face any complications because all my select queries are the same..differing only in the table name..i.e. the columns and their data types are exactly the same.
Only thing i had to add was an if-condition that would never be true before the dummy select statement, because SSIS was always returning the NULL values from the dummy select instead of going down further in the code..
Thanks a lot John, Phil.
July 7, 2009 at 11:30 am
Ah, yes. I see earlier in the thread that you said that the table names were the only differences. This will work much better than creating data flows for each table.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply