Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
theladycoder
Starting Member
16 Posts |
 Posted - 2008-08-19 : 10:41:44
|
| I am wondering if it is possible to automate a series of jobs in SQL 2005. I am needing to do the following on order:1. Automate the export of data from one table to another (There are a total of 6 separate tables that I will transfer data to 6 other tables)2. Automate the export of the transferred data from each of the tables to 6 separate pipe delimited text files that can overwrite the files and rename the files. For example, the base file name would be 123456_filename and the full file name would be 123456_filename_yyyymmddhhmmss. I would need the date part to overwrite everyday as this would indicate when the files were created.3. Automate to FTP the 6 pipe delimited text files to an outside serverI am fairly new to SQL 2005 and not quite sure how to accomplish this. I initially created my SSIS to transfer table between tables, but not sure if I can use them for this.In SQL 2000 I used jobs for certain things, but in SQL 2005 I am a little lost as to the steps to begin implementing. I am unsure of how to implement steps 2 and 3. How to I execute the data transfer to pipe delimited files (each time creating a new text file with the current date in the file name to be the date of when I export the data to file) and then execute ftping those files to an outside server?I have searched various SQL sites and gone through my books, but not sure if I am just overlooking how to do this procedure, or if language as to how things are referenced in SQL 2005 has confused me.One question, in 2005, are jobs and SSIS packages different. I have created jobs and SSIS packages for step 1. Just not sure if they are the same are not or if one is better than that other or do I use jobs to call on the SSIS package? I created these using SQL Server Management Studio. I have always done these type of tasks through a web page and using a server side language to do what I needed to do, but this is a first for me to do all of this within SQL. I am unsure of syntax as I imagine I will need to write the SQL code to export to file and create file names on the fly, as well as code the command to ftp to an external server.I created the following script to dynamically name my txt file:select ( rtrim(partnerID) + '_' + 'ITEMMASTER' + '_' + Replace(Convert(varchar(10), getdate(), 120), '-', '') + '000000' + '.' + 'txt' ) from tblNameSo, how when and where do I execute this script, as well as creating and executing a script within SQL to FTP? Thanks in advance for any assistance! |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-19 : 10:49:53
|
| 1)Use OLEDB task for it--on success2)Use OLEDB task (Source) and Flat file Destination:You can change dynamically like this:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=108920--on success3)Use FTP task for itUse this package and schedule it as a job in SQL server agent. and you should be good. |
 |
|
|
|
|
|
|
|
Automating several tasks