SQLTeam.com | Weblogs | Forums

Csv load exact format using ssis

i have one doube in ssis how to load double codes data from sql server to csv filesusing ssis pacakge . source : emp table

CREATE TABLE [dbo].[empcsvfile](
[eid] varchar NULL,
[empstring] varchar NULL,
[details] varchar NULL
)
GO
INSERT [dbo].[empcsvfile] ([eid], [empstring], [details]) VALUES (N'1', N'''1'',''yt'',''1te'',''1d''', N'{"test":"ed","eDate":"1990-01-16","isflag":null,"Type":"te","Number":null,"std":null,"mark":null,"Code":"22","cty":"r","state":"tn","country":"IN","isPermanent":null,"Lines":{"Street2":"street2","Street1":"street2","Taluka":"street2","District":"street2"},"Proof":null},{"Type":"PHONE","Date":"2017-05-28","Verified":null,"Type":"BSNSS","Code":null,"Code":null,"Number":"954457565","ette')

destination : Test.csv file eid |empstring |details

I tried like below but in csv files its create extra columns emp table have 3 coumns when i tried to load data into csv that fileds its create more than 3 fileds.
a


can you please me how to load empcsvfiles(sql server ) 3 fileds data load into destination csv exact 3 fields in csv

Your sample DDL and DML do not work on my ssms. Can you please verify and post back a fix

CREATE TABLE [dbo].[empcsvfile](
[eid] varchar NULL,
[empstring] varchar(8000) NULL,

[details] varchar(8000) NULL
)
GO
INSERT [dbo].[empcsvfile] ([eid], [empstring], [details]) VALUES (N'1', N'''1'',''yt'',''1te'',''1d''', N'{"test":"ed","eDate":"1990-01-16","isflag":null,"Type":"te","Number":null,"std":null,"mark":null,"Code":"22","cty":"r","state":"tn","country":"IN","isPermanent":null,"Lines":{"Street2":"street2","Street1":"street2","Taluka":"street2","District":"street2"},"Proof":null},{"Type":"PHONE","Date":"2017-05-28","Verified":null,"Type":"BSNSS","Code":null,"Code":null,"Number":"954457565","ette')

Which 3 fields do you want from this blob of data (which looks like json data) Because right now you do have more than 3 columns if you are using comma as separator. On your ssis it shows you attempting to delimit the data using double quotes "
Also are you using a csv file as your file connection or a sql table?

The table definition does not include the size for the varchar columns - it needs to include that to be valid. It also appears that your data is quoted in the table?

If the data is already quoted - then on output into a file you do not need to include the quoted identifiers. But you also cannot utilize a comma as the separator...change it to a pipe or some other delimiter and export the data directly from the table.