Export data from multiple views to multiple CSV files

[RasmusH]

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

 

[fredericofonseca]

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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[gk53]

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...

 

[RasmusH]

Thanks gk53,
I managed to get it done.