Solved

DTS package on SQL server

Posted on 2006-10-31
7
624 Views
Last Modified: 2013-11-30
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
Comment
Question by:vbothello
7 Comments
 
LVL 3

Expert Comment

by:sanjaykp
Comment Utility
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
 
LVL 9

Assisted Solution

by:dduser
dduser earned 250 total points
Comment Utility
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
 
LVL 3

Accepted Solution

by:
sanjaykp earned 250 total points
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:vbothello
Comment Utility
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
 
LVL 9

Expert Comment

by:dduser
Comment Utility
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
 
LVL 30

Expert Comment

by:nmcdermaid
Comment Utility
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
 
LVL 3

Expert Comment

by:sanjaykp
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now