June 4, 2009 at 10:21 pm
I have a really simple SELECT statement & I want the results to a file so I can schedule it to run nightly. I need to use the file in a third party app.
My T-SQL is
SELECT field1, Field2
FROM MyTable FOR XML RAW, TYPE, ROOT('Inventory')
The results are fine in a query window, but how do I get the results to a file? And also schedule it?
I thought this would be an easy 15 minute job & have so far spent about 5 hours on it! I have scoured the internet for similar posts and most talk about SSIS which I am not entirely unfamiliar with & even though I know a little, it still has me stumped. There are articles about using XSLT translations which i don't really need - just the output directly from the SELECT is fine.
How can something so simple be so flipping hard? Oh, any answer needs to spell out the steps so a dummy like me can understand!
I have an Execute SQL Task. Where to from there?
-- Just as an aside - I could get it to output to a file by using SQLCMD, but I had that annoying '1 row affected' line at the bottom of the file. A bit of googling and found that SET NOCOUNT ON at the beginning of the SQL statement would kill that line. End result is that now I have exactly what I am after!! My question above still stands though. How can I do it through SSIS?
June 5, 2009 at 2:49 am
Am I being too simplistic by saying just add a job?
Under SQL Server Agent, right click jobs, new job and fill in the boxes.
Under steps, general tab holds your commands and advanced gives you somewhere to add the output file name.
You might have to play around a bit if the file already exists.
Under schedules you can .... well you get the idea....
June 5, 2009 at 6:24 pm
Sometimes the answer is so obvious I miss it...Why has not anyone else suggested this?
And then I found out why...
My output is not the XML I need, it is
Job 'My Job' : Step 1, '1' : Began Executing 2009-06-05 20:53:53
String data, right truncation [SQLSTATE 01004]
Assuming that truncation is the cause of the error, I couldn't find any setting within the job which defines how long the output string needs to be.
So, back to square 1. I thought this would be so simple! Other ideas?
June 8, 2009 at 9:41 pm
I guess from the silence that this is harder than what I thought.
Does no-one know how to get XML output (or really, just any SELECT output) to a file from SSIS?
June 9, 2009 at 2:46 am
Sorry I was out of touch. I was being a bit dumb as that output file is just the output messages from the job run.
I can't say I've output xml from ssis but you can get flat file outputs. I usually use the export wizard in ssms and save the ssis package to a file. Then open the .dtsx file in the BI dev suite if you want to edit the package or just create a job to execute the .dtsx file.
[In the job - steps - type - SQL Server Int Ser Package - Generaltab - package source = file system - package = your.dtsx filename]
But this still doesn't get you an xml output...
June 9, 2009 at 3:23 am
You can do it with BCP utility that works with a query that has the for xml extention:
bcp "select * from tempdb.dbo.Customers for xml raw, root('Root')" queryout c:\Temp\Customers.xml -T -c -SServerName
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 9, 2009 at 6:21 pm
Thanks twillcomp and Adi.
I thought I had seen somewhere that it could be done in SSIS and wanted to pursue that further. The bcp (being a DOS command, or should a say an operating system command line) is just like the sqlcmd solution which already gives me what I'm after. I'll try both solutions and see which ones suits my needs best.
cheers all
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply