webtechy
asked on
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.
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.
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
then you use teh agent to kcik off teh stored procedure and there you have it
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Pedro, could you run through how to create an SSIS Package as that's the bit I'm struggling with ... cheers.
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
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
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
ASKER
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).
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Full Convert
Data Sync
www.spectralcore.com
I use this to convert and sync data seamlessly...