locked
Package with DataSource as T-Sql statement Table Variable RRS feed

  • Question

  • Hi,

    I have Put a Sql statement which puts the result into a Table Variable.I get the output when i run in in Management Studio. When I put the same sql in OLEDB SOURCE i can see the preview of data.

    But when i run the package to put those results in to a destination table,it runs fine but no rows are moved.

    Can any one explain why this happens??

    Can I not use the results coming from sql statement with table variables?? 
    Tuesday, November 17, 2009 8:08 PM

Answers

  • Never Mind it worked when i put SET NOCOUNT ON before my sql script !!
    • Marked as answer by johnwelch Wednesday, November 18, 2009 3:10 PM
    Tuesday, November 17, 2009 8:30 PM

All replies

  • Never Mind it worked when i put SET NOCOUNT ON before my sql script !!
    • Marked as answer by johnwelch Wednesday, November 18, 2009 3:10 PM
    Tuesday, November 17, 2009 8:30 PM
  • Can you post a sample of the SQL statement you are using? If you are using SELECT ... INTO table_var, that won't work. Just run a normal SELECT without the INTO.

    If you are selecting from a table variable, that should work, as long as the table variable is defined correctly and in the proper location.
    John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com
    Tuesday, November 17, 2009 8:33 PM
  • My sql looks like:

    set nocount on
    declare @t table(col1,col2)      --declaring table variable
    insert into @t                           --inserting into the table variable with select statement
    select col1,col2
    from TableA

    select * from @t  --finally selecting them
    Tuesday, November 17, 2009 9:37 PM
  • My sql looks like:

    set nocount on
    declare @t table(col1,col2)      --declaring table variable
    insert into @t                           --inserting into the table variable with select statement
    select col1,col2
    from TableA

    select * from @t  --finally selecting them

    Above code (after adding data types in declare @t table statement) will work fine. Are you getting any error?
    Set No Count On should be used.
    Nitesh Rai- Please mark the post as answered if it answers your question
    • Proposed as answer by Murty Addanki Wednesday, November 18, 2009 5:46 AM
    Wednesday, November 18, 2009 12:11 AM
  • First,
    As nitesh pointed, provide proper datatype in the @t(table variable) declaration,
    Syntax would be like this: Declare @Table_Name Table(ColumnName1 Datatype,ColumnName2 DataType,...ColumnNamen DataType)


    Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.
    Wednesday, November 18, 2009 4:30 AM