Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


SSIS Data Transform Task from Access to Excel

SSIS Data Flow task using Access as an OLE DB source into an Excel Destination

This article describes the process of creating an SSIS package to pull data from a MS Access data source into an Excel worksheet. An Access database using data from the SQL Server AdventureWorks database is used for sample data.

Getting started with SSIS MS Access to Excel

To begin, create a Microsoft Access database with a table. This article uses a table called newView which was created from data in the AdventureWorks database.

Access Database

Open Visual Studio and create a new Integration Services (SSIS) Project.

SSIS Project

SSIS Connection Managers

Right click in the Connection Managers area, select New OLE DB Connection.

Connection Managers

Click New in the Configure OLE DB Connection Manager window to configure a new connection to an Access Database.

Configure Connection

Select Native OLE DB Microsoft Jet 4.0 OLE DB Provider. Browse to select the Access database and click OK.

SSIS Add Connection

Configuring the SSIS Steps in the Data Flow Task

Drag and Drop a Data Flow task on the Control tab.

Data Flow Task

Click the Data Flow tab and add an OLE DB Source.

OLE DB Source

Right click on the OLE DB Source and select Edit to bring up the OLE DB Source Editor. Select the table name.

Configure DB Source

Click columns to check the column information is correct.

OLE DB Columns

Click on the Error Output to view or make changes to how SSIS handles errors when processing the source data.

OLE DB Error Output

Right click on the Connection Managers area in the Control tab and select New File Connection. Select Excel as the type.

SSIS Connection Manager

Browse to get the path to the Excel file to use in the project.

Excel Connection Manager

Add an Excel Destination from the Data Flow Destinations to the Data Flow tab. Connect the Precedence Constraint (Green Arrow) to the Excel Destination. Right click the Excel Destination and select Edit.

Excel Destination

Check to see that the correct Connection is in the Drop Down for the connection manager. Click on New next to the Name of the Excel sheet to create a new worksheet for the Access data in the Excel file.

Excel Connection

SSIS DataTransform Task Data Types and Column Mappings

Verify the data types are correct for the new worksheet. The name of the Table has been changed from the default of "Excel Destination" to "new View", the name of the Access data source.

Create Worksheet

Click on the Mappings properties to check the column mappings. Click OK to close the dialog box.

Excel Column Mappings

Run the Data Flow Task to see if any errors are generated.

Data Flow Test

Verify the data successfully transferred to Excel by opening the file.

Excel Data

SSIS Data Source in a Package

*The OLE DB Data Source is not limited to just tables and views. The Data Access Mode may be a variable created at the Package level. In this instance, a variable named tableName was created within the Scope of the Package (be careful not to create it with a Scope of DataFlow or it will not be accessible to dtexec).

Variable

Set the EvaluateAsExpression property of the variable to True.

Variable Properties

The data access mode is set to Table name of view name variable. and the tableName variable is selected.

OLE DB Using Variable

From the command window, dtexec is called passing the file name of the package and setting the variable to newView (or any data source with the same column names and data types. For additional information on DTEXEC, click here: link to DTEXEC.

dtexec

The package is executed with Success

Successful Package Execution

*The data access mode can also be changed to a SQL Command. Still using newView, a select statement is created.

SQL Command

Additional SSIS MS Access to Excel Notes

A note about syntax, when using expressions, the SQL syntax must be used instead of the Access syntax. The query below will produce a result set of 12 rows using the '%' T-SQL wildcard, but will not return any results if the Access wildcard of '*' is used.

SQL Syntax

Clicking on the Preview button confirms the results.

SQL Results

*The data access mode can also be set to SQL command from a variable. Here the variable tableName has its Value set to the SQL command SELECT statement.

Variable Value Update

The OLE DB properties can then be modified to accept the variable for the data access mode. OLE DB Update

*Note regarding data types*

The data types for the columns in this article are Text in Access with a limit of 255 characters.

Access Table

If the data types were changed to data type Memo and characters added to exceed the 255 character limit, the package will fail.

Access Table Modified

An error will be generated: "Verify that the column metadata is valid"

SSIS Error

Although the Excel table was created with LongText, once it was opened, the file reverted to a Text data type of 255 characters. In the Advanced Editor, the data type for the Excel Destination is showing as DT_WSTR when it should be DT_NTEXT. Resetting the values here will not produce the desired result as the SSIS Data Flow task will complain that the Excel columns are out of sync and reset the value back to DT_WSTR for you automatically (not what you wanted).

Advanced Editor View

The trick here would be to add an Execute SQL Task with a File System Task to drop the Excel table and recreate it each time with the correct data types to ensure success. There are additional details on how to do this here.

This article has covered the basic task and covered some of the additional functionality of using SSIS with Access and Excel.