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