none
Using parameters in OLE DB Source of DataFlow task RRS feed

  • Question

  • Hi all,
    I'm using SSIS 2008 and I am puzzled by the following behavior:
    I have a table T1 with 1 column Nbr of type int.
    I have a SSIS parameter Nbr of type int.
    Now I want to insert the value of the SSIS parameter into T1 using a DataFlow task so I have a DataFlow task.
    In that DataFlow task, I have an OLEDB Source connector and an OLEDB Destination connector which of course points to T1.

    As for the Source, I tried:
    1) SELECT 'nbr' = ?
    And then map the parameters to User::Nbr
    **But I can't get to the parameters, I get an ERROR (cannot extract parameters...)

    So I tried creating a sp:
    CREATE PROCEDURE dbo.s_TEST_Return
    @nbr
    int
    AS
    SELECT
    'nbr' = @nbr

    And then use the sp in the DataSource:
    2) EXEC dbo.s_TEST_Return ?
    IT WORKS...

    SSIS Flaw or is it normal?

    Thanks
    Monday, July 6, 2009 3:29 PM

Answers

  • This is an issue with SSIS.
    Dont know y but yes it is there.
    In the MS website it explains how to use parameters in the OLEDB Source but as you said it does not work.
    Hope this helps !! - Sudeep | Please mark the post(s) as “Answered” that answers your query.
    • Marked as answer by fleo Monday, July 6, 2009 9:48 PM
    Monday, July 6, 2009 3:30 PM
  • I'd guess the OLE DB parser doesn't like the table function syntax. It's painfully picky.

    You could try using a string variable with an expression on it to assemble the SELECT statement - the expression can be used to insert the variable values. Then use the SQL Command From Variable option in the OLE DB Source.
    John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com
    Monday, July 6, 2009 7:40 PM
    Moderator

All replies

  • This is an issue with SSIS.
    Dont know y but yes it is there.
    In the MS website it explains how to use parameters in the OLEDB Source but as you said it does not work.
    Hope this helps !! - Sudeep | Please mark the post(s) as “Answered” that answers your query.
    • Marked as answer by fleo Monday, July 6, 2009 9:48 PM
    Monday, July 6, 2009 3:30 PM
  • Your approach is a little flawed here. If you want to write a single value to the database, particularly from a variable, you should use an Execute SQL Task, not a data flow. A data flow is designed to move many records between a source and destination. Normally, your source would be a SELECT statement that returns multiple rows.

    If you want to copy a variable's value into your data flow, you would use a Derived Column transform, but that still requires a source component, to provide rows for the data flow.

    You can use parameters in an OLE DB Source, but only in the WHERE clause of the SQL Statement.


    John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com
    Monday, July 6, 2009 5:19 PM
    Moderator
  • Thanks for your replies.

    The example was only created to illustrate that problem with SSIS.
    In the normal context I have to issue a SELECT with @StartDate/@EndDate.

    Thanks again.
    Monday, July 6, 2009 5:29 PM
  • You can use parameters in an OLE DB Source, but only in the WHERE clause of the SQL Statement.


    Hey I just re-read your reply...

    Not true, I just showed it in the example above.  Passing parameters in SQL Statement doesn't work at all (issue in SSIS as stated by Sudeep).
    Monday, July 6, 2009 5:32 PM
  • That's not correct, fleo. You can use Parameters in a OLE DB Source - I've done it on a number of occasions.

    For example:

    SELECT * FROM Person
    WHERE ID = ?

    works fine.

    Your example, though, does not work, because you are attempting to set a value from a parameter. If that example doesn't match what you are attempting to do, please post a more complete example.


    John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com
    • Proposed as answer by Tinkureddy Friday, May 4, 2012 2:49 PM
    Monday, July 6, 2009 5:50 PM
    Moderator
  • Hi jwelch!

    I've done it too in 2005, I thought it was with 2008, but you're right it seems to work fine...

    Maybe it has to do with the position of the parameters as you said.

    Precisely I am using a table function as Source:
    SELECT C1,...,Cn FROM dbo.F1 ('2008-01-01', '2009-01-01')
    *It works fine when I hard-code the parameters. 

    But I get an error as soon as I try to parametrize it:
    SELECT C1,...,Cn FROM dbo.F1 (?, ?)
    Monday, July 6, 2009 6:45 PM
  • I'd guess the OLE DB parser doesn't like the table function syntax. It's painfully picky.

    You could try using a string variable with an expression on it to assemble the SELECT statement - the expression can be used to insert the variable values. Then use the SQL Command From Variable option in the OLE DB Source.
    John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com
    Monday, July 6, 2009 7:40 PM
    Moderator
  • What I did is create a stored proc that calls the function and use that sp a the DataSource.

    I hate using a sp to return a dataset but well...

    Thanks

    Monday, July 6, 2009 9:48 PM
  • I do agree that creating an SP just for that is over killer, but I am not sure why you did not the other approach suggested by John. That approach, in my opinion, is far simpler/cleaner.
    Rafael Salas | Don’t forget to mark the post(s) that answered your question http://rafael-salas.blogspot.com/
    Monday, July 6, 2009 11:42 PM
    Moderator
  • I tried both approaches for fun...

    It took me more time doing it with the expression!  I have to say I made a couple of mistakes with the syntax, cast the variables...
    And then check the result of a 50-line command in a 2-line tall "Evaluated value" box...
    I copied it to UltraEdit at the end (BTW the CTRL+A shortcut is not implemented in those boxes).

    And when the SSIS bug is eventually fixed I simply have to paste the code from the sp and replace the variables with question marks.  No need to change anything else.

    So I'm not sure how you came to the conclusion that one approach is FAR more better than the other.  I think they're pretty equivalent.
    I guess I'm a SQL guy.

    Tuesday, July 7, 2009 2:01 PM