How to Run a Scheduled Data Export to File

Dear All,

NB Relates to SQL Server 2005

I need to generate a daily report that generates a TXT file of the data required and saves it to the file system (actually an SFTP location as well). I can generate this file by pasting in the SQL command and selecting "Results to File".

However, what I would now like to do is to automate this daily task. As I understand it, I can do this with an SQL Server Agent. However, it looks like I can only paste in the SQL, but not do a "results to file" type option. I therefore think I need to create a SIS (SQL Server Integration Services) Package as the step type in the job. This is where I've got a bit stuck. As I understand, to create a SIS Package, you need to do this from SQL Server Business Intelligence Studio ... but when I open it I am prompted to create one of the following:

Analysis Services Project
Integration Services Project
Import Analysis Services 9.0 Database
Report Server Project Wizard
Report Server Project

Could you let me know which one to use and how to go about creating the correct SIS package (assuming I am on the right lines).

Many thanks.

Ben.
LVL 2
webtechyAsked:
Who is Participating?
 
PedroCGDCommented:
Create a package in SQL Integration Services and call that from a SQL Job.
Check attached images.
Regards,
Pedro
www.pedrocgd.blogspot.com

1.-Create-Job.JPG
2.-Create-Step-To-Call-SSIS.JPG
3.-Select-package-Store.JPG
4.-Select-SSIS-package.JPG
0
 
isaackhaziCommented:
If you have the money get one of these products: :)

Full Convert
Data Sync

www.spectralcore.com

I use this to convert and sync data seamlessly...
0
 
slam69Commented:
You can do this using BCP teh export utiiity and using a stored procedure to perform the function.

then you use teh agent to kcik off teh stored procedure and there you have it
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slam69Commented:
create teh below as a stored procedure

then set an agent task to run it using the following exec line

Exec yourstoredprocedurename 'c:\yourfilename.type, 'out','nameoftabletoexport

change teh variables to be yours'
Declare @cmd            varchar(1000)     
      Declare @dbname         varchar(1000) 
      /* Validation for filepath */
  
 
  If (@filepath is null)
      begin
            Raiserror('Please enter the FilePath ',16,1)
            Return
      end      
      /* Validation for Direction  */
      If (@direction is null) or (@direction Not In('In','Out'))
      begin
            Raiserror('Please enter the Direction(In/Out)',16,1)
            Return
      end 
      /* Validation for Table name */
      If (@tablename is null)
      begin
            Raiserror('Please enter the Tablename or Viewname',16,1)
            Return
      end
      else if (Object_id(@tablename)is null)
      begin
            Raiserror('Please enter a Validate Tablename or Viewname',16,1)
            Return
      end 
      --Get the database name.
      set @dbname = db_name()      
--bcp command.
set @cmd = 'bcp  '+@dbname+'..'+@tablename+' '+@direction+'  '+@filepath+' -t, -c -S -T'
      --To run the bcp using extended stored procedure.
      exec master..xp_cmdshell @cmd 
set nocount off
end

Open in new window

0
 
webtechyAuthor Commented:
Pedro, could you run through how to create an SSIS Package as that's the bit I'm struggling with ... cheers.
0
 
slam69Commented:
You need to select integration services project and then build teh data flow tasks, if yuou havent used SSIS before though Id recommend going through a couple of the online tutorials with the sample DB as its a bit tricky to get yoru head round building the connections and managing teh data flow tasks and error reporting
0
 
PedroCGDCommented:
Open a new Integration Services Project
Open the package.dtsx that appears by default.
...
now what you need to do inside SSIS Package? You already has the textfile in the server?
Regards,
Pedro
0
 
webtechyAuthor Commented:
The SSIS package basically needs to query a table and export the results to a text file e.g. userdata_yyy-mm-dd.txt (wasn't aware you could export with a stored procedure).
0
 
slam69Commented:
course you can, before 2005 came out BCp was pretty much teh only way of doing it ( well dts as well but BCP has always been my preferred way of doing it)

SSIS is new for 2005 Pedro's way will work but i think mine is just as easy and you dont need to reinvent the wheel, all teh information for genearting BCP exports is out there in the land of google
0
 
nmcdermaidCommented:
You could also use SQLCMD to export text files from a SQL Statement. I'm not sure if the format is what you're after.
Both BCP and SQLCMD are 'command line' tools which can be run from any scheduler.
An example of using SQLCMD:
SQLCMD -S YourServer -E -i <path to your a file containing your sql query> -o <path to your output file>
This uses a static output file name but then you can use a variety of tools to rename it and move it.
 
It just depends on whether you're more comfortable with a UI type of tool or a command line type of tool. Personally I use simple command line tools for simple tasks and more flexible tools (SSIS) for more complex tasks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.