Home > SSIS Best Practices > SSIS – Pass a variable to a OLE DB Source in a Data Flow

SSIS – Pass a variable to a OLE DB Source in a Data Flow

Continue from SSIS – Use “Foreach Loop Container” and pass a variable to a OLE DB Source in a Data Flow.

Step 2: Create a data flow step, into which we are going to pass a variable

image

Step 3: Create an OLE DB Source, Destination and a Data Conversion

image

Step 4: In the OLE DB Source Editor, write a SQL query.

Notice the WHERE clause, WHERE    M.TIME_DAY_GEN_ID = cast( ? as date ).

The question mark ? indicates that we need a parameter for this query.

Attention: when writing SQL query in this editor, always start with a simple query. The editor is not very sophisticated SQL editor. Complex SQL queries will confuse the editor.

image

Step 5: Click the Parameters… button.

In the Set Query Parameters windows, add a parameter by setting Parameter0 under Parameters, and User::vLoopDate under Variables.

Continue to finish the Column mappings.

image

We are done now.

If you open the Advance Editor for the OLE DB Source, you will see this in the Component properties tab.

Notice the ParameterMapping and the SQLCommand properties.

image

Categories: SSIS Best Practices
  1. Greg Kern
    August 26, 2011 at 8:04 pm

    Perfect, just what I neeeded.

    Like

  2. Siphiwe Khuzwayo
    September 29, 2011 at 10:36 am

    Hi,

    How would I go about passing a variable to an ADO.Net Source, for example Teradata. I would like to query teradata using a variable to return a specified query result.

    Been struggling to get this work, help would be appreciated.

    Like

  3. Sherry
    September 30, 2011 at 11:28 pm

    Hi,

    I’ve answered your question in a new blog,

    SSIS #101 – Use expressions to pass parameters to ADO.NET Source

    Hope it helps.

    Sherry

    Like

  4. Megha
    January 27, 2012 at 5:28 pm

    getting below error,
    ……where s.iso_country = cast( ? as varchar)

    Any sugesstions?

    TITLE: Microsoft Visual Studio
    ——————————

    Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the “SQL command from variable” access mode, in which the entire SQL command is stored in a variable.

    ——————————
    ADDITIONAL INFORMATION:

    Syntax error, permission violation, or other nonspecific error (Microsoft SQL Server Native Client 10.0)

    ——————————
    BUTTONS:

    OK
    ——————————

    Like

  5. Sherry Li
  6. SteveA
    April 23, 2012 at 3:03 am

    Hi
    Just a comment on passing a date variable from SSIS to SQL if your local time is not MM/DD/YY eg UK, Australia, etc.
    I found that when entering the date 01-07-2005 (1 july 2005) as a variable to be passed to SQL SSIS will display the varible as 01-07-2005 BUT pass the date to SQL as 07-01-2005 (7 January 2005).
    By entering the variable as 2005-07-01 the variable is passed correctly.
    Weirdly when you look at this date in the SSIS variable it is displayed as 01-07-2005, exactly the same as the “incorrect” date was displayed.

    Like

  7. Roma
    June 20, 2012 at 3:17 am

    HI

    I am a newbie to SSIS.. it is good article. but my User variable is not passing from Control Flow to data flow . It is giving following error ..

    Error: Variable “User::XXXX” does not contain a valid data object

    Am I doing something wrong or am missing something?

    Thanks in advance!!

    Roma

    Like

  8. jose
    July 18, 2012 at 11:53 pm

    I have implemented this on my data flow using an OLE DB Source.
    On the OLE DB Source, I used the SQL Command then on the SQL command text, I ahve this
    EXEC [dbo].[mystoredproc_queryperson] ?
    where ? is an input parameter @PersonID

    On the Parameters tab (set Query Parameters, I changed the named of the Parameter0 as @PersonID then create a user variable: User::PersonID

    When I run this using debug mode, everything is working fine. But when I run it using a stored procedure with xp_cmdshell dtexec command, it is not recognizing the input parameter.
    e.g:
    DTEXEC /File “E:\MySSISFolder\MyPackage.dtsx” /Set \Package.Variables[User::PersonID].Properties[Value];”1″

    where 1 is the PersonID

    Am I missing anyting?

    Jose

    Like

  9. Roma
    July 23, 2012 at 6:10 am

    Hi

    Please try

    EXEC [dbo].[mystoredproc_queryperson]
    @PersonID = ?

    And In Parameters tab Set

    Parameters -> @PersonID
    Variables-> User::PersonID

    Hope this helps you !!

    Thanks
    Roma

    Like

  10. marco
    December 17, 2012 at 9:16 am

    Hi, that’s fine when your are usign a simple condition ie select * from xxx where cond=?. But it doesn’t work when you’re using an inner nest or a more difficult condition. Ie select * from xxx1 inner join (select max(i) from xxx where cond>?) as u on u.i=xx1.i. Can I manage this with ole? It works only with an .net connection! Thank you

    Like

  11. hui
    January 10, 2013 at 11:53 pm

    HI sherry, what if I want to pass a variable into the select statement instead of the Where clause? Seems your approach only works if the paramenters are on the where clause.

    thanks

    Like

    • Patrick
      November 2, 2013 at 1:05 am

      I hope your question has already been answered, but in case someone else has the same question…

      Passing a variable into the SELECT statement is not possible. I would recommend using a ‘Derived Column’ task to pass variables as columns. This will have the same effect of using a variable in the SELECT list.

      Like

  12. Abed
    May 23, 2013 at 10:56 am

    hey i have an ole db component thats calling a stored procedure that has the output parameter as of type nvarchar(max)

    it returns an xml value

    i have been trying to map it to a DT_NTEX column in the ole db comand but it never returns a value

    i tried with nvarchar(50) and that worked so im assuming its the DT_NTEX issue

    (cant really use nvarchar(50) since its an xml column and will get concatenated)

    is there a way to solve this ?

    appreciate any help

    Like

  13. February 23, 2015 at 3:23 pm

    “Attention: when writing SQL query in this editor, always start with a simple query. The editor is not very sophisticated SQL editor. Complex SQL queries will confuse the editor.”

    That’s exactly what happened to me. The editor handled a simple query, but with my initial query, it didn’t want to cooperate with the variables 🙂
    Funny and annoying bug.

    Like

  1. August 27, 2011 at 3:24 am
  2. November 25, 2014 at 3:08 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: