February 2, 2011 at 10:56 am
After lots of struggling, I managed to called stored procedures with INPUT and OUTPUT parameters within a "Execute SQL Task".
To cut short a long story, it's much easier/cleaner using an ADO.Net connection than an OLE DB one.
My step 2 is to call a stored procedure within a Data Flow Source, with INPUT (only) parameters.
Using a (ADO.Net) Data Reader, there seems to be nowhere in the designer to specify parameters, so I assume it can't be done.
Which leaves me with the dreaded OLE DB Source...
I declared my SQL Command like this
SET FMTONLY OFF;
EXEC Maintenance.p_XmlUpload @XmlData = ?,@XmlJobDef = ?
and defined my two parameters using the names "@XmlData" and "@XmlJobDef" because I read than when using the EXEC form, I had to specify the parameters by name.
Now, the problem is that my data flow component does not define any columns...
If I try to parse the query, I get "Attempted to read or write protected memory... blablabla" that does not sound good.
If I replace my two "?" by NULL then it's fine, I get dummy data because I programmed my stored proc to do that but it does not really help because I need to pass these parameters...
Any ideas what to try before hanging myself??? :crying:
February 3, 2011 at 4:10 am
With everything I have read so far, I have little hope of achieving this through one step
So, just in case someone is interested in my answer, I will use two steps...
I will send my (complex) parameters through a "Execute SQL Task" with stored proc via an ADO.Net connection because it's far cleaner.
That stored proc will store all parameters in that database against a Unique Identifier
Then, I will start my Data Flow task and use a stored procedure with a single parameter as a Data Source.
Because my Unique Identifier will be simple (an INT), I can easily build the SQL Statement via some variable or expression.
My stored procedure can then pick up the parameters from that table and delete them straight away to keep things clean.
A new day begins! 😛
February 3, 2011 at 11:24 am
there is a work around. Expose metadata that will be flowing though the pipeline in your original Stored Proc. For eg. last statement of your stored proc will be something like following right now.
select empid, empname, dob from Employee
that means you want empid, emapname and dob to flow through the dataflow pipeline.
Modify your proc something like this
create proc sp_getdata
@dept varchar(10)
as
begin
--exposing metadata
if (1=2)
begin
select 1 as empid,
'abc' as empname,
'1/1/1900' as DOB
end
--write your actual code here
end
hope this helps.
Thanks,
Devesh
February 3, 2011 at 1:23 pm
What Devesh posted above is the workaround. The reason is that SSIS wants to build out metadata, and it will use the first SQL statement of any kind in the proc to do it. So, if it's not a select, it screws up. If it's not the final select, it screws up.
I've used the method Devesh posted above a number of times to work around the issue. Is it the best choice? Probably not, but it's what we've got as best practice right now.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 3, 2011 at 3:50 pm
I thought I had tried something similar which I have used with various client software, like LINQ for SQL, SSRS, etc...
It goes something like
DECLARE @Dummy INT
SELECT @Dummy = COUNT(*) FROM sys.tables
IF @Dummy IS NULL
BEGIN
SET FMTONLY OFF
SELECT ... my dummy statement
RETURN 0
END
-- and then my real code
...
I suppose it should have the same effect.
If you reckon that should fix it, I'll have another go...
Thanks
February 3, 2011 at 3:56 pm
The reason that didn't work is you have a select statement before the metadata definition.
SELECT @Dummy = COUNT(*) FROM sys.tables
The first statement that isn't a set of some kind in the proc needs to be the definition. It's the only way to make this workaround function properly. That's why the WHILE 1=2 bit exists at the start of it. You never want it to trigger, it's only there for a definition.
I usually wrap it like so:
CREATE PROC xyz
AS
SET NOCOUNT ON
WHILE 1=2
BEGIN
SELECT CAST(NULL AS VARCHAR(50)) AS Col1, CAST (NULL AS INT) as Col2...
END
... real proc here.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 3, 2011 at 4:17 pm
Hum...
I gave my two params a default value (NULL)
If I try this
EXEC Maintenance.p_XmlUpload_ForTest
I can parse my statement and the columns are generated
But if I try to add the 2 parameters like this
EXEC Maintenance.p_XmlUpload_ForTest @XmlData = ?,@XmlJobDef = ?
I get a warning about memory corruption.
My two parameters are XML by the way but I tried by using a single INT parameter and I had the same error...
Am I missing something???
February 3, 2011 at 4:22 pm
You won't be able to parse, unfortunately, with the parameters. Turn bypass prepare to true, make sure your parameters are named 0, 1, 2 (etc), and give her a whirl.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 3, 2011 at 4:43 pm
OK, I switched the DelayValidation to True and I managed to get everything fine with an INT parameter but still no joy with the XML parameters.
Since I managed to get the "Execute SQL Task" also working with an OLE connection but never with the XML parameters, I suppose it's "consistent".
Having said that, I do really appreciate your help but this all thing seems so clumsy I think I'll stay clear of it.
In my numerous attempts, I got so many misleading and weird error messages that I dread the idea I might come back a year later to fix something and go round so many cirlcles a few more times...
This tool seems to require "expert" knowledge beyond what is documented in the manuals and that is usually a good indication that there must be a wiser solution out there...
Once again, many thanks for your help.
Eric
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply