Access

MS Access is the database of choice for countless individual users and small workgroups. It has many great features and wizards that enable a small application or prototype to be quickly developed. Often, when an application has outgrown its humble Access origins, discussions of moving the data to SQL Server emerge. Many times, the client will be rewritten as a Web or desktop application using VB.NET or another language. Sometimes the plan will be to link to the SQL Server tables, utilizing the existing Access front-end. Unfortunately, if the original application was poorly designed, moving the data to SQL Server will not improve performance.

Note

Designing an application with an SQL back-end and Access front-end that performs well is beyond the scope of this book. To learn more about creating Access applications where SQL Server hosts the data, read Microsoft Access Developer's Guide to SQL Server, by Andy Baron and Mary Chipman (SAMS, 2000) or Microsoft Access Projects with Microsoft SQL Server, by Ralf Albrecht and Natascha Nicol (Microsoft Press, 2002).

Also, keep in mind that Access select queries will be imported into SQL Server as tables. Any queries that must be ported to the SQL Server database will have to be rewritten. Many select queries can be rewritten as views. Update, append, delete, create table and parameterized select queries can be rewritten as stored procedures if you need to move them to the SQL Server database. There may also be VBA (Visual Basic for Applications) functions used in queries. You may want to rewrite them as CLR User Defined Functions in SQL Server. What you do depends on the requirements for your application or solution, and that discussion could fill up a whole book in itself.

Importing Access tables is similar to importing Excel worksheets. It is very easy to accomplish using the Import and Export Wizard and, if you have several tables to import at once, that's probably the way to go. See Chapter 2 for detailed instructions on how to use the wizard. In this section you will learn how to import data from Access by building an SSIS package along with a few tips specific to Access.

Understanding Access Security

Connecting to an Access database is usually quite simple. If Access security has been enabled on the database, it gets a bit more complicated. Before you learn how to import from Access, take a quick look at how Access security works.

The simplest way to "secure" an Access database is to set a Database Password. This is done by opening the database in exclusive mode (see Figure 8-14) and then entering a password in the Set Database Password dialog box found at Tools Security Set Database Password. After that, the password must be supplied by anyone who opens the database, including your SSIS package.

Figure 8-14

The other method involves associating a database with a Workgroup Information file (system.mdw), setting up groups and users, and configuring permissions. Users attempting to open the database authenticate against the workgroup file. Access provides a wizard that can be used to set up groups and permissions, simplifying the process.

A default Workgroup file is specified in the registry for the user. If more than one individual shares the same database, usually a Workgroup Information file is stored on the network. Either that file can be the default workgroup file for the user or it can be used only for a specific database. If it is for the specific database, the path to the workgroup file must be specified as a startup command-line argument in a shortcut provided to the user. A user name and password can also be included in the command, like this:

 "C:\Program Files\Microsoft Office\Office11\MSACCESS.EXE"      c:\chapter8\Northwind.mdb /wrkgrp c:\chapter8\system.mdw      /user kim /pwd kimspw 

If the Access database does not have security enabled, all users actually open the database as the Admin user with a blank password. That enables the user to create, modify, and own all of the Access objects without even being aware that security, albeit not much security, is being used in the background. Creating a nonblank Admin password effectively enables security.

The Admin account is the same account no matter which Workgroup Information file is used, and it is a member of the Admins group by default. It seems counterintuitive, but moving the Admin account to a group with no object permissions and removing it from the Admins group is an Access security best practice. This keeps anyone with a copy of Access who opens the file using the Admin account from a different Workgroup file from viewing or modifying data. Before taking the rights away from Admin, another account must be added to the Admins group.

If you would like to learn more about how to set up Access security, refer to Microsoft Access Help for more information.

Configuring an Access Connection Manager

Once the Connection Manager is configured properly, importing from Access is simple. First, you'll look at the steps required to set up the Connection Manager.

Create a new SSIS project and create a new Connection Manager by right-clicking in the Connection Managers section of the design area of the screen. Select New OLE DB Connection to bring up the Configure OLE DB Connection Manager dialog box. Click New to open the Connection Manager. In the Provider drop-down list, choose the Microsoft Jet 4.0 OLE DB Provider and click OK.

The Connection Manager dialog box changes to an Access-specific dialog. Browse to the Access database file to set the Database File Name property. You are using the Northwind MS Access sample database for this example.

By default, the database user name will be Admin with a blank password. If security has been enabled for the Access database, a valid user name and password must be entered. Enter the password on the All pane in the Security section. The user Password property is also available in the properties window. Check the Save My Password option. Additionally, the path to the Workgroup Information File (system.mdw) must be set in the Jet ODBC:System Database property, also found by clicking the All tab.

If, on the other hand, a database password has been set, enter the database password in the Password property on the Connction pane. This also sets the Jet ODBC:Database Password property found on the All tab.

If both a database password and user security have been set up, enter both passwords on the All pane. In the Security section, enter the user password and enter the database password for the Jet OLEDB:New Database Password property (see Figure 8-15). Check the Save my password option. Be sure to test the connection and click OK to save the properties.

Figure 8-15

