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.
Open Visual Studio and create a new Integration Services (SSIS) Project.
SSIS Connection Managers
Right click in the Connection Managers area, select New OLE DB Connection.
Click New in the Configure OLE DB Connection Manager window to configure a new connection to an Access Database.
Select Native OLE DB Microsoft Jet 4.0 OLE DB Provider. Browse to select the Access database and click OK.
Configuring the SSIS Steps in the Data Flow Task
Drag and Drop a Data Flow task on the Control tab.
Click the Data Flow tab and add an 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.
Click columns to check the column information is correct.
Click on the Error Output to view or make changes to how SSIS handles errors when processing the source data.
Right click on the Connection Managers area in the Control tab and select New File Connection. Select Excel as the type.
Browse to get the path to the Excel file to use in the project.
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.
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.
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.
Click on the Mappings properties to check the column mappings. Click OK to close the dialog box.
Run the Data Flow Task to see if any errors are generated.
Verify the data successfully transferred to Excel by opening the file.
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).
Set the EvaluateAsExpression property of the variable to True.
The data access mode is set to Table name of view name variable. and the tableName variable is selected.
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.
The package is executed with Success
*The data access mode can also be changed to a SQL Command. Still using newView, a select statement is created.
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.
Clicking on the Preview button confirms the 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.
The OLE DB properties can then be modified to accept the variable for the data access mode.
*Note regarding data types*
The data types for the columns in this article are Text in Access with a limit of 255 characters.
If the data types were changed to data type Memo and characters added to exceed the 255 character limit, the package will fail.
An error will be generated: "Verify that the column metadata is valid"
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).
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.