Can you use a SSIS Variable in an OLE DB Source SQL Statement?

Posted on 2009-04-17
Last Modified: 2013-11-10

The question kind of says it all, but can I use the syntax User::Variable in a SQL Statement?

Select Customer, NetSales, SurrogateValue + User::Variable
From tblSales

Question by:ToddFields2
    LVL 57

    Expert Comment

    by:Raja Jegan R
    LVL 17

    Accepted Solution

    You have to use the varible in another Variable. LOL Sounds silly, but it is true. You create a variable to contain the entire SQL Statement. Then you can set the EvaluateAsExpression property of the SQL Statement Variable to True. In the Expression you build the SQL Statement and incorporate yor variable.

    Author Comment


    Sorry for the long delay on this thread, but just getting back to trying this.  I setup a variable with a select statement pictured below, but not sure how to do the second piece of this.  Are you suggesting a SQL task or can I reference this within a OLE DB Source - SQL Qualifier?

    Select RowGUID + User ::MaxSHSurrogate as NewRowGUID, CustomerNumber, NetSales from


    Author Comment

    This is too cumbersome to use.  Everything was switched into a stored procedure.  The benefit of putting it in a stored procedure is that you don't need to rebuild your SSIS package if you have a modification.

    Author Closing Comment

    This will work, but my connection to AS400 for standard edition doesn't support it.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now