Exporting FILESTREAM (varbinary (max)) data through SSIS package from source database to FLAT FILE destination and then loading from FLAT FILE to destination database, was a time consuming task for ME, at least. Let’s check out how I had resolved it.
(Note: Reader must have initial information of package creation using Sql Server Integration Services)
Source/destination table is “image”, with only two columns, image_id as uniqueidentifier and image_file VARBINARY(MAX) FILESTREAM.
To extract data, I have OLE DB source, Data Conversion and Flat File Destination. Image_OLE is OLE DB source with simple select query. “SELECT image_id, image_file FROM dbo.image”. Here we need to convert image_file column as follow:
For destination select a Flat File Destination and make following changes on “General” and “Advance” tabs
Advance Tab:
Datatype of image_file must be image[DT_IMAGE]. Here you are finished with Data Extraction. Execute this package and data will be exported to a text file at your desired location.
Let’s create one more package which will extract data from FLAT FILE source to an OLE DB destination, as follow:
Lookup is used to avoid duplicate row insertion. You can skip it. “Images” is FLAT FILE SOURCE with following necessary changes.
And on “Advance” tab
Datatype should be “Unicode text stream [DT_NTEXT]”. Use data conversion, to convert image_file data type to DT_IMAGE.
In the end provide OLE DB destination. Save your package and Execute to shift VARBINARY(MAX) data from text file to your desired database table.
Hi,
ReplyDeleteI wants to export the data from SQL Server to txt files as
1. Three tables (master, detail 1, detail2)
detail 1 contains the FK of master table
detail 2 contains the FK of detail 1 table
i wants to export the data of the three tables to 3 separate text files.
How would i do that ?
When exporting data to text files, if data is being exported separately for each table. Then no need to worry about relations. Only keep in mind that, when you will import data from these separate files to SQL Server, dependencies must be care about. Like in your case, when importing data, import for detail2 first, then detail1 and then into master table. If you need a package to as example, do mail me.
ReplyDeleteSQL Server Destination
ReplyDeleteSQL Server Destination We have looked at two different ways to import data into SQL Server—using the Bulk Insert Task and the OLE DB destination earlier in this chapter. Though both are capable of importing data into SQL Server, they [...]
for more please visit us
http://server2008.org/?p=886
Please specify the steps for Extracting the data from SQL Server from than one table that has parent child relationship with each other.
ReplyDeleteI have to transfer FILSTREAM enabled Column from Source to Target(both are SQL Server). The above example will work for extract data from one table (with filestream enabled column) and load into another table.?
ReplyDelete