Export CSV to json with C# script component

under2811 · February 8, 2019, 2:17pm

I need to export csv file to jason using script component. can anyone guide me how to do it.
I haven't do this trasaction so less knowledge of C# component. I cannot use T-SQL (JSON AUTO) also to convert the file to jason.

I tried but getting below error..

T.I.A

my data in csv

yosiasz · February 8, 2019, 5:26pm

why not try it out via powershell?

import-csv "SampleInput.csv" | ConvertTo-Json -Compress | Add-Content -Path "output.json"

under2811 · February 8, 2019, 5:38pm

getting err
[Execute Process Task] Error: In Executing "PowerShell.exe" "import-csv "C:\New folder\convertcsv.csv" | ConvertTo-Json -Compress | Add-Content -Path "C:\New folder\output.json"" at "", The process exit code was "1" while the expected was "0".

yosiasz · February 8, 2019, 5:42pm

what are you running this from?

yosiasz · February 8, 2019, 5:55pm

or this

Get-Content -path "SampleInput.csv" | ConvertFrom-Csv -Delimiter ',' | ConvertTo-Json | Out-File "Booya.json"

under2811 · February 8, 2019, 6:31pm

I need to use SSIS service only ..no t-sql option for me..so i am executing this on execute process task

under2811 · February 8, 2019, 6:37pm

same err

yosiasz · February 8, 2019, 6:43pm

Can you show us exactly how you have added that command in your SSIS

Also check this out

http://www.sqlservercentral.com/articles/execute+process+task/129996/

under2811 · February 8, 2019, 7:01pm

command -
Get-Content -path "C:\New folder\convertcsv.csv" | ConvertFrom-Csv -Delimiter ',' | ConvertTo-Json | Out-File "C:\New folder\Booya.json"

yosiasz · February 8, 2019, 7:06pm

please open a PowerShell window as Admin
cd to C:\New folder
run
Get-Content -path "C:\New folder\convertcsv.csv" | ConvertFrom-Csv -Delimiter ',' | ConvertTo-Json | Out-File "C:\New folder\Booya.json"

what happens?

under2811 · February 8, 2019, 7:08pm

it succeeded when ran in PowerShell..but failed in SSIS Execute procedure task

yosiasz · February 8, 2019, 7:41pm

under2811 · February 8, 2019, 7:59pm

Thank you very much!! it worked!!!!

under2811 · February 8, 2019, 8:19pm

I have below json file data

first i need to convert it to csv and then need it again in json..
I tried online converter for json to csv it gives me below csv file

then I ran execute process task and got json file but its incorrect

not sure first jason to csv was wrong or execute process task went wrong..
plz help..

yosiasz · February 8, 2019, 8:23pm

Is this honework or real life? Why do you have to convert back and forth?

What isbyour final goal in this ssis

under2811 · February 8, 2019, 8:24pm

Actually I am trying to build package that convert csv to json. and for that I need test file that's reason I am manually converting json file to csv first.. and to validate also..

yosiasz · February 8, 2019, 9:21pm

how did you try to convert that file from json to csv? what tool did yo use? anyways the result you are looking at are actually correct. look at your csv no column names.

jeffw8713 · February 8, 2019, 9:25pm

I found this: SSIS: C# CODE TO CONVERT CSV FILE JSON FILE FORMAT

It shows how to convert a CSV file to JSON and is fairly simple. The Powershell code is also quite simple but I would call it differently. It is actually easier to call Powershell from a script component...you can do something like this:

using (PowerShell powerShell = PowerShell.Create())
{
    // Source functions.
    powerShell.AddScript(PsScript);
    powerShell.Invoke();

    // Call function contained in sourced script above.
    powerShell.AddCommand("My-Function");
    powerShell.AddParameter("ParamA", varA);
    powerShell.AddParameter("ParamB", varB);
    powerShell.AddParameter("ParamC", varC);
    powerShell.AddParameter("ParamD", varD);

    results = powerShell.Invoke();
}

When you add the script - it can be an actual file or the actual code with parameters defined...

under2811 · February 8, 2019, 9:48pm

may be.. thank you for helping me !! appreciated!!