Importing from Access

Using the project you created in the last section with the Access Connection Manager already configured, add a Data Flow task to the Control Flow design area. Click the Data Flow tab to view the Data Flow design area. Add an OLE DB Source component and name it Customers.

Double-click the Customers icon to open the OLE DB Source Editor. Set the OLE DB Connection Manager property to the Connection Manager that you created in the last section. Select Table or View from the Data Access Mode drop-down list. Choose the Customers table from the list under Name of the Table or the View (see Figure 8-16). Click Columns on the left where you can choose which columns to import and change the output names if you need to. Click OK to accept the configuration.

Figure 8-16

Create a Connection Manager pointing to AdventureWorks. Create an OLE DB Destination component and name it NW_Customers. Drag the connection (green arrow) from the Customers source component to the NW_Customers destination component. Double-click the destination component to bring up the OLE DB Destination Editor and configure it to use the AdventureWorks Connection Manager.

You can choose an existing table or you can click New to create a new table as the data destination. If you click New, you will notice that the Create Table designer does not script any keys, constraints, defaults, or indexes from Access. It makes its best guess as to the data types, which may not be the right ones for your solution. When building a package to be used in a production system, you will probably want to design and create the SQL Server tables in advance.

Note

A tool that could save some time when porting Access tables to SQL is the Access Upsizing Wizard. This can be found in the Tools Database Utilities menu of Access. This tool will enable you to upload the table and attributes along with or without the data. You still need to review the data types and the index names that the wizard creates, but it could save you quite a bit of time over the manual process.

For now, click New to bring up the table definition (see Figure 8-17). Notice that the table name is the same as the destination component, so change the name to NW_Customers if you did not name the OLE DB Destination as instructed previously. Click OK to create the new table. Click Mappings on the left to map the source and destination columns. Click OK to accept the configuration.

Figure 8-17

Run the package. All of the Northwind customers should now be listed in the SQL Server Table. Check this by clicking New Query in the Microsoft SQL Server Management Studio. Run the following query to see the results (see Figure 8-18):

 USE AdventureWorks GO SELECT * FROM NW_Customers 

Figure 8-18

Empty the table to prepare for the next example by running this query:

 TRUNCATE TABLE NW_CUSTOMERS 

Using a Parameter

Another interesting feature is the ability to pass a parameter from a package variable to a SQL Command.

Note

In Access, you can create a query that prompts the user for parameters at runtime. You can import most Access select queries as tables, but data from an Access parameter query cannot be imported using SSIS.

Using the package you started in the last section, create a variable to hold the parameter value. Move back to the Control Flow tab and right-click the design area. Choose Variables. Add a variable by clicking the Add Variable icon. Name it CustomerID. Change the Data Type to String. Give it a value of ANTON (see Figure 8-19). Close the Variables window and navigate back to the Data Flow tab.

Figure 8-19

Note

The design area or component that is selected determines the scope of the variable when it is created. The scope can be set to the package if it is created right after clicking the Control Flow design area. You can also set the scope to a Control Flow task, Data Flow component, or Event Handler task.

Double-click the Customers component to bring up the OLE DB Source Editor and change the Data Access Mode to SQL Command. A SQL Command text box and some buttons appear. You can click the Build Query button to bring up a designer to help build the command or click Browse to open a file with the command you want to use. For this example, type in the following SQL statement (see Figure 8-20):

 SELECT CustomerID, CompanyName, ContactName, ContactTitle,    Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = ?) 

Figure 8-20

The ? symbol is used as the placeholder for the parameter in the query. Map the parameters to variables in the package by clicking the Parameters button. Set the name of the parameter to ?. Choose User::CustomerID from the variable list and click OK (see Figure 8-21).

Figure 8-21

Note

Variables in SSIS belong to a namespace. By default, there are two namespaces, User and System. Variables that you create belong to the User namespace. You can also create additional namespaces. See Chapters 6 and 7 to learn more about variables.

Note that you cannot preview the data after setting up the parameter because the package must be running to load the value into the parameter. Click OK to accept the new configuration and run the package. This time, only one record will be imported (see Figure 8-22).

Figure 8-22

You can also go back to SQL Server Management Studio to view the results.

 USE AdventureWorks GO SELECT * FROM NW_Customers 

If you wish to use multiple parameters in your SQL Command, use Parameter0, Parameter1, etc., for your parameter names instead of the ?.

Set up a second Package-level variable for CompanyName and set the value to Island Trading. Change the query in the Customers component to the following:

 SELECT CustomerID, CompanyName, ContactName,    ContactTitle, Address, City, Region,    PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = Parameter0) OR    (CompanyName = Parameter1) 

Now the Parameters dialog box will show the two parameters. Associate each parameter to the appropriate variable (see Figure 8-23).

Figure 8-23

Delete the row from the table and run the package. Now two customers will be listed in the table.

Importing data from Access is a simple process as long as Access security has not been enabled. Often, porting an Access application to SQL Server is the desired result. Make sure you have a good book or resource to help ensure success.

Now you'll see just how easy it is to import from Oracle, as long as you do a bit of configuration first.