Quick one today:

Recently, we were tasked with an interesting task. One of our applications stores digital shopping receipts in a database table in binary format. A different team wanted an export of all the millions of such digital receipts in separate files for their ETL engine. Initially, T-SQL was used to export all the data and it worked well for a few thousand to a million rows. But soon we realized that we need a more scalable solution. After exporting the data into RAW files using T-SQL, we then needed to move these files from the database server to another server. From that server we need to download then over VPN to different team’s developer laptop. Windows explorer is okay with a few thousand files; But once we get to copying/moving millions of files, it creeps to a halt. Gets painfully slow. So we needed another way that transfers the files directly to the developer’s laptop.

Drum roll !! Enter SSIS !!

In Sql Server Data Tools, open a new Integration Services Project.

Step 1: Create Data Flow Task

Step 1: SSIS Export VARBINARY to RAW File

Step 2: Create Ole DB Source with necessary SQL query to retrieve data from the column.

Step 2: SSIS Export VARBINARY to RAW File

Step 3: Create Export column from the “Other transformations”

Step 3 : SSIS Export VARBINARY to RAW File

Step 4: Make some property changes as needed.

Step 4 : SSIS Export VARBINARY to RAW File

Step 5: Run it

Voila !! All files are now brought from the database server, over VPN, to the developers local laptop (once authentication & authorization is properly put in place)

Hope this helps,
_Sqltimes