none
Is "Insert into table(param1,param2) Select ?,X from table2" syntax not supported in SSIS OleDBCommand Transformation

    Question

  • Hi,

    I'm using the below SQL statement in a OLEDB command transformation. 

    Syntax 1: 

    INSERT INTO dbo.Table1 (col1,col2) 
    SELECT ?, 22 FROM dbo.Table1

    But, it fails with an error "syntax error , permission violation or other nonspecific error" . However , 

    Syntax 2:

    INSERT INTO dbo.Table1 (col1,col2) 
    values (?,?) works perfectly .

    Is Syntax 1 not supported by SSIS.

    Thanks

    Girija

    Friday, October 11, 2013 10:30 AM

Answers

  • I can duplicate your issue.  There is a funky issue with OLE DB Command.   When I write the query with the Values statement commented out, the Syntax 1 query as you have written it does work.  When I remove the commented out statement it does not work.  It seems to want the word Values() in there, regardless of whether it is commented out.

    Try this and see if you get the same behaviour. If so, this should work successfully for you.

    INSERT INTO dbo.Table1 (col1,col2) 
    --Values()
    SELECT ?, 22 FROM dbo.Table1


    Martina White


    Tuesday, October 15, 2013 8:01 PM

All replies

  • I fear its not supported.

    How about loading the data into a temp table, and then do a SQL Task with the statement you have mentioned?


    A.m.a.L Hashim
    Microsoft Most Valuable Professional
    My Blog - Dot Net Goodies

    Friday, October 11, 2013 10:37 AM
  • This is what I did to make it work.

    Replaced
    INSERT INTO dbo.AS_TYPE (AS_TYPE_NAME,AS_TYPE_ID) SELECT ?,MAX(AS_TYPE_ID) + 1 FROM AS_TYPE" 

    with

    "Declare @as_type_id int; 
    SELECT @as_type_id = MAX(As_type_ID) + 1 FROM dbo.AS_TYPE; 
    INSERT INTO dbo.AS_TYPE (AS_TYPE_NAME,AS_TYPE_ID) values (?,@as_type_id)." 

    It works, however would like to have your feedback if the approach is valid.

    I'm basically trying to push the output of a lookup transformation into a destination table & that output is the input parameter to the select statement.


    Saturday, October 12, 2013 8:10 AM
  • I can duplicate your issue.  There is a funky issue with OLE DB Command.   When I write the query with the Values statement commented out, the Syntax 1 query as you have written it does work.  When I remove the commented out statement it does not work.  It seems to want the word Values() in there, regardless of whether it is commented out.

    Try this and see if you get the same behaviour. If so, this should work successfully for you.

    INSERT INTO dbo.Table1 (col1,col2) 
    --Values()
    SELECT ?, 22 FROM dbo.Table1


    Martina White


    Tuesday, October 15, 2013 8:01 PM
  • Thanks Martina. After applying the hack ,it did work :). How did you figure that out.
    Wednesday, October 16, 2013 7:58 AM
  • Purely by accident.  Was trying different SQL statements to see where the problem lies, and commenting out pieces of it while testing.  Just noticed that this worked consistently.  :)

    I'd appreciate it if you'd mark my reply as the answer. 


    Martina White

    Wednesday, October 16, 2013 2:09 PM