Load big one line flat json file in ssis

Question

I am trying to load a big file which basically is a json format flat file from my local drive to SQL Server by using SSIS. It's a one line file and I don't need to specify columns and rows as I am going to parse it as soon as it's in SQL Server by OPENJSON.

but when I tried to create Flat File Source in Visual Studio SSIS, I was not able to do that as even I used 'fixed width' format according to the solution here: import large flat file with very long string as SSIS package, as the max width seems to be 32000, while the json file could be much bigger.

here are my settings:

There are other options of loading the data by t-sql like OPENROWSET but we have SQL Server instance installed on another server rather than the same one we are doing our dev work. So there are some security limits between them.

So just wondering if this is the limitation of Flat File Source in SSIS or I didn't do it right?

Answer 1

You're likely looking for the Import Column transformation. https://learn.microsoft.com/en-us/sql/integration-services/data-flow/transformations/import-column-transformation?view=sql-server-ver15

Define a Data Flow as OLE Source -> Import Column -> OLE Destination.

OLE Source

Really, any source but this is the easiest to reproduce

SELECT 'C:\curl\output\source_data.txt' AS SourceFilePath;

That will add a column named SourceFilePath with a single row.

Import Column

Reference the article on Import Column Transformation but the summary is

1. Check the column that will provide the path
2. Add a column to the Import Column Collection to hold the file content. Change the data type to DT_TEXT/DT_NTEXT depending on your unicode-ness and note the LineageID value
3. Click back to Import Column Input and find the column name. Scroll down to the Custom Properties and use the LineageID above for FileDataColumnID where it says 0. Otherwise, you have an error of

The "Import Column.Outputs[Import Column Output].Columns[FileContent]" is not referenced by any input column. Each output column must be referenced by exactly one input column.

OLE DB Destination

Any data sink will do but the important thing will be to map our column from the previous step to a n/varchar(max) in the database.