Importing data into Infor CRM (Saleslogix) is a task that is typical for any system. However, sometimes the custom format table ID values are a roadblock in using standard, or widely used tools to do the job. In this article I will look at creating a simple data import using SQL Server Integration Services (SSIS) and have the Infor CRM table ID values auto-created for us. SSIS is a good choice for any import since it’s a tool you already have that is easy to use.
Auto-Creating Table ID Values
First of all, lets discuss how to have the Infor CRM table ID values auto-created. That’s the key to all of this working. The Saleslogix OLEDB Provider has the capability, built-in, to create the table ID values for us. We just need to tell it to do that. There is a setting in the Database Manager in the Administrator that we can use to turn on auto-incrementing for a table ID. Double-click the ACCOUNTID column in the Account table in the Database Manager. You’ll see the following:
All we need to do is check that box at the bottom of the dialog. That will turn on auto-increment for the ACCOUNTID field. Any time we omit that column in an INSERT statement it will create one for us. NOTE: Before we can use it we need to restart the SalesLogix Server service. Once we’ve restarted the service, we can execute a SQL INSERT through the SalesLogix OLEDB Provider and we can see that it will create a new ACCOUNTID value for us, such as this INSERT statement:
insert into account (type, account, seccodeid) values ('Test', 'Test Account', 'SYST00000001')
Note, that we’ve completely omitted the ACCOUNTID column from the statement. If that column is included and set with a value of NULL it won’t auto-create the ID, we must leave it out completely. Also note, this only works through the SalesLogix OLEDB Provider, not directly via SQL using SQL Management Studio, etc.
Creating the SSIS Package for the Import
Now we can get into creating the SSIS package for the import. I thought a video would best show how to do this. Keep in mind, from this point on, it’s just like creating any SSIS package. The only requirements as far as importing into Infor CRM goes is the following:
- Our destination connection (the Infor CRM target database) is an OLEDB Connection using the SalesLogix OLE DB Provider
- That the table we’re importing into has the auto-increment setting turned on for it’s primary table ID
- That we have the primary table ID mapping set to “ignore” in the SSIS package (see video for details).
Take a look at the video below to see a complete walkthrough:
Keep in mind, you do need to make your package run as a 32-bit package on 64-bit SQL Servers. See this post for more details: Running SSIS on a 64 bit machine using the SalesLogix Provider
Good luck.
Thank you so much for this demo! I had one question. When using the Saleslogix OLE DB provider, did you find a way to allow fast loading? I’m trying to move 80k rows from a MYSQL database and the push to SLX is taking 30+ minutes.
My workaround is to fast load the data into staging tables using a SQL Server Destination (2 minutes), then write a unique id for each record using the Saleslogix OLE DB this will also write in the seccodeid, etc. Once these rows are created I would then come back through and update the new rows with my data from the staging tables.
I’d sure love a better idea (:
Sadly, no fast load available with the SLX OLEDB provider. However, if there’s no sync involved, I typically don’t use the SLX provider at all (again, only if there’s no sync). If I have some unique value in my source data that is less than 12 chars, I’ll pad that value and use it as my record ID, otherwise, I’ll precreate the ID values (or do a rank over my source data to create a unique value – search our site for “SQL table id” and you’ll find some SQL procs and functions to do record ID values in SQL). Doing things this way allows you to just load the target tables with INSERT INTO TABLE SELECT FIELDS FROM… or for updates UPDATE TABLE SET FIELDS FROM… and 80k rows will load in the blink of an eye.
However, if you are using sync, then no other way than the one record at a time approach through the OLEDB provider.
Ryan
Hello,
We manage to import data with the OLE DB Provider, but how can we update existing data without use the SSIS script component? This component is really slow and we have a large volume of data to sync, plus we have remote computers.
Regards Yoann