Solved

DTS package on SQL server

Posted on 2006-10-31
7
630 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 3

Expert Comment

by:sanjaykp
ID: 17842911
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
ID: 17843052
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
ID: 17843478
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:vbothello
ID: 17844219
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
ID: 17844244
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
ID: 17846978
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
ID: 17848185
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
export sql server dbs 2 26
Question on sending emails using SQL Server 2008 R2 database mail 10 57
Report 8 27
sql server string_split 4 28
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

734 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