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

• This will launch the SQL import wizard.

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

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

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

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

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

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

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

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

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

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

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.

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.

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

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.