[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to Run a Scheduled Data Export to File

Posted on 2009-02-10
11
Medium Priority
?
658 Views
Last Modified: 2013-11-30
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.
0
Comment
Question by:webtechy
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 8

Expert Comment

by:isaackhazi
ID: 23599167
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
 
LVL 25

Expert Comment

by:slam69
ID: 23599193
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
 
LVL 22

Accepted Solution

by:
PedroCGD earned 336 total points
ID: 23599204
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 25

Assisted Solution

by:slam69
slam69 earned 332 total points
ID: 23599211
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
 
LVL 2

Author Comment

by:webtechy
ID: 23599216
Pedro, could you run through how to create an SSIS Package as that's the bit I'm struggling with ... cheers.
0
 
LVL 25

Expert Comment

by:slam69
ID: 23599230
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23599244
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
 
LVL 2

Author Comment

by:webtechy
ID: 23599515
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
 
LVL 25

Expert Comment

by:slam69
ID: 23599572
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
 
LVL 30

Assisted Solution

by:nmcdermaid
nmcdermaid earned 332 total points
ID: 23647035
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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question