Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2009-04-17
5
Medium Priority
?
987 Views
Last Modified: 2013-11-10
Hi,

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

Thanks,
Todd
0
Comment
Question by:ToddFields2
  • 3
5 Comments
 
LVL 17

Accepted Solution

by:
HoggZilla earned 1500 total points
ID: 24172956
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.
0
 

Author Comment

by:ToddFields2
ID: 24283132
Hi,

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?

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





Variable-Select.jpg
0
 

Author Comment

by:ToddFields2
ID: 24377456
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.
0
 

Author Closing Comment

by:ToddFields2
ID: 31597490
This will work, but my connection to AS400 for standard edition doesn't support it.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

581 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