Export Data using DTS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Export Data using DTS

Author

Topic

gspdts
Starting Member

4 Posts

Posted - 2005-06-24 : 09:45:41

I am new to DTS .I am using DTS to export data to a CSV file. So far the SQL query had a static table name. But now the table name is going to change every month (this process runs monthly). How do i make it dynamic in the SQL part in the DTS? MS Sql server 7.0

bagha
Starting Member

5 Posts

Posted - 2005-06-24 : 13:39:21

Use a Global Variable as an input parameter in the SQL.The SQL statement will be like :
...
Where Table_Name = ?
...
The "?" mark represents you are passing an input parameter in your SQL.

gspdts
Starting Member

4 Posts

Posted - 2005-06-24 : 14:32:05

i am not sure I can use the parameter for a FROM clause.

SELECT *
FROM ?

bagha
Starting Member

5 Posts

Posted - 2005-06-24 : 15:24:10

You can use dynamic SQL.If you are using SQL server, in your DTS the SQL should be : Exec ssp_testSP ? (pass the table name as parameter)

And the body of the stored proc should something like this :
create proc ssp_testSP(@vTable varchar(10))
as
begin
Declare @SQLString nvarchar(100)

Select @SQLString = "Select * From " + @vTable

EXEC sp_executesql @SQLString

end /* proc */

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-24 : 15:29:40

I'd just use bcp for this:

DECLARE @TableName sysname, @SQL nvarchar(4000)

SET @TableName = 'TableNameGoesHere'

SET @SQL = 'bcp DBName.dbo.' + @TableName + ' out C:\SomeFile.txt -c -SServerName -T -t, -r\r\n'

EXEC master.dbo.xp_cmdshell @SQL

Tara

gspdts
Starting Member

4 Posts

Posted - 2005-06-24 : 16:15:58

Bagha

I am getting an error saying No value given for one or more parameter.I had defined the global variable for this.Here is what i did

a) Created the Stored Procedure
b) Used the Import / Export Wizard to define the connection ,destination and on the SQL part i had Exec ssp_testSP ? and saved the pkg.
c) I opened the package and defined the parameter.
on execution of the pkg I had this error.

Please let me know

bagha
Starting Member

5 Posts

Posted - 2005-06-24 : 17:22:41

Here's what you have to do to make it work :
1. Open the DTS(in design mode).
2.Right click on the Transform Data Task.Make the parameter hardcoded.(i.e. sp_test 'Table1')
3.Click on the Destination tab(it should show all the columns of Table1)
4.Click OK
5.Right click on the Transform Data Task again. Replace the hard coded parameter with ?.Make sure your global variable's default value is your 'Table1'(replace Table1 with your table's name).
6. Click on Transformations tab.
7. Click new and Select 'Copy Column' and click OK.

It should work now. Atleast it's working for me :).

gspdts
Starting Member

4 Posts

Posted - 2005-06-24 : 23:27:54

Thanks a lot Bagha it worked fine for me. You made my day !!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources