Importing data into SalesLogix using SQL DTS – Part 1

While SQL DTS has now been replaced by SQL SSIS, I have always wanted to write up this post about how you can use DTS to import data into SalesLogix.  This post also describes some general concepts that can be used in SQL servers at 2000, 2005, or 2008 versions.  I hope to also write up another post about how to do this same type of stuff in SQL SSIS in the near future.


While there are a myriad of ways to import data into SalesLogix, SQL servers integration functionality (wether it be SSIS or DTS) have a couple of advantages in my book:


1 You likely already own them.  No additional software to purchase or maintenance fees.


2 These tools are well known and widely used.  You are more likely to find quality free support on-line or at least find resources that can help you.  Other import tools are too often niche players that do not have a wide adoption base.


3 Flexibility.  The SQL integration tools (especially SSIS) offer a whole world of capability.  Probably more than you will ever be able to utilize.  One of the basic benefits over something like the SalesLogix import wizard is the ability to do inserts, as well as updates and deletions.


OK, so lets take a look at a very simple scenario of importing an Excel file that contains just accounts and their related addresses.


The source data


The first step with any import is to get your source data into a format that correlates to the SalesLogix data format.  Data imports often deal with flat files or files where the column names do not match the target column names.  IN my scenario we have an Excel file that contains accounts and addresses in a flat format.  To keep things simple I will be only working with four records in my source file.  Lets go with something like this:





















































Company Phone Address Address2 City State Zip Country Mgr

Abbott Worldwide


444-333-2222


Some street


 


Plainsville


TX


10239


USA


Lee


Trees Inc


3332221111×234


1 Oak St

 

Allen


NC


33222


USA


Linda


Apple Basket Builders


 


333 Maple


Suite 2


Stonebrooke

MN 55422   Lee
Winston House 4445553332     Lily AZ 33211   Lee

While this is about as basic as you can get for a file, it should allow us to go through demonstrate the main points


The Sandbox


The approach I take when needing to manipulate a source file is to create a SQL database that I can use to perform the database transformations.  For the purposes of most installs I create this “Sandbox” database on the same SQL server as my test SalesLogix target database.  To create a Sandbox database:



  • Open the SQL Server Enterprise Manager.

  • On the SQL server instance with your test database, right click on the database folder and choose “Create new database”

  • SalesLogix Import using DTS - Creating a new DB

  • On the Database Properties dialog that opens, enter in the name of the database to be created. I am calling mine “Sandbox”.  You may also need to specify the location and settings for the data and transaction files if you wish to change them from the default.

  • SalesLogix Import using DTS - Creating a new DB Dialog

  • Click OK to create your database.

  • Now if you expand out your databases folder on the SQL server instance you should see you new database listed.

  • Expand out the database, and under users add the Sysdba user as a DB Owner (This is so that the tables and views in the sandbox will be the same owner as the target tables in SLX.  This is not required but is more convenient.)

  • Adding SYSDBA user

OK, so now we have a database created in which we can load the source data into.  In my next post I will talk about actually importing the file.  I will be writing up posts on the following topics soon.  Once I have these topics completed I will update the following topics with a link to the corresponding article.


Importing the Source File


Transforming the Data Source


Auto-increments


The DTS Package


Data Driven Queries


Running the Package


The SSIS Equivalent

ABOUT THE AUTHOR

Kris Halsrud

Kris Halsrud is a Senior Analyst / Developer for Customer FX Corporation.

1 Comment

  1. Kris, any idea when you’re going to get the remaining articles posted for this?

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!