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.
| Author |
Topic |
|
jamie
Aged Yak Warrior
542 Posts |
 Posted - 2005-05-10 : 09:03:10
|
| hi,how can I run a stored procedure from a dts package which will output the data to a text file ? |
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-05-10 : 10:05:27
|
| I'm also trying for this, If you get a solution please do let me know.Karunakaran___________It's better to be loved and lost, than ever to be loved... |
 |
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2005-05-11 : 04:40:36
|
| Hi,I have used bcp instead of dts...DECLARE @FileName varchar(100), @bcpCommand varchar(2000)SET @FileName = REPLACE('C:\block_data_'+CONVERT(varchar,GETDATE(),111),'/','').txt'SET @bcpCommand = 'bcp "exec s_build_blocks" queryout "' SET @bcpCommand = @bcpCommand + @FileName + '" -U sa -P password -c'EXEC master..xp_cmdshell @bcpCommandGO |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-05-11 : 12:26:55
|
| For me, I dont think I can do that, Since I have some manipulation during transformation.Karunakaran___________It's better to be loved and lost, than ever to be loved... |
|
|
|
imsganesh
Starting Member
28 Posts |
Posted - 2005-05-11 : 14:53:43
|
| You can use the "Transform Data Task" in the DTS Package (3rd task in the first row). In the Source SQL query, Exec the stored procedure! |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-05-11 : 15:24:44
|
| I had tried that. The Transformation couldnt be able pick up the column names from SP and it says no source column available for transformationKarunakaran___________It's better to be loved and lost, than ever to be loved... |
|
|
|
imsganesh
Starting Member
28 Posts |
Posted - 2005-05-12 : 16:39:31
|
| I've many DTS packages which is extracting data from SQL tables & exporting into a flat file. Did you "Define the columns" of the output file in the Destination tab of "Tran Data Task"? |
|
|
|
|
|
|
|
|
run sp and export data