Search code examples
jsonsql-serverssisflatfilesource

Load big one line flat json file in ssis

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.

I was able to load data via Import Flat File Task Wizard directly from SSMS, 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:

General Tab

Columns Tab

Advanced Tab

Preview Tab

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?

Solution

  • 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 enter image description here
    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. enter image description here 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.