SQL - Export data from multiple views to multiple CSV files

Coloplast
Coloplast used Ask the Experts™
on
Hi Guys,
I need help to export data from multpile views to CSV files one to one.
Either by T-SQL or a SSIS package.
I have tried using T-SQL like this:

SET NOCOUNT ON;
DECLARE @Views as TABLE (Object_id int, name nvarchar(200));

INSERT INTO @Views (Object_ID, name)
SELECT      Object_ID, name 
FROM        sys.views
WHERE name LIKE 'VW_XFormData_%'

DECLARE @viewName   nvarchar(200) = (select top 1 name from @Views);
DECLARE @sql        nvarchar(max) = '';

WHILE(Exists(select 1 from @Views)) BEGIN
	SET @sql = 'EXEC xp_cmdshell ''SQLCMD -S . -d EPIServerColoplastCMS -Q "SELECT forms.*, vw.* FROM ' + @ViewName + ' vw INNER JOIN VW_XForms forms ON vw.Meta_FormId = forms.ExternalId" -s "," -o "D:\CSV\' + @ViewName + '.csv"'';'
	EXEC(@sql); 
    --Print (@sql);

    DELETE FROM @Views where name = @viewName;
    SET @ViewName = (select top 1 name from @Views);
END;

Open in new window


Above works but the CSV files look like crap (a lot of spaces and line shifts), so it's not usable.

If have better ideas how to export data to CSV files either by using T-SQL or articles describing how to create a SSIS package doing the same wouold be great.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vikas GargAssociate Principal Engineer
Top Expert 2014

Commented:
Hello,

Creating SSIS package sounds easy and good compare to T-SQL

Here I am sharing one LINK
explaining step by step creating SSIS package to load data from SQL to CSV file.

Hope this would help you..
ColoplastIT engineer

Author

Commented:
Thanks Vikas for your reply.
But I need a solution that takes multiple views and exports data to CSV files one to one which will involve parameters and for each loop in SSIS.
Your solution only takes from one table/view to one file.
Hope you still can help me...thanks.
Associate Principal Engineer
Top Expert 2014
Commented:
Hi,

For that you have to use Fore each loop in SSIS.

select query to get all view names
Store them to object variable
Then in DFT use expression as sql statement and use this variable as source view name.

An example of using fore each loop is shown HERE.

Hope this will guide you
ColoplastIT engineer

Author

Commented:
I have already seen that article and tried to work it out from that wihtout any luck.
But thanks for trying.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial