locked
OLE DB Command Dynamic Column Name - SSIS RRS feed

  • Question

  • Hi, this is my first post here . Sorry my bad english ...

     I have a table with this columns: column1,column2,column3 

    A need update in SSIS using OLE DB Command Tranformation, but the update column depends of the variable, that can have  either the values 1 or 2 or 3 ... ( it is INT data type ... )

    I have create Stored Procedure for update :

     

    CREATE PROCEDURE prcDoUpdate
    (
    	 @ColumnValue tinyint -- 1 or 2 or 3
    )
    AS
    
    DECLARE
    	@SQL nvarchar(500);
    
    SET
    	@SQL = N'
    	UPDATE
    		MyTable
    	SET
    		column'+CAST(@ColumnValue AS nvarchar(1))+' = getDate()
    	'
    ;
    
    EXEC sp_executeSQL @SQL
    

     

    In the OLE DB Command  configure :

    SqlCommand = prcDoUpdate ?

    Then this error is returned, when i go to mapping columns:

    [...] "Invalid Column name column0" [...]

    I cannot change the table structure ...

    Seem that OLE DB Command transalate the dynamic query without passing parameter and dont find the column ( The cast result in zero ... )

    How fix this ? Somebody help me ???

    Thanks ...

    Friday, January 21, 2011 3:19 PM

Answers

  • Thanks BrianTrom and Todd McDermid.

    When you posted this help, i had already found the solution !!!

    My solution were to use Property Expressions !!!!! 

    I dont knew that Property Expressions of the Data Flow objects was configured in Data Flow Container ... :( .. 

    Here is :

     OLE DB Command Transformation Property:

    SqlCommand = UPDATE myTable SET column1 = getDate()

    I make this for dont generated errors in Design Mode ...

    And The Property Expresssion :

    [My_OLEDB_COMAND_TRANSFORMATION].[SqlCommand] = "UPDATE myTable SET column"+((DT_STR,10,1252) @[User::ColumnValue] )+" = getDate() "

     

    But, much very thanks !!!!!! 

     


    []s Rodrigo Ribeiro Gomes
    • Marked as answer by RodrigoRRG Friday, January 21, 2011 7:42 PM
    Friday, January 21, 2011 7:42 PM
  • I think it's just your properties in the OLE DB Command component. In the Input and Output properties tab, go to the OLE DB Command Input section of the Inputs and Outputs box. Expand it, and then expand External Columns. In here is where you'll need to have your Destination columns defined, by name and datatype, so that you can map those columns to the Input columns.

    Please browse the properties and get back with us.

    Thank you.


    Brian
    • Marked as answer by RodrigoRRG Friday, January 21, 2011 7:43 PM
    Friday, January 21, 2011 3:38 PM
  • It's probably better for you to use a Conditional Split to split out your rows into the three different updates that need to happen, and then pass each condition to its own OLE DB Command component.

    You'll get better throughput, as those three OLE DB Commands will operate (mostly) in parallel, and the construction of your SQL statement and configuration of each command will be very simple.


    Todd McDermid's Blog Talk to me now on
    • Marked as answer by RodrigoRRG Friday, January 21, 2011 7:43 PM
    Friday, January 21, 2011 6:50 PM

All replies

  • I think it's just your properties in the OLE DB Command component. In the Input and Output properties tab, go to the OLE DB Command Input section of the Inputs and Outputs box. Expand it, and then expand External Columns. In here is where you'll need to have your Destination columns defined, by name and datatype, so that you can map those columns to the Input columns.

    Please browse the properties and get back with us.

    Thank you.


    Brian
    • Marked as answer by RodrigoRRG Friday, January 21, 2011 7:43 PM
    Friday, January 21, 2011 3:38 PM
  • It's probably better for you to use a Conditional Split to split out your rows into the three different updates that need to happen, and then pass each condition to its own OLE DB Command component.

    You'll get better throughput, as those three OLE DB Commands will operate (mostly) in parallel, and the construction of your SQL statement and configuration of each command will be very simple.


    Todd McDermid's Blog Talk to me now on
    • Marked as answer by RodrigoRRG Friday, January 21, 2011 7:43 PM
    Friday, January 21, 2011 6:50 PM
  • Thanks BrianTrom and Todd McDermid.

    When you posted this help, i had already found the solution !!!

    My solution were to use Property Expressions !!!!! 

    I dont knew that Property Expressions of the Data Flow objects was configured in Data Flow Container ... :( .. 

    Here is :

     OLE DB Command Transformation Property:

    SqlCommand = UPDATE myTable SET column1 = getDate()

    I make this for dont generated errors in Design Mode ...

    And The Property Expresssion :

    [My_OLEDB_COMAND_TRANSFORMATION].[SqlCommand] = "UPDATE myTable SET column"+((DT_STR,10,1252) @[User::ColumnValue] )+" = getDate() "

     

    But, much very thanks !!!!!! 

     


    []s Rodrigo Ribeiro Gomes
    • Marked as answer by RodrigoRRG Friday, January 21, 2011 7:42 PM
    Friday, January 21, 2011 7:42 PM