SSIS to JSON - Iteration

Question

Post reply

SSIS to JSON - Iteration

darkangelBDF

Old Hand

Points: 349
More actions
January 24, 2020 at 7:42 am

#3717997
Hi there, everyone. I've been trying for two days now to figure out a way to iterate through a table and export to JSON. The scenario is:
I have a stored procedure that checks for data using a field called GLExtractId. I need to extract the data to a separate file for each GLExtractId. I.e. for each GLExtractId there needs to be a JSON file.
I have populated my staging table from which this will take place. I have a field in the table called IsProcessed that is populated with a 1 after each GLExtractId has been processed. Problem now is that when I run this as an Execute SQL Task in SSIS, it just exports one file with just one GLExtractId.
When I test the stored procedure in SSMS it works with output to the screen as separate JSON's.
My current setup in SSIS is:
1. Data flow task that extracts from an OLE DB Source using SQL command from variable as Data access mode. This calls the stored procedure that does the following:
- Creates a temp table variable that contains four fields. Three are unique to make sure I extract the data correctly. The last is another IsProcessed field.
- It then uses a WHILE EXISTS loop that selects the TOP(1) from the temp table variable where IsProcessed = 0.
- In the while loop it declares three variables (as per the temp table variable) and set these to select from the temp table variable using TOP (1).
- A select statement then builds the JSON structure using these three variables. I used FOR JSON AUTO as part of this statement.
- I update the temp table variable setting IsProcessed to 1 for the three three fields used.
2. The second part of the data flow task is a Flat File Destination task that uses a Flat File Connection manager I created.
Problem is when I run it like this it exports just the one file.
How can I change my process so that it runs this export for each GLExtractId?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16155 More actions · Answer 1

possibly use a execute sql task instead of a dataflow - resultset

pass this resultset to a component script and for each record retrieved create a new file and write the contents to it

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

Here's an article from SSC:

https://www.sqlservercentral.com/articles/8-ways-to-export-sql-results-to-a-text-file

If it were me I'd use BCP, the Bulk Copy Program that comes with SQL Server.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Sounds like you need to put your data flow task inside a Foreach container.

Your Foreach container needs to iterate around the various GLExtractId values.

To make that happen, use an ExecuteSQL task to populate an SSIS Object variable, and use that variable as your iterator.

Modify your stored proc (or create another version) to accept GLExtractId as a parameter.

For every loop iteration, the data flow executes and calls the stored proc with the 'current' GLExtractId. You'll have to use a variable within the loop to control the output file name too.

Hope that makes some sense. Haven't had any coffee yet 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

darkangelBDF Old Hand Points: 349 More actions · Answer 4

Hey everyone. Thanks for the suggestions. I think I know what to do now. Will let you know once I’ve done it on Monday...