INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Export data from multiple views to multiple CSV files

Export data from multiple views to multiple CSV files

(OP)
Hi,
I have 677 views (SQL 2008R2) from where I need to export data to 677 csv files (one to one)
The csv files also need to get the name of the view from where the data is coming from.
I have a query which gets the view names which I quess will be the result set.
Can anyone help create a SSIS package that will do this?
Thanks

RE: Export data from multiple views to multiple CSV files

as SSIS is quite sensitive to metadata you have 2 main options for this.
option 1 - create a simple ssis package with
1 execute sql to get the view name and assign it to a variable
2 dataflow that executes a select from the view - this select should concatenate all fields with a delimiter (comma or tab, and field delimiters if you so require) so the resulting recordset is a single field.
the output file should be defined as ragged right and with a field big enough to hold the biggest of your views output plus any delimiter you add. connection string of the output file should be made from an expression where you concatenate both the folder location and the view name and whatever else you wish.

option 2 - programatically create the package on the fly and execute it - this way you have full control of it, and you dont have to worry about the concatenation of the fields. you can use ezapi for this as a api. search net for it.

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Export data from multiple views to multiple CSV files

I would say it is really simple.
create table to held view name and status flag
something like
create table myViews as
(
veiwName varchar(200) not null,
status_cd char(1),
primary key (dndveiwName)
)

1. create SSIS package
2. Add package variable vieName (string) and variable isLast string with default 'N'
3. Add SQL connection
3. Add execute script task, connect task to SQL and put inside SQL script to clear myViews table and refill with status 'P'. After that "select top 1 @vieName = vieName from myViews where status_cd = 'P'"
and "select @isLast = case when count(*) = 1 then 'Y' else 'N' end from myViews where status_cd = 'P'"
and last one "select isLast = @isLast , vieName = @vieName"
4. set property on script task ResultSet to Single Row and on result set map variables to fields from last select statement.
5. Add for loop container set on container Fro Loop evalExpression to @[User::gIsLast] == "N"
6. Inside container you need to run export to CVS from view in @vieName (dataflow task). And after extraction done Execute SQL script task (change status on current view in myViews, and "select top 1 @vieName = vieName from myViews where status_cd = 'P'" and "select @isLast = case when count(*) = 1 then 'Y' else 'N' end from myViews where status_cd = 'P'")
on that task you will need to pass in parameter @vieName and get back single row as in first script
I hope I gave you enough details...




RE: Export data from multiple views to multiple CSV files

(OP)
Thanks gk53,
I managed to get it done.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close