| Author |
Topic |
|
Glyph
Starting Member
4 Posts |
 Posted - 2004-06-02 : 15:54:30
|
| I'm trying to setup a scheduled process using DTS that saves the results of a query to a CSV file. The SQLTasks don't allow me to specify a file and the TextFile Destination connections don't allow me to specify a SQL Query. How does one connect the two?thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-02 : 16:04:43
|
| You connect the two with a Transform Data task. Go to the task menu and select it. You then select which is your source and which is your destination. You then double click on the arrow between them to define it.Tara |
 |
|
|
Glyph
Starting Member
4 Posts |
Posted - 2004-06-02 : 16:21:22
|
| Thanks Princess.I connected a SQL Server connection to a Text file destination connection using a Transform Data task. So what's the point of the SQL Tasks? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-02 : 16:23:33
|
| The SQL Task allows you to execute a SQL query, but it can't do anything else. A SQL connection connects you to a SQL Server. You transform data between two connections. In my DTS packages, I've usually got a SQL task that deletes data out of my staging table which is imported into during the transformation task.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-02 : 16:34:18
|
Why bother with DTS?USE NorthwindGOEXEC master..xp_cmdshell 'bcp "SELECT * FROM Northwind.dbo.Orders" queryout c:\test.csv -SNJROS1D151\NJROS1D151DEV -Ppassword -Uuser -n'GO Brett8-) |
|
|
|
Glyph
Starting Member
4 Posts |
Posted - 2004-06-02 : 17:00:37
|
| Thanks...That's how I used to do it...putting the sql into a scheduled task. I've been looking at some of the posts here and am wondering, does DTS have a bad rap? Is it just a clumbsy gui for stuff that can be done more easily through code? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-02 : 17:04:20
|
| DTS is considered to be the slowest of the 2 tools. For importing data, it's bcp and BULK INSERT that are faster than DTS. So if you can do it with bcp or BULK INSERT, then use those before using DTS. DTS certainly has its place for importing and exporting data, but not for simple stuff that can be done faster with another tool.Tara |
|
|
|
Glyph
Starting Member
4 Posts |
Posted - 2004-06-02 : 17:22:40
|
| So I guess you only want to use this when you need the 'T' in DTS... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-02 : 17:27:59
|
| Exactly. Other times to use it would be to convert data from another data source such as FoxPro or any other dbms that you can access via an ODBC driver. DTS definitely is a valuable tool, it's just overused. People find the import/export wizard and never realize that there are better tools to do the simple task that they are doing.Tara |
|
|
|
|
DTS Question