SSIS Package to Import Image Type Data with Fast to a Remote Database Server

Question

SSIS Package to Import Image Type Data with Fast to a Remote Database Server

dumin.net

Old Hand

Points: 327
More actions
April 3, 2010 at 9:18 am

#143903

I have created a SSIS package to import Image Type Data around 3 million records to remote server. it was quiet slow and i wanna what are the best practices for import image type data to remote server using SSIS.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 1

My first question is whether storing image data in the database is the right path. The other is how you are doing the import. Where is the data coming from.

CEWII

dumin.net Old Hand Points: 327 More actions · Answer 2

Images are saved in the database. i wanna import those data (around 3 million records) to remote database. i going to import those data via SSIS. what are the best practices for it and what are the other methods to import it.

Regards

Dumin

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 3

Are both sides SQL Server? Is the destination configured as fast-load? Also, on the source are you chossing the tablename from the list or are you using a SQL Statement?

Also, with 3M rows, how big is that on disk now? What do you consider slow? fast?

CEWII

dumin.net Old Hand Points: 327 More actions · Answer 4

Hi

Im using fastload to load data in destination.i have optimize the ssis task up to around 50% increasing the networksize of the connection Manager. But i wanna do it quickly. is there any ways to do this task .

Regards

Dumin

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 5

How long does it take to run? What would you consider fast? How much data? The statement 3M rows doesn't tell me much..

CEWII

John Rowan SSC Guru Points: 56440 More actions · Answer 6

I'm with Elliot here, we need more info on what you've already done. In addition to what Elliot has asked for, can you give us an explanation of how your data flow is set up? Meaning, a description of the tasks inside of your data flow?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

dumin.net Old Hand Points: 327 More actions · Answer 7

Hi Elliot

I have created a SSIS Package using C# because i wanna create ssis package dynamically from my application. it will create the same as SSIS package with data flow task . in the dataflowtask i have used from the oledbsource im using sqlcommand to get the data . i will save the data in the oldedb destination using Openrowset fastload .In the LAN it works very fast. Actually this table is contain image type data .i have increase network packet size of the connection in connectionManagers in SSIS Package.it increase my performance up t0 50%.But Even though it increase the performance it is not satisfied my requirement. there are around 3 million records which contain image type data.size of the table is around 60 GB what is the best way to transfer this data to remote database via WAN. using a backup or restore it or ?

John Rowan SSC Guru Points: 56440 More actions · Answer 8

It sounds to me like the problem is not the SSIS packge, but transferring data over the WAN. You mentioned that the packge was very fast when used over the LAN. This tells me that the pacakge itself is not the problem. You can, as you are aware, play with the packet size setting in your connection string.

You may consider alternate routes to getting your data to your SSIS server. Maybe even extract your data on your remote server with a local job that extracts the image data to file. Move the file accross the WAN to somewhere where your SSIS server can access it and run the package using the file as the data source.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 9

I am unsure why you want to create this package dynamically, but that doesn't really change anything in my mind.

So, 3M records and about 60GB of data. Over LAN works great, over WAN not so hot.

How often are you needing to do this copy? Do you need to send every record every time? Does this data change a lot?

CEWII

dumin.net Old Hand Points: 327 More actions · Answer 10

Hi All

Thanks for the reply. this is a one time job. Eventhough in the future frequently have to update these amount of data in the remote server . is there any other options ?

Thanks again

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 11

I tend to view one-time differently. A lot depends on the type of image data. Is it actually graphics, if so what file format? If it is other files, what kind? The reason I ask is that some file types won't compress very well, like JPG and GIF images. If the data contains a lot of those then what I'm about to talk about isn't a consideration.

You could write the data out to a file, compress the file, and move the file, decompress it and load it.

Sometimes you just have to "pay" for the initial load.

As far as the later reloads. Do you really need to reload the data on the remote server or do you simply need to get some of those 3M rows updated on the remote end.

The best way, in my mind, is to keep a record of when a record in the source system changes. I usually handle this with Last Change Date that often gets updated by a trigger. I then keep a single record on the destination side of the maximum date/time (from the data) that was applied, i use this to generate delta data from the source.

I have also built systems that sometimes verify the remote system against the source. The method depends on whether I am trying to check whether the record is there or whether the record is the same on both sides. For the record being the same I usually pull back for the remote system the primary key of the record and a checksum of the record, that checksum does not include the last change date. I then compare that to the checksum stored locally, if they are different I use that push the record down to the remote. These tend to be paranoia checks because the processes tend be be robust enough not to miss updates.

Is this clear?

CEWII

dumin.net Old Hand Points: 327 More actions · Answer 12

Thanks Elliot for yor reply. it's clear to me. for the next times only the new and updated records will be upload to remote server.The issue is with the first time job. seems like it takes huge time . i have already saved the images with compression.

is there any way to do it differently ?

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 13

dumin.net (4/8/2010)
Thanks Elliot for yor reply. it's clear to me. for the next times only the new and updated records will be upload to remote server.The issue is with the first time job. seems like it takes huge time . i have already saved the images with compression.
is there any way to do it differently ?

Short of dumping the data out to a removable disk and hand carrying it there, I don't see any options. It may well take a long time, some providers will let you boost the capacity of WAN links for a short period. But I would almost guess that sneaker-netting it would be substantially cheaper..

CEWII

fabulous617 Old Hand Points: 329 More actions · Answer 14

Hello all,

I was reading the previous posts and have a similar issue. I am new to SSIS and SQL Server in general.

I created a basic package where my Control Form consists of an "Execute SQL Task" and "Data Flow Task". My SQL task consist of

CREATE TABLE "PLUGSSIS" (

"JOB_NAME" VARCHAR2(50),

"JOB_ID" VARCHAR2(22),

"ACCT_NO" VARCHAR2(50)

)

My Data Flow consists of Source Connection (Excel Spreadsheet), Data Conversion, and OLE DB Destination.

Simply I am just pushing data over a WAN from an excel spreadsheet into an oracle server. I was successful at doing this but it took about 30 minutes to upload 2000 rows to the server. Any ideas as to how I can increase the performance?

FYI - I am currently using SQL Server 2008 R2, SQL Management Studio and Visual Studio 2008

Any information is greatly appreciated.

Thanks.

Fabe

Fab