January 24, 2007 at 2:20 pm
I've got an OLE DB Source.
I was given a stored procedure on another machine that returns a resultset that I need to use to generate data for this pipe.
With Data access mode set to "SQL Command"... If I put this in the "SQL Command Text" field...
exec usp_ETL_mysproc '200612'
... it works like a charm. As soon as I change it to...
exec usp_ETL_mysproc ?
... and assign User::vn_snp_myvariable, it breaks everything downstream.
I did find this on the microsoft forums which I thought was promising, but implemented it to no avail.
--------
The parameters used by an EXEC statement are mapped by name. The parameter names must match the names that the stored procedure, run by the EXEC statement, expects. So in this case, you should name your parameters as @startDate and @endDate, including the @ sign, instead of 0/1/2... etc.
The parameter used by a select statement are mapped by order.
----------
This seems like a pretty basic thing to need... I'm sure there are people out there either having the same problem or glad they figured it out already.
PLEASE HELP!!! My bag of tricks is empty!
Thanks,
Greg
January 24, 2007 at 2:37 pm
Kirk you can try this - excerpt of a previous post I made on this
There is one thing you may need to check first and thats the scope of your variable make sure its at a level where it can be seen in the ole db source the easiest thing to so is to set it at the level of the package. Once its been created at one level you can't change the scope. This was my probelm when I initially tried to do this a few weeks ago. I set the variable at the level of the transfom in which i set it and couldn't nsee it in the ole db source.
If its set at the correct level then all you need to do in the ole db source is set the data access mode to sql command and then eneter an sql statement e.g.
SELECT * FROM MyTable WHERE Something = ?
the parameters button will un grey click on it next to parameter 0 click on the variables drop down if your variable is the correct scope it will be in the list and you can map it if its not in here it has defined at the wrong scope. So you just need to check at which scope it is defined and recreate it at the corect scpe nad the you will be able to map it.
Or go Jamies route which is easier
http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx
hth
David
January 25, 2007 at 11:58 am
Kirk?
Anyway, thanks for the pointers Ned. I ended up getting it to work by using the "SQL Command from Variable" approach and a VB script to stick the correct parameter in there.
All the rhetoric I found on "stored procedure calls match parameters based on name... straight up SQL matches based on the order of the parameters... blah blah blah". Total crap. It just doesn't work.
Good luck to anyone who's barking up that tree. I say, cut your development time losses now and go with the "SQL Command from Variable" approach.
Hey Microsoft... for frig's sake, it's a simple stored procedure with an input parameter. Can you make it a little easier to implement in BI?
UNGH.
total time wasted: ~4 hours. Should I send them an invoice?
Greg
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply