Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009

    Unanswered: SSIS parameters for execute sql task and oracle

    I am trying to pass parameters to in the Execute SQL Task for an Oracle query. I am running a query like below on a Oracle server through SSIS. However, when I create the parameter for the drugname, nothing gets inserted into the temptable. The Job does complete successfully. The variable is of type string and the paramater is of type varchar. So I am not sure why it isn't working. Any ideas? BTW, I can get this to work fine when I do an integer for a field as a parameter. Its just the drugname that is giving me grief. I am running SS 2005 btw. Thanks.

    insert into tempTable
    select *
    from table t
    join table2 t2 on
    t2.drugid = t.drugid
    where = to_date('05-01-2010', 'YYYY-MM-DD')
    and t2.drugname = ?

  2. #2
    Join Date
    Jul 2003
    San Antonio, TX
    Use Expressions in the Execute SQL Task, and form your statement there using the parameter.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    May 2009
    I actually figured out what was going on. Some invisible character was getting tacked onto the end of the variable, probably a line feed. I could fix it by doing the substr function and removing the last character. But I found an even better way. I switched to using an ODBC connection, instead of a OLE DB. And I had to set the variable to WVARCHAR, since WVARCHAR is Unicode, and that is what the Oracle server is set to.

    Thanks for the reply though.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts