I created a Dts package to copy one or both of the tables in an Access database to a Sql server database. I created the connections, created global parameters, and in the Transform data Task properties, I chose "SQL query" and wrote this
IF ? = 1 -- gvTable1Flag (unsigned int)
SELECT * INTO ? FROM Access_Table1 -- gvNewTableName1 (string)
IF ? = 1 -- gvTable2Flag (unsigned int)
SELECT * INTO ? FROM Access_Table2 -- gvNewTableName2 (string)
Basically, I want to be able to choose which Access tables to copy to the Sql server database
and have the source tables copied to destination tables whose names are not constant.
I am getting an error
"Invalid Sql statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE'"
If I remove the If statements, I get the following error
"The SELECT statement includes a reserved word or an argument name, that is misspelled, or the punctuation is incorrect."
How can I get this to work?