OLEDB performance and Script task

Hi enquiring whether is it more efficient to use OLEDB destination to update and extract all records from table A to tabl e B or use script task? And whether OleDB destination perform row by row update? Finally will data integrity be affected if i run delete, update and insert operations in the same data flow but extracting different sets of data each time from the 2 tables?

Unfortunately the answers to your first question is, it depends... Try testing them out for yourself as the speeds will vary by system and by the transforms that you are trying to push the data through.

OleDB destination will NOT preform a row by row UPDATE. The destination transformation is for inserting new rows, to do the update you will use the OLE DB Command Transformation.

Your data integrity should be fine for doing delete update and insert operations in the same data flow. Of course, this will vary depending on what transformations you do, when, how you get your data, etc... Try watching the Kimball video which is linked to on the front page for a good overview of the various ways of handling update / insert. Also, look at the thread for "check to see if a row exists, if so update, else insert" (also stickied on the front page).

|||

Thanks for the quick reply, but using OLE DB Command from what i understand is too expensive, so comparing OLE DB Command and Script task, script task would be a better choice in this aspect, can i say that at first glance?

|||If you have a large number of updates, you should write those updates to a temporary table and then once the data flow is done, issue an Execute SQL task to perform a batch update. That's the *best* option.|||

Hi,

Oh thanks for the advice, but just to verify this 'batch update', is it using a sql statement something like this

"update tablea set tablea.rows = tableb.rows from tablea, tableb (with the critriea)" and the temporary table contains only the records that need to be updated.

|||

garynkill23 wrote:

Hi,

Oh thanks for the advice, but just to verify this 'batch update', is it using a sql statement something like this

"update tablea set tablea.rows = tableb.rows from tablea, tableb (with the critriea)" and the temporary table contains only the records that need to be updated.

Yes

OLE Object extraction

Hi,

I have virtually no ms access skills as am an Oracle dba.

I need to extract OLE Object data which are images from an Access 2000 mdb file for an import I am writing, can anyone point me in the direction of either some software that will do this or any scripts I can write to do this.

When I open the table the field reads "Long Binary Data"

Many thanks

RobertHi,

I have virtually no ms access skills as am an Oracle dba.

I need to extract OLE Object data which are images from an Access 2000 mdb file for an import I am writing, can anyone point me in the direction of either some software that will do this or any scripts I can write to do this.

When I open the table the field reads "Long Binary Data"

Many thanks

Robert
Import to what ? Oracle or Sql server 2000 ?|||sorry, I just want to export the files into the file system|||sorry, I just want to export the files into the file system

We generally do it using BLOB..
Check this link
Link (http://www.vbwm.com/forums/topic.asp?TOPIC_ID=4073)
Hope it will help you.

OLE DB Source Editor

hi,

I am using SSIS to extract data from sql server and import into MDB file. In
the process, under data flow task, I have used OLE DB Source Editor as source. Here
i have choosen SQL Command as mode of data population. In the box below i
have typed the following statements.

"Exec Site_Address"

I have used many temperory tables in this procedure.
When i run this procedure in the query analyzer window i get the desired data which has to be imported to an MDB. After typing the above statements and when i
click the button preview i can see the data. But when i click the
Columns.... i dont see anything there. I am unable to see any columns there.
This is getting to my nerves because, when i use OLE DB as Destination i am
unable to map the columns and i get an error.

I dont know how to solve this problem. cannot we map columns in temp tables .... or wat is it ?

Please help me to find a solution.

I will also paste the procedure code that i have used.

Create procedure Site_Address

as

begin

create table #Data_For_Site_Address_Table

(

unitid varchar(20),

city varchar(50),

cust_num varchar(40),

zip varchar(20),

CountryID varchar(20),

CreatedBy varchar(20)

)

-- tblcrdsiteaddress

insert into #Data_For_Site_Address_Table

select distinct * from

(select

(select top 1 fsu.ser_num

from fs_unit fsu

where ca.cust_seq <> 0 and fsu.cust_num = ca.cust_num

order by ca.city desc) as UnitID,ca.city,ca.cust_num,ca.zip,

CASE

WHEN ca.country like 'Luxembourg' THEN 'LU'

WHEN ca.country like 'Deutschland' THEN 'DE'

WHEN ca.country like 'Austria' THEN 'AT'

WHEN ca.country like 'Czech Republic' THEN 'CZ'

WHEN ca.country like 'Denmark' THEN 'DK'

WHEN ca.country like 'CHINA' THEN 'CN'

WHEN ca.country like 'CROATIA' THEN 'HR'

WHEN ca.country like 'Egypt' THEN 'EG'

WHEN ca.country like 'Germany' THEN 'DE'

WHEN ca.country like 'Hungary' THEN 'HU'

WHEN ca.country like 'Jordan' THEN 'JO'

WHEN ca.country like 'Korea, Republic Of' THEN 'KR'

WHEN ca.country like 'Poland' THEN 'PL'

WHEN ca.country like 'Switzerland' THEN 'CH'

WHEN ca.country like 'United Kingdom' THEN 'GB'

ELSE '- N/A -' END AS CountryID, CA.CreatedBy

from custaddr ca

) al

where unitid is not null

Select TT.Unitid as Short_Site_Name, TT.City as Site_Name,'N.A' as Street_Po_Box,TT.Zip as Postal_Code_City, Null as State_Region,

TT.CountryID as CountryID,Null as Zone, Null as Note, TT.CreatedBy as UserID, GetDate() as Date, 'A' as [Action]

From #Data_For_Site_Address_Table TT

END

Thanks.

Rgds,
Meher Krishna.V

I just did a quick search in the forum and found a couple threads dealing with SP being used inside of an OLE DB Source:

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=ole+db+source+procedure&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

This, particularly, seems to talk about same issue:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=161617&SiteID=1

I hope it helps

|||

Hopefully this should explain the problem:

Using stored procedures inside an OLE DB Source component
(http://blogs.conchango.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx)

-Jamie

|||

I knew I have read about a solution somewhere; I just could not remember the exact place.

Good stuff Jamie!

|||

Rafael Salas wrote:

I knew I have read about a solution somewhere; I just could not remember the exact place.

Good stuff Jamie!

Well it wasn't at the above link cos I only wrote it about an hour ago

-Jamie

|||So, probably I dreamed about it. |||

thanks Jaime... that was great.

The problem is solved when i used a function......

But there should be a way how to use a sproc right ? In another posts that you had written, saying SET FMTONLY can solve the issue. i tried using it, but it was futile.

Any clue how to use it ?

|||

meher666 wrote:

thanks Jaime... that was great.

The problem is solved when i used a function......

But there should be a way how to use a sproc right ? In another posts that you had written, saying SET FMTONLY can solve the issue. i tried using it, but it was futile.

Any clue how to use it ?

No, in some circumstances I just don't think it can be done. Where did I suggest SET FMTONLY?

-Jamie

|||

okieee.... i am sorry again.... i thought it was you suggested using that statement. It was somebody else.

Thanks for your help.