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.
