October 22, 2010 at 2:29 pm
I've read quite a few websites and still haven't found a nice walkthrough on how to output my data to a flat file.
Here's were I am:
Created a "Execute SQL Task" that calls a stored procedure with 2 variables, and have the resultset variable type as an object.
Added a "Flat File Destination", and that's where I'm stuck. MSDN is horrible for doing a walkthrough for this (at least I haven't found anything).
I just want to know how to take the resultset object, convert it to a fixed-width file.
The "Flat File connection manager" wants me to select a file, I'M TRYING TO CREATE THE FILE!
I'm sure I'm missing something stupid here, but any help would be great.
October 22, 2010 at 2:36 pm
gregory.anderson (10/22/2010)
I've read quite a few websites and still haven't found a nice walkthrough on how to output my data to a flat file.Here's were I am:
Created a "Execute SQL Task" that calls a stored procedure with 2 variables, and have the resultset variable type as an object.
Added a "Flat File Destination", and that's where I'm stuck. MSDN is horrible for doing a walkthrough for this (at least I haven't found anything).
I just want to know how to take the resultset object, convert it to a fixed-width file.
The "Flat File connection manager" wants me to select a file, I'M TRYING TO CREATE THE FILE!
I'm sure I'm missing something stupid here, but any help would be great.
Just calm down and type in the filename you want to use in the file name box.
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]
October 22, 2010 at 2:39 pm
Alvin Ramard (10/22/2010)
gregory.anderson (10/22/2010)
I've read quite a few websites and still haven't found a nice walkthrough on how to output my data to a flat file.Here's were I am:
Created a "Execute SQL Task" that calls a stored procedure with 2 variables, and have the resultset variable type as an object.
Added a "Flat File Destination", and that's where I'm stuck. MSDN is horrible for doing a walkthrough for this (at least I haven't found anything).
I just want to know how to take the resultset object, convert it to a fixed-width file.
The "Flat File connection manager" wants me to select a file, I'M TRYING TO CREATE THE FILE!
I'm sure I'm missing something stupid here, but any help would be great.
Just calm down and type in the filename you want to use in the file name box.
Ok, but what about the columns tab? How do I specify that my source is the execute sql task?
October 22, 2010 at 2:42 pm
gregory.anderson (10/22/2010)
Alvin Ramard (10/22/2010)
gregory.anderson (10/22/2010)
I've read quite a few websites and still haven't found a nice walkthrough on how to output my data to a flat file.Here's were I am:
Created a "Execute SQL Task" that calls a stored procedure with 2 variables, and have the resultset variable type as an object.
Added a "Flat File Destination", and that's where I'm stuck. MSDN is horrible for doing a walkthrough for this (at least I haven't found anything).
I just want to know how to take the resultset object, convert it to a fixed-width file.
The "Flat File connection manager" wants me to select a file, I'M TRYING TO CREATE THE FILE!
I'm sure I'm missing something stupid here, but any help would be great.
Just calm down and type in the filename you want to use in the file name box.
Ok, but what about the columns tab? How do I specify that my source is the execute sql task?
You obviously did not take the time to learn how to work with SSIS before trying to use it. Take the time to do some reading. Pay attention when you get to the section that deals with DataFlow.
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]
October 22, 2010 at 3:06 pm
Your problem is that you've chosen to use the Execute SQL task in combination with an object type variable. Not good. To get the data into a flat file from here, you'll need a script task and need to manually parse out the object variable properties and pipe it to a file....all within a script task.
The simple way to do this is to create a Data Flow task. Inside the Data Flow, you'll need a source adapter to get the data and a Flat File Destination. When you open up the Flat File Destination, you'll be able to create a new Flat File Connection manager. This is where you will define the column definitions and file properties.
October 22, 2010 at 3:09 pm
John Rowan (10/22/2010)
Your problem is that you've chosen to use the Execute SQL task in combination with an object type variable. Not good. To get the data into a flat file from here, you'll need a script task and need to manually parse out the object variable properties and pipe it to a file....all within a script task.The simple way to do this is to create a Data Flow task. Inside the Data Flow, you'll need a source adapter to get the data and a Flat File Destination. When you open up the Flat File Destination, you'll be able to create a new Flat File Connection manager. This is where you will define the column definitions and file properties.
Thanks John, I actually just found that out (about DataFlow task) reading "Answer 2" on this webpage: http://www.go4answers.com/Example/call-oledb-source-aftre-execute-sql-65411.aspx , now I'm just trying to get it to work with a stored procedure where I don't know what the columns are...but I'm assuming I'll find out that I have to manually enter those...
October 22, 2010 at 3:14 pm
SSIS Data Flows work off of pre-defined columns. Are you saying that the result set from the SP can change?
October 22, 2010 at 3:20 pm
John Rowan (10/22/2010)
SSIS Data Flows work off of pre-defined columns. Are you saying that the result set from the SP can change?
No.
My first thought was because the stored procedure that starts the job just calls 3 other stored procedures, and since that procedure didn't have any columns, I thought that that was the reason why I didn't have any columns. So I just referenced the last stored procedure (that does the final select statement) to just get the columns to populate on the ole db source, but that doesn't work either. After I enter the SQL Command, I get this at the bottom of the page: Error at Data Flow Task [DataReader Source [137]]: Cannot acquire a managed connection from the run-time connection manager.
October 25, 2010 at 10:34 am
The source adapter can usually glean the result set metadata from nested SP calls, but it sometimes gets confused when there are several nestings and many selects within the called SPs.
You may need to include an empty result set in your highest level SP. Hopefully your result set is not super wide so it won't take too much effort. You can accomplish this like such:
CREATE PROCEDURE dbo.HighestLevelProc
AS
SET NOCOUNT ON
IF 1=2 SELECT CAST(NULL as int) as Col1........
EXEC dbo.NestedProc
GO
Build out your IF 1=2....SELECT statement to mimic your final dataset's metadata.
October 29, 2010 at 8:03 am
John Rowan (10/25/2010)
The source adapter can usually glean the result set metadata from nested SP calls, but it sometimes gets confused when there are several nestings and many selects within the called SPs.You may need to include an empty result set in your highest level SP. Hopefully your result set is not super wide so it won't take too much effort. You can accomplish this like such:
CREATE PROCEDURE dbo.HighestLevelProc
AS
SET NOCOUNT ON
IF 1=2 SELECT CAST(NULL as int) as Col1........
EXEC dbo.NestedProc
GO
Build out your IF 1=2....SELECT statement to mimic your final dataset's metadata.
I have question related to STORED PROC. Result of my stored SP is a table. And I Have generated the result based on many Tenmptables insdie the SP. And When I am try to execute this SP in my DATA FLOW TASK. it is not abel to capture the result. When I bulid the query at data flow task it works..but when I try to go and check the colums tab..it would say...the temp table which I am using is not initialized...could you please help me out.
Thanks,
Preensheen
October 29, 2010 at 11:11 am
preensheen (10/29/2010)
John Rowan (10/25/2010)
The source adapter can usually glean the result set metadata from nested SP calls, but it sometimes gets confused when there are several nestings and many selects within the called SPs.You may need to include an empty result set in your highest level SP. Hopefully your result set is not super wide so it won't take too much effort. You can accomplish this like such:
CREATE PROCEDURE dbo.HighestLevelProc
AS
SET NOCOUNT ON
IF 1=2 SELECT CAST(NULL as int) as Col1........
EXEC dbo.NestedProc
GO
Build out your IF 1=2....SELECT statement to mimic your final dataset's metadata.
I have question related to STORED PROC. Result of my stored SP is a table. And I Have generated the result based on many Tenmptables insdie the SP. And When I am try to execute this SP in my DATA FLOW TASK. it is not abel to capture the result. When I bulid the query at data flow task it works..but when I try to go and check the colums tab..it would say...the temp table which I am using is not initialized...could you please help me out.
Thanks,
Preensheen
This is what I was referring to in your other post. I don't want to hijack this post so if you could go back to the other post and answer my question about your errors, I'd be happy to help you.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply