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:

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”


• 


• 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.


• 


• 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.)


• 

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