DTSX package to upload a table from Access Database to SQL Server

Question

The Problem:

I recive monthly data updates for a SQL databse shipped to me in an ACCESS database, there are about 30 tables that follow this pattern the largest table contains ~40k rows.

CREATE TABLE [dbo].[TABLE_NAME_HERE] (
[title] nvarchar (255) NULL, 
[classification1] nvarchar (255) NULL, 
[classification2] nvarchar (255) NULL, 
[1986] float NULL, [1987] float NULL, [1988] float NULL, [1989] float NULL, [1990] float NULL, [1991] float NULL, [1992] float NULL, [1993] float NULL, [1994] float NULL, [1995] float NULL, [1996] float NULL, [1997] float NULL, [1998] float NULL, 
[1999] float NULL, [2000] float NULL, [2001] float NULL, [2002] float NULL, [2003] float NULL, [2004] float NULL, [2005] float NULL, [2006] float NULL, [2007] float NULL, [2008] float NULL, [2009] float NULL, [2010] float NULL, [2011] float NULL, 
[2012] float NULL, [2013] float NULL, [2014] float NULL, [2015] float NULL, [2016] float NULL, [2017] float NULL, [2018] float NULL, [2019] float NULL, [2020] float NULL, [2021] float NULL, [2022] float NULL, [2023] float NULL, [2024] float NULL, 
[2025] float NULL)

We currently have a DTSX package that uploades these table however every so often (yearly) they add another column, and the DTSX package has the column details harded coded.

We do not have the time or budget to reprogram the applications envolved - though I agree there are many better ways of storing this data.

I would like to have the DTSX package automaticaly DROP the table on the SQL server, CREATE a TABLE that matches the one the ACCESS database and upload the data.

Is this possible?

Answer 1

Is it an option to change the table structure? For example if you had a child table with a column called Year and another called Value then there would be no need to keep adding new columns year on year. It is good practice to store data in tables rows rather than in the column names.

It is also possible for example to use ADOX in MS-Acceess VBA to loop through the Fields Collection of the Access table and from this create a new table in SQL Server. Code would be something like in this link but would need changing to work with a SQL Server connection etc.

Answer 2

One of the options in DTS is to create a new table at import. But as the previous post suggests you should review the problem cause normally there is no need (and it is heavily advised against) to change a table. When there is a new column than you have to change the code of your program anyway unless the new column is completely useless. In this case just drop the column before importing and you are fine again. If the column is useful then how will you use it without writing code that refers to it (after the create/import of the table)???