Sqlcmd returnns space within my export to csv datatypes

SSIS and Import/Export
1

i wrote the below code that connect to sql server and export table results to .csv file. when using SSIS to export to a table the datatypes have too many spaces

sqlcmd S DBINSTANCE - E -i c:\test.sql -o c:files\test.csv -s "," -W -d DATABASENAME

the headers are coming outas
[name ] [varchar] (50),
[address ] [varchar] (200)

etc .................

it should be

[name] [varchar] (50),
[address] [varchar] ((200)

why is my sqlcmd file creating all these spaces with my column datatypes PLEASE ADVISE

2

I can't see a difference between "outas" and "should be" (except for an extra left parenthesis in the latter). What's in test.sql? Can you post that?

3

Probably because the column names were created with a space at the end.

4

image

5

the snip shows how the files are coming out based on the code
sqlcmd S DBINSTANCE - E -i c:\test.sql -o c:files\test.csv -s "," -W -d DATABASENAME

image

6

no thats not it sir thx

7

test sql is select name, address from dbo.emp

8

Just to make sure, try running this in SSMS on your table:

use yourdatabase;
select '¤'+[name]+'¤' as column_name
      ,len([name]) as column_name_length
  from sys.columns
 where [object_id]=object_id('dbo.emp')
;

This shows two columns:

  • the column names surrounded by ¤
  • the length og the column names
9

that didnt work i decided to use bcp (bulk copy) instead

thx