SQL Server Import Export Wizard executing but not writing rows to file

Question

I am trying to export data from a database using the export wizard. My destination is a delimited flat file with a custom text qualifier(^`~) because the data in one of the fields I am exporting may contain basically every character known to man. So I am able to set up the query in the wizard just fine, and it executes with 0 errors, everything's good. But it shows that it has written no records to the destination file. Now, I know my query returns records, but it is somewhat of a multistep query. It goes something like this:

DECLARE @RecordCount AS INT SET @RecordCount = SELECT COUNT(*) FROM SomeTable WHERE COLUMN1 = 1 AND COLUMN2 = 2

DECLARE @TempTable AS TABLE( COLUMN1 INT, COLUMN2 INT, COLUMN3 VARCHAR(255)) INSERT INTO @TempTable SELECT Column1, Column2, Column3, Column4 FROM SomeOtherTable WHERE Column1 IN (--Sub Query that gets a random list of ids)

SELECT TOP(@RecordCount) A.COLUMN1, A.COLUMN2, A.COLUMN3, A.COLUMN4, B.COLUMN1, B.COLUMN2, B.COLUMN3 FROM @TempTable A INNER JOIN JoinTable B ON A.COLUMN1 = B.COLUMN1 ORDER BY COLUMN1

Like I said, the query works just fine when run in Ssms, it's only when using the export wizard that I have the issue of it not actually writing any rows to the destination. I would simply save the query results as a csv, but the comma delimiter is not possible for me because of the data in the fields, which is why I need the export wizard to declare a custom text qualifier. Can anyone point me in the right direction? Thanks :)

Answer 1

Hi,

Try to use BCP. Let me know the results.

bcp "YOUR QUERY" queryout test3.csv /Usa /P12345 /Sip\instance -c -t"^`~"

Best Regards,

Yigit.

---

http://www.yigitaktan.com

Answer 2

check these link

http://www.techrepublic.com/blog/datacenter/how-do-i-use-bcp-in-sql-server/319

http://programming4.us/database/1308.aspx

Answer 3

Yigit has almost answered your question. BCP is best utility considered for Data transfer. But you have to keep an eye on your Tempdb Database files that gets increase depending upon the batches used.

bcp "QUERY Text" output.csv /U sa /P yoursapassword /S serverip\yourinstancename -c -t"^`~"

http://msdn.microsoft.com/en-us/library/ms162802.aspx

---

Please Vote & "Mark As Answer" if this post is helpful to you. Cheers Prakash Nandwana Bangalore , India

Answer 4

Hi Wessless,

Regarding to your description, seems you need to export data to flat file using export wizard, which does not work.  You can use the BCP utility to transfer data as Prakash mentioned.

Moreover there is an easiest way for exporting a record set through SSIS is to use a data flow and a flat file destination with the flat file connection manager  The destination can be configured in the connection manager to include a header row with the column names.

For more information, please refer to these articles as below:
1. http://msdn.microsoft.com/en-us/library/ms141668.aspx 
2. http://msdn.microsoft.com/en-us/library/ms140266.aspx.

If you have any problem related, please link to SSIS forum.

---

Regards,
Amber zhang
TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.