This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Remove From My Forums

Automating SQL Table data transfer

Question
0

Sign in to vote

Hi All,

Currently we are fetching data from SQL tables using simple SELECT commands and copying data to xls file and copying xls files to remote server. Next day the user will use these xls files for some data requirement analysis.

Can you please tell me how to automate below tasks in SQL Server.

Step 1. Execute SQL commands automatically everyday at 12 AM

Step 2. Save the output of SQL command on local server in the form of CSV/or XLS.

Step 3. Copy CSV/or XLS file from local server to remote server.

Step 4: Keep latest xls file on local server and delete old xls file automatically from local server.

Thanks in advance.

Sunday, June 5, 2016 8:52 AM

Answers

0

Sign in to vote
This is a SSIS package was developed for you :-)

http://knowlton-group.com/using-ssis-to-export-data-to-excel/

Deploy the package to SSISDB and schedule (job) it later on

https://technet.microsoft.com/en-us/library/dd440761%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
- Edited by Uri DimantMVP, Editor Sunday, June 5, 2016 9:13 AM
- Proposed as answer by Sam ZhaMicrosoft contingent staff Monday, June 6, 2016 5:34 AM
- Marked as answer by Sam ZhaMicrosoft contingent staff Tuesday, June 14, 2016 8:15 AM
Sunday, June 5, 2016 9:11 AM

Answerer

All replies

0

Sign in to vote
This is a SSIS package was developed for you :-)

http://knowlton-group.com/using-ssis-to-export-data-to-excel/

Deploy the package to SSISDB and schedule (job) it later on

https://technet.microsoft.com/en-us/library/dd440761%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
- Edited by Uri DimantMVP, Editor Sunday, June 5, 2016 9:13 AM
- Proposed as answer by Sam ZhaMicrosoft contingent staff Monday, June 6, 2016 5:34 AM
- Marked as answer by Sam ZhaMicrosoft contingent staff Tuesday, June 14, 2016 8:15 AM
Sunday, June 5, 2016 9:11 AM

Answerer
0

Sign in to vote

If you want SLQ Server to scheduled it for you, see this.

https://www.youtube.com/watch?v=EfDv9dwh0I8

If you want Excel to automatically send the data to SQL Server, see the options below.

http://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm#Excel%20Data%20Export%20to%20SQL%20Server%20using%20ADO

Finally, to get Windows to schedule the task for you, see this.

http://windows.microsoft.com/en-us/windows/schedule-task#1TC=windows-7

That's all you need!

MY BOOK

Friday, June 10, 2016 1:25 PM