Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Sunday, July 14, 2024 
 
slxdeveloper.com Community Forums  
   
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
 Data & Imports Forums - SSIS/DTS
Forum to discuss using SQL SSIS or DTS to perform SalesLogix imports. View the code of conduct for posting guidelines.
Forums RSS Feed


 Back to Forum List | Back to SSIS/DTS | New ThreadView:  Search:  
 Author  Thread: OLE Provider Concerns
Patrick Sullivan
Posts: 13
 
OLE Provider ConcernsYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 14 May 07 2:49 PM
fiogf49gjkf0d
Hi I am about to build a SSIS application to import a very large amount of data from our old CRM to SLX. Is there any concerns i should have on performance of the SLX OLEDB Provider?
[Reply][Quote]
Phil Parkin
Posts: 819
Top 10 forum poster: 819 posts
 
Re: OLE Provider ConcernsYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 14 May 07 6:35 PM
fiogf49gjkf0d
You are going to take a performance hit in performing the import, because you will need to use the provider to create new IDs for you - for every record in every table.

What this means is that you will not be able to perform traditional set-based INSERTs along the lines of

INSERT INTO [tablename(field1, field2, ...)]
SELECT field1, field2, ...
FROM ...

because every SLX record needs its own ID to be generated and this generation does not happen automatically via SQL Server IDENTITY fields.

So you will have to perform INSERTs one at a time via a loop - unfortunately losing a lot of SQL Server's query-optimisation power in the process. Your INSERT logic will need to use the generated IDs to maintain relationships between ACCOUNTS, CONTACTS etc - if you do not know SalesLogix, you should seek help here to understand how the tables are linked.

Bearing that in mind, performance via the provider is fine. Having said that, for big imports I would probably use it only to grab SLX IDs and use a SQL Native Client connection for everything else.

Remember that you can grab IDs in batches (eg 10,000 at a time) - this is much faster than doing it individually.

Good luck.
[Reply][Quote]
Patrick Sullivan
Posts: 13
 
Re: OLE Provider ConcernsYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 15 May 07 10:40 AM
fiogf49gjkf0d
Yeah I have been looking at the IDs a lot. I think my SSIS Package will use a mid-tier type table to store say the accounts or contacts I am bringing over and toss a row count into a package variable then I can use the slx_DBIDs('ACCOUNT', N) to build N IDs for the table and just union or merge the column into the midtier. Then at the end I can just insert that mid level table to where it needs to go.
[Reply][Quote]
Phil Parkin
Posts: 819
Top 10 forum poster: 819 posts
 
Re: OLE Provider ConcernsYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 15 May 07 6:03 PM
fiogf49gjkf0d
So you are thinking of creating an 'intermediate' ACCOUNT table that is empty apart from x thousand rows of IDs and then merging into that?

I guess you could do it by record number - maybe. Eg if you have another IDENTITY(1,1) field in the intermediate table and match that to your source data somehow...

update iAccount
from iAccount join iSource on iAccount.RecNo = iSource.RecNo
set ifield = iSource.field1, ...

Then at least you can harness some SQL Server processing power - and you'd only have to create the IDs once.

Anyway - sounds like you know what you're talking about - good luck.
[Reply][Quote]
Patrick Sullivan
Posts: 13
 
Re: OLE Provider ConcernsYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 21 Jun 07 3:35 PM
So I ran into a minor issue. Here's the jist: I have all my data ready to get SLX IDs for the account migration. I created an OLE DB Source in a data flow task and the connection is a SLX OLE connection to our dev SLX Server. The SQL Command for the OLE DB Source is slx_DBIDs('ACCOUNT', ?) and i am passing in my parameter for row count in my mid tier account table. When i first attempted this I thought no way it works but low and behold the OLE DB Source is seeing that I am getting a table back with the column named ID. I was like wow awesome easy. Then I realized it is seeing the Column as a char(13) instead of a char(12) for some reason. All my attempts to mess with the properties in the advanced editor are not doing what I thought they woul so I am still trying to get this last issue resolved.

Anyway if you have an idea let me know. I am also going to post this as its own topic. Thanks
[Reply][Quote]
 Page 1 of 1 
  You can subscribe to receive a daily forum digest in your user profile. View the site code of conduct for posting guidelines.

   Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
 

 
 slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation.
 This site, and all contents herein, are Copyright © 2024 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.

code of conduct | Subscribe to the slxdeveloper.com Latest Article RSS feed
   
 
page cache (param): 7/14/2024 3:47:18 AM