Sql Server : Convert VARBINARY column data into raw file

Quick one today:

Recently, there was an interesting request. We have an application that stores shopping receipts in database table in binary format. To run some ELT processes on the content, we needed gather all the receipts; To be transferred ETL team. Seems like a simple task, but there was no ready template to generate all this data. We have about 10 million receipts will new 2 million receipts each day.

There are many ways to do this, but here we cover two ways:

• Using SSIS : Convert VARBINARY column data into raw files using SSIS
• Using T-SQL : Convert VARBINARY column data into raw files using TSQL

Hope this helps,
_Sqltimes

 

Read Full Post »

Sql Server : Export VARBINARY column data into raw file on disk using SSIS

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 »

Sql Server: Convert VARBINARY column data to raw files using T-SQL

Quick one today:

A few months ago, there was a new requirement to export millions of records in a table column to raw files on a disk. This column contains millions of shopping receipts stored in binary format in database tables. Early on, I stumbled up on a tip that gave me a good idea of how to approach this problem.

This uses T-SQL with help from ‘ OLE Automation Procedures’ to accomplish this task. Towards that, we need to enable ‘Ole Automation Procedures’ advanced options.

--
-- Enable 'Ole Automation Procedures'
--
SELECT * FROM sys.configurations
WHERE name = 'Ole Automation Procedures'
GO

EXEC sp_configure 'SHOW ADVANCED OPTIONS', 1
RECONFIGURE
GO

EXEC sp_configure 'Ole Automation Procedures', 1
RECONFIGURE
GO

EXEC sp_configure 'SHOW ADVANCED OPTIONS', 0
RECONFIGURE
GO

SELECT * FROM sys.configurations
WHERE name = 'Ole Automation Procedures'
GO

 

asdas

--
-- Code to export VARBINARY into RAW files using TSQL
--
DECLARE @SQLIMG VARCHAR(MAX)
 , @IMG_PATH VARBINARY(MAX)
 , @TIMESTAMP VARCHAR(MAX)
 , @ObjectToken INT

DECLARE IMGPATH CURSOR FAST_FORWARD FOR
 SELECT logcontent
 FROM dbo.TransactionLog
 WHERE EnterpriseID = '423-DZFDSF-23432-254E57A1-45A7-4DBF'

OPEN IMGPATH
FETCH NEXT FROM IMGPATH INTO @IMG_PATH

WHILE @@FETCH_STATUS = 0
BEGIN
 --
 -- Create unique file name for each row of data
 --
 SET @TIMESTAMP = 'M:\MSSQL\Hyvee\Receipts_' + replace(replace(replace(replace(convert(varchar,getdate(),121),'-','_'),':','_'),'.','_'),' ','_') + '.ZIP'

 -- PRINT @TIMESTAMP
 -- PRINT @SQLIMG

 --
 -- Use Ole Automation to save the contents to a file on disk
 --
 EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
 EXEC sp_OASetProperty @ObjectToken, 'Type' , 1
 EXEC sp_OAMethod @ObjectToken, 'Open'
 EXEC sp_OAMethod @ObjectToken, 'Write' , NULL , @IMG_PATH
 EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL , @TIMESTAMP , 2
 EXEC sp_OAMethod @ObjectToken, 'Close'
 EXEC sp_OADestroy @ObjectToken

 FETCH NEXT FROM IMGPATH INTO @IMG_PATH
END

CLOSE IMGPATH
DEALLOCATE IMGPATH
GO

 

OleAutomation Procedures RAW Files

At the end of it, reset the ‘Ole Automation Procedures’ settings.

<pre>--
-- Enable 'Ole Automation Procedures'
--
SELECT * FROM sys.configurations
WHERE name = 'Ole Automation Procedures'
GO

EXEC sp_configure 'SHOW ADVANCED OPTIONS', 1
RECONFIGURE
GO

EXEC sp_configure 'Ole Automation Procedures', 0
RECONFIGURE
GO

EXEC sp_configure 'SHOW ADVANCED OPTIONS', 0
RECONFIGURE
GO

SELECT * FROM sys.configurations
WHERE name = 'Ole Automation Procedures'
GO

Hope this helps,
_Sqltimes

 

Read Full Post »

Sql Server : How to TRUNCATE table variable

Quick one today:

Table variables are ubiquitous. There are several benefits to using them in your T-SQL code. There are some nuances between table variable and temporary table that we discussed previously; Today, we’ll look at dropping table variable.

How do we DROP a table variable? A table variable is not fully a table, so we cannot use DROP statement or TRUNCATE statement.

--
--   Throw error
--
DECLARE @Table_Variable TABLE (ID2 INT)
INSERT INTO @Table_Variable (ID2) VALUES(1), (2)
SELECT * FROM @Table_Variable

TRUNCATE TABLE @Table_Variable  -- This code throws error
GO

--
-- Error message
--
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '@Table_Variable'.

We get the same error message when we use TRUNCATE TABLE or DROP TABLE with table variable.

Solution:

--
--  Correct way to empty table variable
--
DECLARE @Table_Variable TABLE (ID2 INT)
INSERT INTO @Table_Variable (ID2) VALUES(1), (2)
SELECT * FROM @Table_Variable

DELETE @Table_Variable
GO

After DELETE, only the contents are removed; The variable and its structure are still available for INSERTing again, within the scope.

Hope this helps,
_Sqltimes

Read Full Post »