Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 638
  • Last Modified:

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
0
vbothello
Asked:
vbothello
2 Solutions
 
sanjaykpCommented:
Yes it's possible through DTS, however, you may want to consider an easier option.

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
0
 
dduserCommented:
Yes this is possible with DTS Package, first of all you need to create a DTS package which runs every day after 12 at night and it takes last days data and update the flag.

Following things you need to do:-

1. Create DTS Package
2. Database Connection as Source
3. Create Text File Connection as Destination For e.g. put the file Trial.csv as destination
4. Map the Source and DEstination using Transform Data Task
5. Select Source as Database Connection and Destination as CSV File Connection
6. Click on the Transform DAta Task, in query fetch yesterdays data with the fields you required.
7. Select File Transfer Protocol Task, configure the same.
8. You can select Workflow on Success for each task.
9. After all success update the data to done. by taking Execute SQL task.

Regards,

dduser

0
 
sanjaykpCommented:
Thanks, I do understand that the creation of a package will be required for moving the data, what I was trying to point out was the bit about querying the data twice and updating your flag. Perhaps, I wasn't clear;

1. Create an extra column in your table and give it a datetime data type and a default value of GetDate()

ALTER TABLE yourTable
ADD someColName datetime default GetDate() NOT NULL

This (someColName ) is your flag column. Then instead of nominating a file transfer in your Data Transformation Task, specify a Query and put down the SELECT statement as such;

SELECT aCol, bCol etc
from yourTable
where datediff(dd, GetDate(), someColName)  = -1 -- this will always give you a result set that has been inserted yesterday. You also have the option of selecting or not selecting the flag column in your query.

Hope that's clear.

Cheers
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
vbothelloAuthor Commented:
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
0
 
dduserCommented:
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
0
 
nmcdermaidCommented:
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.
0
 
sanjaykpCommented:
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now