Posted by arlene

Let’s have a go now at creating our own DTS package to bring in the new data for our spies, bad guys, and so forth.

To import the primary tables, follow these steps in the wizard. Then, refer to this set of steps later in this section when you import the remaining tables.

Once again, navigate through the server until you see the Data Transformation Services folder. Right-click the folder, select All Tasks, and then Import Data.

This will launch the DTS Import/Export Wizard, which allows us to begin specifying the different options for our import process. The initial wizard screen.

Like most of the wizards, the first screen doesn’t allow you to do too much, so click Next.

The next screen is a little more interesting and allows us to specify the source from which we want to retrieve our data.

The Source drop-down box allows us to specify the place our data comes from. Select Microsoft Excel 00-2008. This will change the screen so that it has only a filename box available.

After you have entered the file path of where the Excel Spreadsheet is or navigated your way through the file system with the ellipsis (…) button, click Next.

The next screen allows us to select the data source that we want the import process to import our data into. This can be a SQL Server 2008 database or almost any other data source.

You need to set the following options for the Destination step in the wizard:

• Select Microsoft OLE DB Provider for SQL Server in the Destination drop-down box.
• Select your server from the Server drop-down box.
• Use SQL Server authentication, and enter sa in the Username box and the password for sa in the Password box.
• Select the SQLSpyNet database from the Database drop-down box. If the
  database drop-down does not contain anything, click the Refresh button.

When you’ve set the configuration options, click the Next button.

The next screen, Specify Table Copy or Query, allows us to choose whether we are going to copy whole tables or specify our own query to copy the data. Because we are copying from an Excel spreadsheet, we are going to specify the first option, Copy table(s) and view(s) from the source database.

After you’ve selected the option, click Next. This will bring up the screen.

This screen allows us to select the tables that we want to copy into, as well as specify the columns that we want to copy into.

We need these mappings:

• ActivityType sheet—ActivityType table
• AddressType sheet—AddressType table
• Country sheet—Country table
• Person sheet—Person table

Do not check the Include all Primary & Foreign Keys option.

When you have the mappings right, click the Next button.

This screen allows us to specify whether the task is run immediately or scheduled for later use. We want to run the task immediately.

The Save DTS package option allows us to specify whether we want to save the package so that we can run it again in the future. If we save the package not only can we run it again later, but we can also modify the package to have custom tasks added.

Select the Save DTS Package option as a SQL Server package. The different save options offer us

• SQL Server—The package is stored in the msdb database within the sysdtspackages table.
• Meta Data Services—This allows us to save the package within the Meta Data Services.
• Structured Storage File—Allows us to save the package as a COM (Component Object Model) storage file.
• Visual Basic File—Allows us to save the package as a VB file.

When you are happy, click Next.

The following screen allows us to specify a name for the package that we are going to save.

Like everything else, we need to enter a name and description for the package that is descriptive and identifies what we are doing, like so:

• Name— SQL Spy Net Excel Primary Table Import.
• Description—An Excel spreadsheet import that repopulates the database primary tables, run by Rob 21 Aug 00.
• Owner Password—This option allows us to specify a password for the owner to protect the package. Leave this blank.
• User Password—This option allows us to specify a password for the user. This means the user can run the package but cannot view the package contents. Leave this option blank.
• Server details—Set these to the server that SQLSpyNet database resides in, using the username and password. This is the server that the DTS package will be saved to.

After you have set the options, click Next.

The final screen is the last step in the wizard.

When you are happy, click Finish. This will execute the DTS package immediately and import our data.You will see a screen.

When the wizard has finished importing our primary tables, we need to import the secondary (or child) tables. Run the wizard again, creating a new DTS package. This time use the SpyDatabaseSecondaryTables.xls file, and map the following tables to the worksheets in the . xls file:

• Spy sheet—Spy table
• BadGuy sheet—BadGuy table
• Address sheet—Address table

As before, save the DTS package, using the following options:

• Name—SQL Spy Net Excel Secondary Table Import.
• Description—An Excel spreadsheet import that repopulates the database secondary tables, run by Rob 21 Aug 00.

So, and I know this gets repetitive, run the wizard again, creating a new DTS package. This time use the SpyDatabaseActivityTable. xls file, and like the first two times, map the Activity worksheet in the .xls file to the Activity table in the SQLSpyNet database.

• Like the first (and second) time, save the DTS package, using the following options:
• Name—SQL Spy Net Excel Activity Table Import.
• Description—An Excel spreadsheet import that repopulates the database Activity table, run by Rob 21 Aug 00.

After the package has finished running, we should perform a quick check on the tables to ensure that our data has come across successfully.

Possibly related posts: (automatically generated)
Using the DTS Wizard to Reload Our SQLSpyNet

• Using the New Wizards in SQL Server 2000
• Setting Up Your PC as a Web
• Altering the Size of the Transaction Log Files
• Connecting to SQL Server 2000 for the First Time
• Installing DNS on a Windows Server
• Dedicated Server with Canadian Web Hosting
• Primary, Secondary, and Caching-Only Name Servers
• Configuring the DHCP Server and Scope Options
• Web Servers and System Hardening
• Installing Active Directory on a Windows Server 2003 Computer