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
Read Full Post »