SQL - Export data from multiple views to multiple CSV files

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
ColoplastAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargAssociate Principal EngineerCommented:
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..
ColoplastAuthor 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.
Vikas GargAssociate Principal EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ColoplastAuthor Commented:
I have already seen that article and tried to work it out from that wihtout any luck.
But thanks for trying.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.