Convert JSON file to CSV using powershell in SSIS

Question

We have a Web Service which is in JSON format(script) that needs to be converted to csv files and dropped to a fileshare location using powershell. All this needs to be captured on running a SSIS package. How can this be achieved?

# Set the filename to have the date suffix.
$fileName = "2758_RS-DM_AIRData" + (Get-Date -Format yyyyMMdd_hh) + ".csv"
$location = "C:\ExtractFilePowerShellWebService\Data"
New-Item -ItemType file -Name $fileName -Path $location 

# delete if file exist
$FileName2 = (Join-Path $location $fileName)
if (Test-Path $FileName2) {
  Remove-Item $FileName2

}

# Capture data from AIR Web Service

$webclient = new-object System.Net.WebClient
$webclient.Credentials = new-object System.Net.NetworkCredential("A", "B", "C")
$Json = $WebClient.Downloadstring("https://air.ciostage.accenture.com/ExternalServices/Application-service/Application(2758)")     
$jsonserial= New-Object -TypeName System.Web.Script.Serialization.JavaScriptSerializer 
$jsonserial.MaxJsonLength = [int]::MaxValue
$appCollection = @()
$appObj = $jsonserial.DeserializeObject($Json)
foreach($app in $appObj.value){

        $appObject = New-Object System.Object 
        $appObject | Add-Member -MemberType NoteProperty -Name "ApplicationId" -Value $app.ApplicationId 
        $appObject | Add-Member -MemberType NoteProperty -Name "ApplicationNm" -Value $app.ApplicationNm
        $appObject | Add-Member -MemberType NoteProperty -Name "Application Tier" -Value $app.ServiceLevelOfferingNm
        $appObject | Add-Member -MemberType NoteProperty -Name "Application Status" -Value $app.AppStatusNm
        #$appObject | Add-Member -MemberType NoteProperty -Name "Description" -Value $app.Description
        #$appObject | Add-Member -MemberType NoteProperty -Name "Acronym" -Value $app.Acronym
        #$appObject | Add-Member -MemberType NoteProperty -Name "Url" -Value $app.Url
        $appCollection += $appObject
}

#$appCollection | Export-Csv -Path "C:\ExtractFilePowerShellWebService\Data\2758_RS-DM_AIRData.csv" -notypeinformation

#extract data into CSV file.
$appCollection | Export-Csv  -Path (Join-Path $location $fileName) -notypeinformation

When I run the script, csv file is created in the location mentioned but it is empty

Why does it need to be in a SSIS package? Just call the powershell script directly — Nick.Mc
– Nick.Mc, Commented May 30, 2017 at 8:40
@Nick.McDermaid because an SSIS package does a lot more than just convert one format to another. It will load, transform, lookup, combine and bulk load data from a LOT of different sources and send it to a LOT of different targets. AND validate fields, sizes etc to ensure you won't truncate data by accident, or try to pass fewer columns than those expected, or send a number to a string field. Trying to do the same with a Powershell script can be .... fun — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 30, 2017 at 8:42
@Aiswarya what does the JSON file look like? Converting/loading flat objects should be easy. Converting nested objects would require transformations eg with Select to flatten them or extract only the necessary fields — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 30, 2017 at 8:45
@Aiswarya furthermore, SQL Server 2016 introduced JSON support and querying. Maybe you could load individual Json rows into a table, then query them — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 30, 2017 at 8:46
Just establishing whether SSIS is required or not. If all they are doing is converting JSON to CSV then it's probably overkill. — Nick.Mc
– Nick.Mc, Commented May 30, 2017 at 8:46

Panagiotis Kanavos · Accepted Answer · 2017-05-30 08:50:00Z

1

I'm assuming your data is already in a format that will suit being in a csv, as Json has a lot of elements which csv cannot display, i'm using ConvertTo-Json in this example to generate a Json file :

Get-Process | ConvertTo-Json -Depth 1 | Out-File .\file.json

note the -Depth 1 which means no nested arrays etc. will be carried over.

Then I can convert that to to a csv using ConvertFrom-Json and the following:

(Get-Content .\file.json | ConvertFrom-Json) | Select Name,Handles,VM,PM | Export-Csv .\file.csv

edited May 30, 2017 at 8:50

Panagiotis Kanavos

134k16 gold badges211 silver badges271 bronze badges

answered May 30, 2017 at 8:34

colsw

3,3661 gold badge18 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Panagiotis Kanavos Over a year ago

In other words, the OP should use Convert-From

colsw Over a year ago

@PanagiotisKanavos i don't think gc .. | convertfrom-json | export-csv works though, covnertfrom-json is weird when piped directly.

Panagiotis Kanavos Over a year ago

The answer will be a lot more useful if you explain that you can convert to/from Json already, which functions to use and add links to them.

Collectives™ on Stack Overflow

Convert JSON file to CSV using powershell in SSIS

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related