How to load a document(text,csv,excel) or BLOB file into a varbinary(MAX) database column using SSIS

Question

I look for any resources that I can, but I only see examples of loading images into varbinary(max) columns in SQL Server. I want to convert the actual documents into varbinary format, and load them into a database column.

I will worry about converting them later. I know you are going to say "Why would you do that when you could just store the directory of the file and retrieve the file from the file location on the server?" It's because we are creating a framework for sending notifications, and the BLOB files that we are loading are going to be the documents - or attachments of any type. It's just the way my boss wants it done.

CREATE TABLE [dbo].[AEI_Notification_Artifacts]
(
    [artifact_id] [BIGINT] IDENTITY(1,1) NOT NULL,
    [notification_id] [BIGINT] NOT NULL,
    [artifact] [VARBINARY](MAX) NULL,

    CONSTRAINT [PK_AEI_Notification_Artifacts] 
        PRIMARY KEY CLUSTERED ([artifact_id] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I am trying to load files and convert them in SSIS via a flat file and import column task... But the resources that I found only work with images. I am thinking I am going to need to create a manual complex script that will convert the data then stream it.

Answer 1

We can handle this without needing to utilize a script task in SSIS.

Here is a small example. In your case you can replace the Source component with your FlatFile source. The FlatFile source should output a column that is the filepath (example: C:\support\yourfile.bin) to your file(s) that you wish to import.

Next, we will configure the Import Column component:

First let's select the column to use as the input, this should be your file path column from above.

Next we need to add the column that we are going to import. First go select Import Column Output/Output Columns and then press the Add Column button. Next set the DataType to [DT_IMAGE] (This will translate to varbinary(MAX) when writing to the destination). Finally make note of the LineageID.

Now back to the Import Column Input\Input Columns \ FilePath. Select that column and then under Customer Properties set FileDataColumnID to the LineageID that we noted before.

Now you should be all set to drop your data into the your destination of choice.

[Edit: typos]