October 19, 2012 at 1:27 am
Dear All,
My requirement is to extract all *.doc, *.xls, *.jpeg files stored within the database to file system.
the database is vendor database and i have no knowlege how these documents are stored within the database.
Need reference to tool or script which will scan the database finds the documents and extracts it to file system
Please help me to meet my requirement. i will ge grateful to you.
regards
Aijaz
October 19, 2012 at 3:48 am
You can use the SSIS Export Column transformation:
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 19, 2012 at 1:39 pm
thanks for your reply. is there any tool/script to extract the documents.
regards
aijaz
October 19, 2012 at 1:45 pm
Aijaz Ahmed Mir (10/19/2012)
thanks for your reply. is there any tool/script to extract the documents.regards
aijaz
Yes, SSIS. This is the very first line of that article.
The Export Column transformation reads data in a data flow and inserts the data into a file. For example, if the data flow contains product information, such as a picture of each product, you could use the Export Column transformation to save the images to files.
You need to create an SSIS package with the column transformation in a data flow task.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 19, 2012 at 1:52 pm
Aijaz Ahmed Mir (10/19/2012)
thanks for your reply. is there any tool/script to extract the documents.regards
aijaz
this is a job for a programming language; here's an example in vb.net for the code for an entire table.
Powershell can do it too, but i don't have a code example for that.
Private Sub btnBlobsToDisk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBlobsToDisk.Click
'--Imports System.IO
'--Imports System.Data.SqlClient
'--this works whether the datatype for is IMAGE or VARBINARY(max)
Dim sqlcmd As String = "SELECT TOP 3 FileName,ImageData From BatchOfBlobs;"
Dim mySqlConnectionFormat As String = "data source={0};initial catalog={1};user id={2};password={3};Trusted_Connection=False;Connect Timeout=600;Workstation ID=GhostInTheMachine;Application Name=HaxxorPadPlusPlus;"
Dim MyConn As New SqlConnection
MyConn.ConnectionString = String.Format(mySqlConnectionFormat, "DEV223", "SandBox", "Noobie", "NotARealPassword")
MyConn.Open()
'now lets get a commadn object
Dim mySqlCommand As New SqlCommand
mySqlCommand.Connection = MyConn
mySqlCommand.CommandTimeout = 600
mySqlCommand.CommandType = CommandType.Text
mySqlCommand.CommandText = sqlcmd
Dim myDataReader As SqlDataReader
myDataReader = mySqlCommand.ExecuteReader
While myDataReader.Read
Try
Dim path As String = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)
Dim imageInBytes As Byte() = myDataReader("ImageData")
Dim memoryStream As System.IO.Stream = New System.IO.MemoryStream(imageInBytes, True)
Dim image As New System.IO.BinaryWriter(File.Open(path & "\" & myDataReader("FileName"), FileMode.Create))
'%APPDATA% variable like "C:\Users\Lowell\AppData\Roaming"
image.Write(imageInBytes)
image.Flush()
image.Close()
Catch ex As Exception
Console.WriteLine(ex.StackTrace)
End Try
End While
End Sub
if you can install a CLR, and the source table has the filename in it, then you could use this project:
http://nclsqlclrfile.codeplex.com/
the syntax for a single document is shown here...you would need a loop/cursor to go thru a table of items.
--MSPSaveFileImage
-- Parameters: @FilePath,@FileName,@FileBytes
-- purpose: given an varbinary image column in a table, write that image to disk
-- usage:
--assumes table and the file from the example above for dbo.MFGetFileImage exists already.
declare @myfile varbinary(max)
SELECT @myfile = rawimage FROM myImages WHERE id = 1
EXEC dbo.MSPSaveFileImage 'C:\Data','spinning.gif',@myfile
you can do it from xp_cmdshell as well.
I have this saved in my snippets from my SQL2000 scripts, and it assumes you can have/ get a copy of TextCopy.exe:
--**************************************
--
-- Name: Procedure to Export/Import Imag
-- es In/From SQL SERVER
-- Description:the procedure is used as
-- an Interface to the TextCopy Utility sup
-- plied with SQL SERVER in order to simpli
-- fy handling of BLOBS (images/docs)
-- By: Eli Leiba
--
-- Inputs:@runpath varchar(100), -- text
-- Copy Location
@srvrvarchar(50), -- server TO LOAD
@db varchar(50), -- DATABASE TO LOAD
@usrvarchar(50), -- login USER
@pwdvarchar(50), -- login password
@tblvarchar(50), -- TABLE TO load/unload
@colvarchar(50), -- COLUMN TO load/unload
@whrvarchar(200), -- WHERE clause
@filvarchar(100), -- filename including path
@modchar(1) -- I FOR LOAD INTO Sql
--
-- Assumes:-- usage (assuming TextCopy.e
-- xe is in c:\)
CREATE TABLE pic (pic_id int,picture image)
INSERT INTO pic VALUES (1,null)
UPDATE pic SET picture = 'xx'
-- Inserting image
EXEC sp_imp_exp_images 'c:\textCopy.exe',
'PCN1943',
'PUBS',
'sa',
'sa',
'pic',
'picture',
'"where pic_id = 1"',
'c:\pic.jpg',
'I'
-- Extracting Image
EXEC sp_imp_exp_images 'c:\textCopy.exe',
'PCN1943',
'PUBS',
'sa',
'sa',
'pic',
'picture',
'"where pic_id = 1"',
'D:\pic.jpg',
'O'
--
-- Side Effects:Import/Export of BLOBS
--
--This code is copyrighted and has-- limited warranties.Please see http://
-- http://www.1SQLStreet.com/xq/ASP/txtCodeId.498/
-- lngWId.5/qx/vb/scripts/ShowCode.htm--for details.--**************************************
--
CREATE PROCEDURE sp_imp_exp_images
(@runpath varchar(100), -- textCopy Location
@srvrvarchar(50), -- server TO LOAD
@db varchar(50), -- DATABASE TO LOAD
@usrvarchar(50), -- login USER
@pwdvarchar(50), -- login password
@tblvarchar(50), -- TABLE TO load/unload
@colvarchar(50), -- COLUMN TO load/unload
@whrvarchar(200), -- WHERE clause
@filvarchar(100), -- filename including path
@modchar(1)) -- I FOR LOAD INTO Sql , O FOR output FROM SQL
AS
DECLARE @cmd varchar(1000)
SET @cmd = @runpath + ' /S ' + @srvr + ' /D ' + @db + ' /U ' + @usr +
' /P ' + @pwd+ ' /T ' + @tbl + ' /C ' + @col + ' /W ' + @whr +
' /F ' + @fil+ ' /' + @mod
EXEC Master..xp_cmdShell @cmd
GO
Lowell
October 20, 2012 at 2:46 am
Thank all you guys there. selfless help much appreciate.
i will try these snippets and post the reply
regards
Aijaz
October 20, 2012 at 5:48 am
--
-- Side Effects:Import/Export of BLOBS
--
--This code is copyrighted and has-- limited warranties.Please see http://
-- http://www.1SQLStreet.com/xq/ASP/txtCodeId.498/
-- lngWId.5/qx/vb/scripts/ShowCode.htm--for details.--**************************************
--
CREATE PROCEDURE sp_imp_exp_images
(@runpath varchar(100), -- textCopy Location
@srvrvarchar(50), -- server TO LOAD
@db varchar(50), -- DATABASE TO LOAD
@usrvarchar(50), -- login USER
@pwdvarchar(50), -- login password
@tblvarchar(50), -- TABLE TO load/unload
@colvarchar(50), -- COLUMN TO load/unload
@whrvarchar(200), -- WHERE clause
@filvarchar(100), -- filename including path
@modchar(1)) -- I FOR LOAD INTO Sql , O FOR output FROM SQL
AS
DECLARE @cmd varchar(1000)
SET @cmd = @runpath + ' /S ' + @srvr + ' /D ' + @db + ' /U ' + @usr +
' /P ' + @pwd+ ' /T ' + @tbl + ' /C ' + @col + ' /W ' + @whr +
' /F ' + @fil+ ' /' + @mod
EXEC Master..xp_cmdShell @cmd
GO
[/quote-0]
Hi,
Do not forget to enable xp_cmdshell. It is not enabled by default.
USE master
GO
-- Allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
-- Enable the xp_cmdshell component.
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
-- Revert back the advance option
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE WITH OVERRIDE
GO
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply