Dump table out to file

Question

Dump table out to file

Bec

SSC Enthusiast

Points: 140
More actions
August 2, 2009 at 8:46 pm

#212965

I need to create a dtsx package to dump out table data to file. The names of the tables to dump out are passed to the package as variables so it is all dynamic.
What methods do you gurus recommend?
A few coworkers have said that data flows cannot do it so I will need to use a script task. I want to check before I jump into the foreign land of .net programming.
Cheers

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

I absolutely, positively would NOT write a script for it. Check Books Online for BCP.

And, I'm not 100% sure, but I believe that there's a "text server" or something like that in DTS that will do the export for you. The reason why I'm not sure is because I always use a stored proc and BCP to do this type of stuff.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul_J Hall of Fame Points: 3698 More actions · Answer 2

How about BCP? You can even call that via the command line in an SSIS package.

Doing it all inside a Script Task should be the last resort in this case.

Bec SSC Enthusiast Points: 140 More actions · Answer 3

thanks guys but does bcp support outer quotation marks for field values? This is necessary when the field has a value that itself contains a comma or quotation marks.

Rupashri Gulawani SSC Veteran Points: 233 More actions · Answer 4

There will not be any straight forward way of doing it. I can suggest few options:

1. Write a code which will generate an Export package for you at runtime and execute that package.

2. Use Execute SQL Task followed by data flow task for Export.

a. Create a View say vw_ExportView.

b. Write an SP which will have an alter command for this view. This SP should have input as the table name.

c. Add Data Flow Task which should have source as this view name. Every time it will show data from different table based on what parameter you pass for the SP.

Thanks

manolomalaga SSCommitted Points: 1697 More actions · Answer 5

Hi, maybe interesting for you this doc:

http://msdn.microsoft.com/en-us/library/ms186757.aspx

and

http://www.codeproject.com/KB/database/Building_Dynamic_SQL.aspx

Bec SSC Enthusiast Points: 140 More actions · Answer 6

manolomalaga, I don't see how those links address my issue.

Bec SSC Enthusiast Points: 140 More actions · Answer 7

Rupashri Gulawani (8/3/2009)
There will not be any straight forward way of doing it. I can suggest few options:
1. Write a code which will generate an Export package for you at runtime and execute that package.
2. Use Execute SQL Task followed by data flow task for Export.
a. Create a View say vw_ExportView.
b. Write an SP which will have an alter command for this view. This SP should have input as the table name.
c. Add Data Flow Task which should have source as this view name. Every time it will show data from different table based on what parameter you pass for the SP.
Thanks

Thanks Rupashri but:

1. I don't understand what you mean here

2. This is not an option

Rupashri Gulawani SSC Veteran Points: 233 More actions · Answer 8

There will not be any straight forward way of doing it. I can suggest few options:

1. Write a code which will generate an Export package for you at runtime and execute that package. - You can write a class which will create SSIS package through .Net code.

2. Use Execute SQL Task followed by data flow task for Export.

a. Create a View say vw_ExportView as select * from xxx

b. Write an SP which will have an alter command for this view. This SP should have input as the table name.

For Ex:

CREATE Procedure usp_ExportData @TableName NVARCHAR(50)

AS

BEGIN

DECLARE @AlterView NVARCHAR(500)

SET @AlterView = 'Alter View vw_ExportView AS SELECT * FROM ' + @TableName

sp_executesql @AlterView

SELECT * FROM vw_ExportView

END

c. Add Execute SQL Task which will execute above mentioned SP.

d. Add Data Flow Task which should have source as this view name. Every time it will show data from different table based on what parameter you pass for the SP.

FOr Ex:

Data Flow Source = vw_ExportView

I hope things will be clearer now.

Please let me know if you have any queries.

Thanks

Rupashri

Thanks

Paul_J Hall of Fame Points: 3698 More actions · Answer 9

Rupashri Gulawani (8/3/2009)
There will not be any straight forward way of doing it. I can suggest few options:
1. Write a code which will generate an Export package for you at runtime and execute that package. - You can write a class which will create SSIS package through .Net code.
2. Use Execute SQL Task followed by data flow task for Export.
a. Create a View say vw_ExportView as select * from xxx
b. Write an SP which will have an alter command for this view. This SP should have input as the table name.
For Ex:
CREATE Procedure usp_ExportData @TableName NVARCHAR(50)
AS
BEGIN
DECLARE @AlterView NVARCHAR(500)
SET @AlterView = 'Alter View vw_ExportView AS SELECT * FROM ' + @TableName
sp_executesql @AlterView
SELECT * FROM vw_ExportView
END
c. Add Execute SQL Task which will execute above mentioned SP.
d. Add Data Flow Task which should have source as this view name. Every time it will show data from different table based on what parameter you pass for the SP.
FOr Ex:
Data Flow Source = vw_ExportView
I hope things will be clearer now.
Please let me know if you have any queries.
Thanks
Rupashri
Thanks

Rupashri, I don't see how programmatically creating an SSIS package will be any easier than doing what is required in a Script Task. I'd avoid this.

Bec, I haven't tried but perhaps BCP can do fixed width. Does your file have be CSV which is what I assume based on your embedded quote issue.

Rupashri Gulawani SSC Veteran Points: 233 More actions · Answer 10

Paul, I agree that generating SSIS at runtime through .Net code is not an easy task.

My second solution is comparitively easier one. Challenge with this solution can be source and destination column mapping in dataflow task. As Source can have any no of columns. If destination file is excel/access then it is easier. I am not sure about csv as I have not tried it.