1

I have a script file in a folder.I want to use TSQL to run this script without opening it in SSMS as I will be using SSIS to run this script to generate CSV.

I have used OSQL to achieve this by using Execute Process Task but it is not giving me exact results I am looking for. It is generating spaces in the csv. A 17 mb file which I exported by rightclick as CSV option turned out to 1gb when I executed using OSQL code.The OSQL I used is:

-E -S CCCMSDSQL20,1819 -d SHARE_SCRUB -q "SET NOCOUNT ON" -i "C:\Scripts\.SQL" -o "C:\Scripts\.csv" -n -h-1 -s"," -w 700

I prefer a TSQL script which can achieve the same,so that I can use it in Execute SQL task and get the results.

EDIT: I am working to generate multiple csv's from multiple .sql files in a folder .I am using a for each loop container and execute process task as of now.I want to change the execute process task to execute sql task and use this script to generate multiple files.

If it is possible to write a TSQL code which can do the looping and generation of CSV files in one script I can use it without using the package.

Thanks for your time and help.

user1757018
  • 35
  • 2
  • 7
  • 1
    It might be helpful if you can explain why you need to use a .sql script rather than an SSIS data flow to export the data? – Pondlife Oct 25 '12 at 16:52

1 Answers1

1

Your parameters for osql are what I would expect, and I just made a small demo to do something similar and it works without problems.

I would examine your actual sql script and its results inside SSMS to make certain it isn't returning more than you want.

Also, it may not be what you want since you intend to also use this with the Execute SQL task, but I'll point out that bcp is specifically designed for copying data in and out of SQL and that you can use pure T-SQL with the opendatasource to make a CSV file without use of -o to dump the results of OSQL to a file.

TimothyAWiseman
  • 12,609
  • 11
  • 32
  • 47
  • @Pondlife : Thanks for the reply and edit.The SQL script I was asking is to use it in a Execute SQL task which in turn is used in a For each loop container ,to execute multiple scripts and generate CSVs. Right now I am using Execute Process task and OSQL which are giving the errors as I described above.So a TSQl script would help me achieve this – user1757018 Oct 25 '12 at 17:11
  • Thanks for the reply .Can you explaine me how to use opendatasource.I am looking specifically for TSQL so that I can completely take OSQL out of my code. – user1757018 Oct 25 '12 at 17:14
  • @user1757018 Take a look at http://www.sqlservercentral.com/articles/OpenDataSource/61552/ That one is mostly about reading from it, but it is the same basic concepts and has links to other resources. – TimothyAWiseman Oct 25 '12 at 20:10