.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Data Flow Source.... Expressions/Variable

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :
I am using the SSIS data source reader to connect to a MySQL database.

Am I able to use expressions with this Data Flow Source. I need to pass a variable to the SQL code?

I am using SQL 2005.

Mr Shaw

View Complete Post

More Related Resource Links

How To Map A GUID Variable And Use It In An OLE DB Source Data Flow Select Query


I have a simple SSIS package that does the following. 

On the control flow tab, there is an Execute SQL task that calls a stored procedure and returns a GUID. 

I have a package scoped variable that stores a UNIQUEIDENTIFIER output from the stored procedure. It appears that the package variable data type does not include a GUID data type so I tried to use string instead.

Next, the Execute SQL task is mapped to a data flow task. 
Within that data flow task I have an OLE DB Source which connects to a SQL Server Destination task.

On the OLE DB Source Editor screen I have a query that performs the following:

SELECT ID, SessionKey, ....
FROM dba_DroppedLoginsLog
WHERE SessionKey = ?

I have clicked on the parameters button to map the Parameter to my variable called User::SessionKey. This is the output variable from my Execute SQL Task component on the Control Flow tab. This value should be a GUID, but again, I don't see a data type for that.

When I execute the package it fails on the OLE DB Source with the following error:
[OLE DB Source [1908]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE D

SSIS Object model - dynamically identifying the source and destination tables of the data flow/ tran


Hello All,

I would like to identifying the source & destination tables of the data flow or the transformation task.

I need to create a custom component which i can put into existing packages which can do some validation.

Please suggest ways i can accomplish this.




How to update a variable in Data Flow component of lookup has rows that don't find matches



I'm using SSIS 2008

I have a data flow that gets data from an Excel spreadsheet. It then does numerous lookups to my database to ensure that all the entities in the spreadsheet are present in my dimension tables. For each of these lookups, I redirect rows with "no matching entries" to be written to another spreadsheet which is then sent to an email address for action.

I have a Package scoped variable that I want to update if any of the lookups have rows with no matching entries. This is used later in the package to determine if it should proceed or not. So this variable only needs to be updated once if any of the lookups fail. 


What are the options for doing this? What is the best way?


Craig Bryden - Please mark correct answers

Using the XML Source data flow object with one to many XML file


I am trying to use the SSIS XML Source object to insert data from an XML file into a relational database.  Within the file and database there are several one to many relationships.  For example, the Person table has a one to many relationship with the Phone table - one person can have multiple phone numbers. 

In my XML file, the PersonID is declared only once and that is with the person data.  I need the PersonID as a foreign key for the phone data.  The XML Source object displays multiple outputs for the XML file, including one each for Person and Phone.  However the Phone output does not include the PersonID which is required to insert the data into the Phone table. 

How can I get the PersonID to appear with the Phone data?  Is it a matter of modifying the XSD file, or is there method within SSIS where I can combine XML elements from different levels?


SQL Select With Variable Table - Using with ODBC Data Source


Hello all,

Having difficulty with an idea for a SQL query today.  I am working with a data set that points to an ODBC data source.  The data source has several TRANSACTIONmmyyyy tables (mm - month, yyyy - year ), each holding data for a particular month, going back several years. Each table has identical fields and data types.

If I were to run the query below for just one month ( say I took out mmyyyy and plugged in a month and year, 052010 ) it will pull totals for the AccountNum I specify.


mmyyyy.AcctNum, SUM
mmyyyy.Amount ) As
 "Current Month"

TRANSACTION mmyyyy.Region = TOTALS.Region AND
TRANSACTION mmyyyy.Region

DataReader Source Data Flow control not in the Toolbox of BIDS ???


I find this advice in the SSIS forum . . . but I can't find the DataReader Source in the Data Flow source items . . . nor do I seem to be able to 'Add Item' that time.

Advice appears to be I should use DataReader Source when pulling data from Oracle using ODBC e.g.:

"ODBC is supported in the data flow by the Data Reader source adapter. I think the original problem may have been that you were trying to use the Data Reader source with an ODBC connection manager.

To extract data from an ODBC source, try the following:

  • Add an ADO.Net Connection Manager.
  • Edit the Connection Manager editor and select the ODBC Data Provider
  • Configure the Connection Manager to use your DSN or connection string
  • Add a Data Flow Task to your package.
  • Add a Data Reader Source adapter to your data flow"

Any help getting the DataReader Source to appear in the BIDS toolbox appreciated.

