vbothello
asked on
DTS package on SQL server
I have a table for users in a SQL Database. Everyday people sign up and put in a new row of information. I want to be able to export the new rows every night as a new CSV file on an FTP location, I would also like to update the table with a flag so I do not query the information twice. Is this possible with a DTS package, can someone give me some insight or an example of how this is done.
Appreciate the help,
vb
Appreciate the help,
vb
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
dd,sanjay
It seems like I am on track, the only problem is that I need to push the new csv file into an ftp sever , I don’t see an option in the dts package where I can push a file into an ftp server. Does it have to be a separate batch process? Or can an ActiveX script accomplish this.
Thanks
Vb
It seems like I am on track, the only problem is that I need to push the new csv file into an ftp sever , I don’t see an option in the dts package where I can push a file into an ftp server. Does it have to be a separate batch process? Or can an ActiveX script accomplish this.
Thanks
Vb
Hi vbothello,
What you can do is keep the latest file in a directory afte FTP is finished remove this file. Same should be on the server for FTP.
Regards,
dduser
What you can do is keep the latest file in a directory afte FTP is finished remove this file. Same should be on the server for FTP.
Regards,
dduser
To FTP a file:
1. First export your file locally (with a consistent name) using a data pump or BCP
2. Either use the FTP task in DTS OR use the FTP command line tool (via an Exceute process task in DTS) to move your file.
1. First export your file locally (with a consistent name) using a data pump or BCP
2. Either use the FTP task in DTS OR use the FTP command line tool (via an Exceute process task in DTS) to move your file.
Vbothello,
Depends on the version of SQL Server are you using, SQL7 does not have it. I know that the FTP Task's there in SQL2K upwards. Please also ensure that you are uptodate with your Service Packs. (Should be sp_4 for SQL2K). You should have a File Transfer Protocol Task in your DTS package designer. It's very easy to create a FTP push through that. Simply insert the FTP server's IP address and point to the directory where you have converted your .csv file.
If you do not have the requisite version os SQL Server, then simply write a batch file to FTP the csv file to your FTP server, and use the DTS Execute Process Task to execute the batch file upon successful completion of the csv transform.
Cheers
Depends on the version of SQL Server are you using, SQL7 does not have it. I know that the FTP Task's there in SQL2K upwards. Please also ensure that you are uptodate with your Service Packs. (Should be sp_4 for SQL2K). You should have a File Transfer Protocol Task in your DTS package designer. It's very easy to create a FTP push through that. Simply insert the FTP server's IP address and point to the directory where you have converted your .csv file.
If you do not have the requisite version os SQL Server, then simply write a batch file to FTP the csv file to your FTP server, and use the DTS Execute Process Task to execute the batch file upon successful completion of the csv transform.
Cheers
If you'll export the data once daily at night you could have a datetime column that's works as a timestamp in your table that's populated by the default function GetDate(). This'll always put the current date and time into the column as soon as anyone inserts any data. Just add a where clause, or append to your where clause, to your query that'll only get rows that have been inserted today, if you have scheduled your package to run before midnight, or yesterday, if the package runs after mignight, as the case may be.
Cheers