I may be attempting to do the impossible with this approach but doing it this way if possible would be preferable. If it is not possible, any suggestions on alternative ideas would be greatly appreciated.
Using BIDS 2005, I am attempting to build an SSIS package that copies contents of a table from one database based on the table name to select from being identified from table data in a different database (simply expressed, if this table is missing from database 1, import it from database 2).
I have two OLE DB source Data Flow objects. They are connected to different databases on different servers. One of the databases is Sybase ASE 12.5 and the other is SQL Server 2000.
What I need to do is query tables in the SQL Server database to determine the name of a table that needs to be imported from the Sybase database. I want to use variables to make the process as flexible as possible so that later on it can be modified to loop through and import data from multiple tables.
On the SQL Server side, I have a variable (var1) based on an SQL query that correctly returns the name of the table that needs to be imported. When I attempt to use that variable (var1) as the where clause in the SQL statement of var2, I get syntax error messages from the Sybase OLE provider. Hard coding the name of the table (bypassing the use of var1) to do the import works fine.
Both variables are in the scope of the package.
I do not have the ability to create a second database in SQL Server for testing to use to eliminate Sybase from the equation. On the Sybase server, I do not have administrative access but rather permissions to an alias database setup by the vendor. Since I cannot connect directly to the Sybase instance, I have no means to simply copy the entire database containing the tables where the required data resides.
Expert help would be greatly appreciated!