Using Table Variable in OLE DB Source RRS feed

  • Question




    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?




    Sunday, June 15, 2008 10:50 PM


All replies

  • 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.


    Monday, June 16, 2008 3:14 PM


    Here is a post showing how you can use an expression an a variable the way Hong is suggesting:



    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.

    Monday, June 16, 2008 3:24 PM
  • 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.

    Thursday, November 11, 2010 11:39 AM
  • 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.

    Friday, September 14, 2012 10:33 AM