I want to do something very simple in BIDS as a means to learning more about what I can and cannot do. Basically I want to:
1. run a query that returns a single data item (e.g. select 1 as one)
2. output that data to a flat file (or to excel or even to the BIDS output panel, if that is possible)
To get started, I added an Execute SQL Task element to the Control Flow tab, where I typed my simple query (select 1 as one). That works as expected. Now, I'd like to send the result set (Which is just one row and one column) to a file. So, I added a Data Flow task, but BIDS doesn't like that, complaining that the number of input columns is 0 (even though I know it is 1).
So, I guess I'm going about this wrong. How can I do this very simple thing in BIDS?
nope. no need of EXECUTE SQL TASK. you should be using a DATA FLOW TASK with OLEDB Source and flat file destination. Put data access mode as as sql command and put your query in it. Point flat file destination to a file connection which creates a file and on executing you'll get results exported to file
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
ONe question though: IT seems that I need a connection on the OLEDB source task even though it will never use it. (I think that's why I went for Execute Sql Task in the first place). Is there a way to run it without a source connection?
ONe question though: IT seems that I need a connection on the OLEDB source task even though it will never use it. (I think that's why I went for Execute Sql Task in the first place). Is there a way to run it without a source connection?
what do you mean by you never use it? you've run your query against a db isnt it? even EXECUTE SQL TASK needs a connection
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
How to do this with BIDS
Posted - 10/24/2012 : 16:37:02