Thank you and a Happy time with family tomorrow,



OLED DB data source not seeing variable with scope of containing sequence container

I'm using SQL Server 2008 R2. I have a sequence container in which an execute SQL task assigns a value to a variable, the last task in the sequence container is a data flow task with a single OLEDB source (SQL Server 2000) and destination. The source in the data flow uses the variable which has the scope of sequence container, however when the query in the data flow task source is executed, its as if its seeing a null value for the variable, can someone please advise as to what I'm doing wrong here. 

How to retrieve data from SqlDataSource and pass to a variable?


Hi there,

I have a page in my website where there is very little code-behind. I only have two lines of code in Page_Load() and the rest of the code is declarative ASP.NET code. I have a FormView control which uses a SqlDataSource to get some order information. The SqlDataSource calls a stored procedure called usp_GetOrderDetails, here's the code for the SqlDataSource:

    <asp:SqlDataSource ID="sdsFormOrderDetails" runat="server" 
ConnectionString="<%$ ConnectionStrings:ForexDB %>"
ProviderName="<%$ ConnectionStrings:ForexDB.ProviderName %>"
SelectCommand="usp_GetOrderDetails" SelectCommandType="StoredProcedure"
UpdateCommand="usp_UpdateOrder" UpdateCommandType="StoredProcedure"
<asp:ControlParameter Name="orderId" ControlID="grdOrders" PropertyName="SelectedDataKey.Value" />
<asp:ControlParameter Name="orderId" ControlID="frmOrderDetails" PropertyName="SelectedValue" />

Toolbox: Data Snapshots, Subversion, Source Code Organization and More


If you want to save, organize, and annotate snapshots of your database data, find an easy way to install and configure Subversion, and automate the organization of your source code, then you'll want to read more about these latest tools.

Scott Mitchell

MSDN Magazine August 2009

Dynamic WPF: Create Flexible UIs With Flow Documents And Data Binding


Flow documents offer enormous flexibility in arranging text layout and pagination, but they don't support data binding, so you can't dynamically change content. Here we build a component to solve that problem.

Vincent Van Den Berghe

MSDN Magazine April 2009

Data Points: Common Table Expressions


In T-SQL, views and derived tables serve similar purposes and have their advantages. But when working with SQL Server 2005,there's a third option--using Common Table Expressions (CTEs). John Papa explains.

John Papa

MSDN Magazine October 2007

SQL Server 2005: Regular Expressions Make Pattern Matching And Data Extraction Easier


Now you can perform efficient, sophisticated text analysis using regular expressions in SQL Server 2005.

David Banister

MSDN Magazine February 2007

Data Points: Column Expressions, DataRelations, and Computations


This month John Papa fields some of his favorite questions regarding data manipulation with ADO.NET.

John Papa

MSDN Magazine January 2007

Data Points: Data Source Controls in ASP.NET 2.0


ASP. NET 2. 0 introduces a series of new tools that improve data access including several data source and data bound controls. The new assortment of data source controls can eliminate a ton of repetitive code that was required in ASP.

John Papa

MSDN Magazine January 2005

Data Points: Expressions in ADO.NET


Managing relations in a database became a whole lot easier with ADO. NET. Instead of returning a single rowset to a data store, you can return a series of rowsets and relate them within a DataSet. There are numerous advantages to using related DataTables in ADO.

John Papa

MSDN Magazine January 2003

Empty Website Data Source windown


Hi all,

I am Visual Web Developer user and facing a problem when I am creating a local report (.rdlc) file.

I am using Business objects as the Data Source for this report. It was working perfectly until all the items in the Website Data Source window suddenly "disappeared". The "Add New Data Source" button on this window is also disabled. Now the window is just like a blank box only. And I can't continue working with the report file like this...

I have tried undoing what I did before but it does not help at all...

Any reason for this to happen? There is no sign or something at least indicating what is wrong.

Please help.


 ps: found a similar thread in this forum but not solved

The report cannot be opened because the data source associated with it is not a report model.


i tried to open a report i created in sql business intelligence development studio in the report builder in wss3 and it gave me this error:

Microsoft.ReportingServices.ReportBuilder.ReportModel.Report: The report 'http://servername:port#/Reports/report.rdl' cannot be opened because the data source 'http://servername:port#/DataSource Library/datasource.rsds' associated with it is not a report model.

This report was not created in Report Builder. Try opening this report in Report Designer instead.

can i fix this so i can open my reports in report builder?

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend