Hi, I have used a Table Variable in an SQL script to retrieve data from a source databse using the OLE DB Source Data Flow tool. The OLE DB Destination recognises the table and allows the columns to be mapped, but when the SSIS package is run no data is returned to the destination table. The last line of the SQL Command in the OLE DB Source is : SELECT * FROM @Var The SQL code executes correctly in the SQL Management Studio. Have I run into a scope problem? Cheers Wee!

First I want to say, among"SELECT * FROM @Var" , @Var doesn't mean to variable in SSIS. It means variable in SQL Script. You need 2 variables. One you can name as @Var to present Table Name. The other you can name as @WholeSentence. You need to set @WholeSentence's expression as "SELECT * FROM " + @Var Then set the Ole DB Source's AccessMode="SQL Command in Variable" (I can't remember this name clearly, it should be this one or similar one). Set Ole DB Source's SqlCommandVariable=@WholeSentence. Then you will get the table's data via setting table name by variable.

Here is a post showing how you can use an expression an a variable the way Hong is suggesting: http://rafael-salas.blogspot.com/2007/11/ssis-mapping-parameter-inside-of.html BTW, the list of columns and their data types in the select clause must be always the same on each execution. The dataflow will error out if changes in the meta data are detected.

My requirement is that i m migrating the data in a table variable first and then migrating to final db , to check whether the values which are going to insert are already present in final db, So m using DataSource's Data Access Mode as "SQLCommand" in which i have written select statement into @Var and then select * from @Var where uniquecol not in (select uniquecol from finaldb) as u I told if use DataSource's AccessMode as "SQLCommand From Variable" , its not getting my @Var . Thus I am not able to do it ,I can I have more information on it? SSIS is totally new for me.

I came across this problem today but I solved it by writing a statement before my actual query. We have to add SET NOCOUNT ON. It worked for me. I learn one new thing today. Thank You All.