Toad World® Forums

Import Wizard Add Query data Type

I have been using TDA 2.6, and now 2.7 (and have seen 3.0) + SQL Server as a ETL tool with very good success. (We used to use Access.) I am now looking to extend this functionality with Automation, and believe it holds a lot of promise to become an end-to-end solution for our team.

Our work process involves:
(1) Receiving data from external parties in Access, Excel, SQL Server, Oracle, and even CSV format. (Access, followed by Excel, are the most common.)
(2) Importing the data into a SQL Server database
(3) Running a number of transformations using procedures, functions, and views
(4) Exporting the resulting views to CSV files
(5) Importing the CSV files into an application

I have been using Import Wizard Add File to import source Excel worksheets into the SQL Server database, and SSIS to import source Access tables into the SQL Server database.

Based on a forum search, I have been trying Import Wizard Add Query for Access tables (SELECT * FROM tablename). The issue I am running into is with the data Types. It appears that TDA is choosing a data Type and size based on the first record in the query(?), and the import fails when there are data type or length conflicts in subsequent records. How can I get TDA to set the data Type and length based on the Access table field definitions (as is done in SSIS).

One idea I am considering s to write an Access VBA module that would create a TDA Import Template (xml) tim file based on the table field definitions.

Thank you.

Import is actually doing a sampling of the first 1000 rows to deterimine data type and size. But this of course, may not cover all of the situations.

In the Import templete you can manually change the type of any column when importing into a new table. Just choose from the drop down of datatypes or type in. The template will remember your choice. See screenshot.