1

good morning.

We need to copy tables from Access to SQL. Thing is the table names in the source vary from day to day. I've followed the steps shown in the solution to this post How do I programmatically get the list of MS Access tables within an SSIS package?

The problem is this:

I changed variable names and that's all since the problem stated in that post is quite similar to mine. I followed the steps and changed Country to a Table Name of my Access DB, lets say CITY. Problem is when the process Loops the tables in Access, the data copied to the tables in SQL is always the same. The data stored in CITY, it seems it's not changing tables, it always use the table provided to the OLE DB source (as shown in screenshot 14#) (the table name provided in the variable 'SelectQuery')

So I have all the tables in SQL created but they are all filled with the same information. Info coming from the same table (the one that has its name stated in the variable)

Thanks, any advice?

Community
  • 1
  • 1
Daniel Sh.
  • 2,078
  • 5
  • 42
  • 67

2 Answers2

1

From reading the linked solution, it looks either incorrect, or missing one step. Right now, screenshot #14 specifies 'SQL Command from Variable', and 'SelectQuery' as the variable source; I can't see where SelectQuery is updated.

Solution 1: Set the OLE DB Source to "Table name or view name variable", and set the variable in question to TableName (which is updated each iteration by the ForEach container)

Solution 2: Change SelectQuery to be an expression-driven variable, i.e. "select * from " + @[User:TableName]

Geoff
  • 8,551
  • 1
  • 43
  • 50
0

you did realize that the table name on that example must match the sheet name on excel, right? So you probably have to do something like that on access.

Also, post more info. It seems that the value on your table variable is not being updated. Tell us how it should be.

Diego
  • 34,802
  • 21
  • 91
  • 134
  • Yes, the named variable (Im using City in this example) exists in Access. (if it doesnt it stops with an error in the data flow). The name of the table (which is held in the variable) is being updated OK. I check this by watching all the new tables in the SQL server. The tables are created meaning the variable for TableName is working. The problem is the information that fills those tables. That is always the same, and it's the data contained in the first table used (the one that is stated in the SelectQuery variable) – Daniel Sh. Apr 11 '12 at 15:45
  • Ill try to be more precise. I get all the table names stored in the Access DB. For doing so I use some C# code. I store it in an Object variable called AccessTables. For every table the process enters a For Each Loop. Inside there I need to create a replica of the table in a SQL DB. Copy everything, table name and content. For doing so I created a variable that contains the sentences 'SELECT * FROM CITY' (in the link, the name CITY is replaced by Country) So, in the source of the data flow I checked the 'SQL Command from variable' – Daniel Sh. Apr 11 '12 at 15:59
  • Part 2.... The tables are created, the names are all there. The problem is the content. All the tables are filled with the first used table, which is the table used in the SELECT variable. The looping variable works, the tables are created. But the problem is, I believe, in the Source of the data flow, it always copy the same table ("select * from [Variable Table Name]") – Daniel Sh. Apr 11 '12 at 15:59