Importing data into SalesLogix using SQL DTS – Part 2

This is a continuation of this article in which I begin to discuss importing data into SalesLogix using SQL DTS.

Importing the Source File

SQL offers an import wizard that is useful for importing data into a SQL database.  Alternatively you can import data from scratch by utilizing a DTS package.  I will demonstrate how to import the data using the wizard and then how to save that import to a DTS package so that you can see what steps comprise an import in a DTS package.  Note:  This step discusses importing data into our “Sandbox” database, rather than directly into SalesLogix.  This is because, as I discussed in my previous post, the data format of our source file is not compatible with the structure SalesLogix is expecting.

  • The first step to importing data is to open up the SQL Enterprise Manager.

  • Expand out the SQL server instance that houses the “Sandbox” database.

  • Within the databases folder, right click on the “Sandbox” database. Choose “All Tasks…Import Data”.

SQL Import Wizard Menu

  • This will launch the SQL import wizard.

SQL Import Wizard Screen 1

  • Click Next on the first screen.

  • The next screen defines the source data type you will be inserting into.  Based upon the data source type that you choose, you will get different parameters to identify the source details.

  • Since in my example, I am importing an Excel file I will choose “Microsoft Excel” in the data source menu. 

  • Since I have chosen this data type I am then prompted for the location of the Excel file.

SQL Import Wizard Data Source

  • Upon clicking Next, I am now prompted to define the destination:

  • Since I am importing into my “Sandbox” SQL database, and since I launched the Import Wizard by right clicking on my destination I already have my destination set.  If you do not have it defined, you can do so on this screen.

  • You do want to change the authentication mode to SQL server and then specify the sysdba user.  This will ensure the new database objects this import will create are correctly owned by sysdba.

SQL Import Wizard Data Destination

  • Click Next.  You will now be prompted to choose how you are going to import the data.  Since we are loading in the entire spreadsheet, I will choose “Copy table(s) and view(s) from the source database.

SQL Import Wizard Import What

  • Click Next.  The next screen defines which tables to import.  Since we are dealing with Excel it presents us with the sheets in the file:

SQL Import Wizard Table Selection

  • Since all of my data resides in the first sheet, I will only select that one.  When I select that sheet, in the destination column I can specify the name of the table I want it loaded into in my SQL “Sandbox” database.

SQL Import Wizard Table Selection with Destination

  • Notice I have changed the default destination name to “ImportTable”.  Also notice that it is being inserted into “Sandbox” with an owner of “Sysdba”.

  • Now I click on the Transform ellipse in Transform column.  This will open the transformation dialog.

SQL Import Wizard Transformation

  • We can see that the Create Destination table is selected with no other options.  This is because SQL has determined the name I used in the Destination column of the previous screen does not yet exist.  If I had entered an existing SQL object name I would have the choice to append data to the existing table or to first clear it out and then import the data.  In the Mappings grid, notice that the source and destination field mappings are automatically named, as is each field type.  You can change these attributes if you would like.  In this example I will leave all of this as is.

  • Click OK to close the Transformation dialog and get back to the previous window.  Click Next.

  • We are now on the final screen of the import wizard.  here you can specify if you want to run the package now to actually do the import or if you want to schedule it for a later time.  I will select to run it immediately and also to save it to a DTS package so we can take a look at it.

SQL Import Wizard Summary

  • Since I chose to save the import to a DTS package, when I click Next, I will be prompted to save the DTS package.  If you did not select this option the import would have started.

SQL Import Saving DTS

  • I have named my DTS package “Test Import Package”.  When I click Next I now see the summary screen of the Import wizard.

SQL Import Wizard Final Screen

  • Clicking Finish will Save my DTS package and actually run the import.

SQL Import Running

SQL Import Finished

So now the data has been loaded into our “Sandbox” SQL database.  If we expand out the Sandbox database and look at the tables we can now see our “ImportTable” table.

SQL DTS Import Table Listing

Now lets take a look at the package that was created. 

  • In Enterprise Manager, I expand out the Data Transformation Service folder.  Under Local Packages, I now see my DTS package that was saved as part of the import wizard process described above.

SQL DTS Package List

  • Double click on the package to open up the DTS designer and see the actual import steps

SQL DTS Import Package Design View

Here we can see it contains three components:

  1. A create table statement to run the creation of the new database table “ImportTable” (This is because the import wizard determined that we needed to create this table since it did not already exist).

  2. A source definition of the Excel file

  3. A target destination of our SQL “Sandbox” database.

The line connecting the source Excel file and the SQL database destination is the transformation details where the mappings occur between the source and target.

There you have it.  We have now loaded in our sample import file into our Sandbox database.  Now that the data is loaded in we can start to transform the data so that it is in a format compatible with SalesLogix.


Kris Halsrud

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

1 Comment

  1. Hi Kris, I understand not wanting to buy an 3rd party tool just for the initial data import… Still, I would recommend using the SLX Provider even if it means longer import time, being a 1 time thing the time should not matter. That overhead caused by the SLX Provider is actually all the data validation that is performed on the background (plus the security validation too, which is not really needed in this especific case), but importing the data without all that validation can cause some ugly problems in the future that are actually quite dificult to troubleshoot…


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!