September 1, 2011 at 11:07 am
It seems like this should be really easy, but I can't figure it out. Is SSIS really non-intuitive or something?
I create a new SSIS package, create a variable the type is Object.
Then in Control Flow I add an Execute SQL Task, set ResultSet to Full Result Set, I enter in the SQL statement SELECT TOP 10 * FROM msdb.dbo.backupset, and under the Result Set tab I enter 0 as the Result Name, and assign my variable.
That all seems to work fine: I run it, it turns green.
But now how do I write that result to a text file?
I add a Data Flow Task, and join it to the Control Flow Task, and... now what?
When configuring the Data Flow Task, there is no "Source" for the result I just created and stored in that variable -- the only sources seem to be a connection to a database, or a text file or XL file.
Where is Source = variable, or whatever??
Thanks!
September 1, 2011 at 11:51 am
Easy way to get started is to run the Export Wizard and save/edit the package.
You'll end up with 2 connection manager items: SourceConnectionOLEDB and FlatFileConnectionManager
You will also need an OLE DB source and a Flat File Destination in the data flow screen.
You won't need an execute SQL task.
But really run the wizard to accelerate the your way up the learning curve...
September 1, 2011 at 12:38 pm
Skip the variable and the Execute SQL Task. All you need for something like this is the Data Flow task.
The query you have in the Execute SQL Task, use that as your data source, using the OLE DB source (that's the one to use for SQL Server, as well as a lot of other things). Add in a Text File destination. Follow the directions on the screen.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 1, 2011 at 2:47 pm
I'm am running into the same situation. After successfully pulling data from an AD and saved to a variable I have no idea how to write it out or display it.
TIA
September 2, 2011 at 6:29 am
If you assign it to a variable, you'll have to step through the variable using a For Each Next loop, assigning each column in the dataset to a separate variable, and then use those to do whatever it is you're trying to do with the data.
If you simply use a Data Flow task, you can skip all of that and simply insert the results of your dataset query into another format (table, text file, Excel, whatever).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply