February 22, 2011 at 2:00 pm
Hi All,
I've created a SSIS package that
1. Calls A stored procedure in the ExecuteSQLTask
2. That ExecuteSQLTask has the Result Set Type = "RowSet"
3. I populated a object variable with that data.
I want to now export this result set to a txt file. I've tried to pass it along to other flow tasks but I'm stuck. Any suggestions, or better ways to accompish this same task. I don't need to manipulate the data just export it.
February 22, 2011 at 2:04 pm
SQL Dude-467553 (2/22/2011)
Hi All,I've created a SSIS package that
1. Calls A stored procedure in the ExecuteSQLTask
2. That ExecuteSQLTask has the Result Set Type = "RowSet"
3. I populated a object variable with that data.
I want to now export this result set to a txt file. I've tried to pass it along to other flow tasks but I'm stuck. Any suggestions, or better ways to accompish this same task. I don't need to manipulate the data just export it.
Why are you trying to do it that way? Why not use your stored procedure in an OLEDB Source in a Data Flow? That would be much easier.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 22, 2011 at 2:09 pm
I tried that way but it didn't like the temp tables in the stored procedure. I tried to copy the text of the procedure and use it as a SQL Command but it would only run if I executed it under the ExecuteSQLTask.
February 22, 2011 at 2:15 pm
SQL Dude-467553 (2/22/2011)
I tried that way but it didn't like the temp tables in the stored procedure. I tried to copy the text of the procedure and use it as a SQL Command but it would only run if I executed it under the ExecuteSQLTask.
I'm think you should go back to the way I suggested. Sounds like you have something in you stored procedure that needs fixing. You might also want to consider table variables instead of tem tables.
In order for the stored procedure to work, it must return the required data. If you procedure stores data in temp tables, it should end with a SELECT statement that returns the data.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 22, 2011 at 2:20 pm
SQL Dude-467553 (2/22/2011)
I tried that way but it didn't like the temp tables in the stored procedure. I tried to copy the text of the procedure and use it as a SQL Command but it would only run if I executed it under the ExecuteSQLTask.
Temp Table? You're probably running into a meta-data issue, a known problem.
At the beginning of your proc, do something like the following:
CREATE PROC sp_works
@paramlist...
AS
WHERE 1=2
BEGIN
SELECT CONVERT( NULL AS datatype) AS column1....
END
... Actual proc
GO
What that first WHERE 1=2 does is makes sure the first select statement the ssis package runs into in the proc creates the correct metadata for the actual final select statement that will return to it (and never run it, by the where clause). Just make sure you build out the WHERE 1=2 to be the exact datatypes/structure you expect to return from the final select.
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 22, 2011 at 2:31 pm
Something like this?
CREATE PROCEDURE [dbo].[sp_Myproc]
AS
WHERE 1=2
BEGIN
SELECT convert(NULL AS varchar(850)) AS column1
END
Select....
It should return one row that could be as large as 850 characters.
February 22, 2011 at 2:58 pm
SQL Dude-467553 (2/22/2011)
Something like this?CREATE PROCEDURE [dbo].[sp_Myproc]
AS
WHERE 1=2
BEGIN
SELECT convert(NULL AS varchar(850)) AS column1
END
Select....
It should return one row that could be as large as 850 characters.
Well, only if your actual proc returned a single VARCHAR(850) column. You want it to basically define the actual select statement at the end that you're going to return to the SSIS component.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply