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

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

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

Posted By:      Posted Date: September 24, 2010    Points: 0   Category :Sql Server

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

View Complete Post

More Related Resource Links

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

Data Flow Source.... Expressions/Variable

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

Can't make parameterized query work in data flow task


I have an OLE DB source in a data flow task.  The connection is pointing to a SQL Server 2008 instance.  My SQL works fine when I click the preview button.  Now I need to make it parameterized by adding a question mark in my where clause.  After doing so & clicking the Parameters button, I get the error described in this post .  When I try to do what's described therein to correct the problem (click the "build query" button), I get the error "The PIVOT SQL construct or statement is not supported."

I can't make this query an expression; it's simply too complex to maintain that way (besides, just pasting it into an expression builder window and trying to evaluate as is doesn't work - can't evaluate).

So the question is, how do I parameterize a query?  And why does SQL here work so radically differently from SQL in an execute SQL task?

How to set items from Select Command in Data Source to populate textboxes


I need to know how to get the  items from a Select Command in a Data Source to populate textboxes. For instance in the Select statement below I want to set the textbox for First Name equal to the item in the Select Command highlighted below:


 Protected Sub MHGrid_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles MHGrid.SelectedIndexChanged
        Me.Table1.Visible = True

        Me.txtFirstName.Text = (1)

    End Sub


Using CROSS APPLY in a data source query causes ARITHABORT error


I have an issue where the following query as an OLEDB data source throws the following error:

SELECT XmlNode.value('SomeValue[1]', 'varchar(10)') as SomeValue

FROM (SELECT CAST('<TestXML><SomeValue>123</SomeValue></TestXML>' AS XML

Query with parameters in data flow



How can I run a query with parameters in a data flow task? I know I can use a script component and write code that executes the query, but I'm looking for a simpler solution, if there's such one.



Select * query not returning all data in a row.


I am using SQL Server 2008 and am new to databases so this may be an obvious one to others.  I have setup a database and have 2 rows in a table which I am trying to read.  If I write a query in SQL Server it works fine, but the exact same query via ODBC connection only returns some of the of data in the affected rows.   The data not being returned is rather large (text field +1000 characters) but even if I create a row with just 1,2,3,4, (one in each column) it still only returns some of the data. 

SELECT * FROM [database].[dbo].[table] WHERE [station_name] = 'XXXXX'

Expected return XXXXX 1 3 5 10-11-2010  (where XXXXX is station name from above)

Actual return XXXXX (null) (null) 5 10-11-2010

The server the database resides on is administrated by IT so I have no idea what settings it has.  I had everything running in a MySQL database and my boss wanted it in MSSQL on the new server so I am trying to convert. This is the last thing that doesn't work.

problem with date range between select Query in data adapter


hi all,

can any one help me out...why am getting "data type mis match error"

 DateTime dFromDate = dt_From.Value;
            dt_From.Value = DateTime.Parse(dFromDate.ToShortDateString());
            string From = dFromDate.ToShortDateString();

            DateTime dToDate = dt_To.Value;
            dt_To.Value = DateTime.Parse(dToDate.ToShortDateString());
            string To = dToDate.ToShortDateString();
            OleDbConnection cn = new OleDbConnection(ConfigurationSettings.AppSettings["connec"]);
            OleDbDataAdapter da = new OleDbDataAdapter("select * from Route_Expenses a where Entry_Date between '" +From+"' and '"+To+"'",cn);
            DataSet ds = new DataSet();
            dataGridView1.DataSource = ds.Tables[0];

 Please..its Urgent!

Assign guid to variable in control flow


How do I assign a script task generated GUID to an ssis variable and then pass it to sql server via a stored proc? I've only been able to find info on how to do it in a data flow task (derived column)

There doesn't seem to be a GUID type in the Data Type drop down in the Variables tab

I get the following error whether I create the variable as an object or string & setting the data type of the variable to either object,guid or string in the parameter mapping tab of the execute sql task don't do much either

Error: 0xC002F210 at Log Fail, Execute SQL Task: Executing the query "EXEC dbo.spLogGenericExtractControlOnComplete
    ..." failed with the following error: "Conversion failed when converting from a character string to uniqueidentifier.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Log Fail


What magic combination of settings do I have to use to make it work?

error when testing query during configuring sql data source for gridview


I got the following error message:

There was an error executing the query. Please check the syntax of hte the command and if present, the types and values of the parameters and ensure they are correct.  Failed to enable constraints.  One or more rows contain values violating non-null, unique, or foreign-key constraints.

But when I run the query through Management Studio, I can get the result. 

There are no parameters for this query either. 

Can anybody give me a glue what to check?


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?


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,



Passing report parameters to a query using a WCF data source


Hello everyone,


I am attempting to pass report parameters to my query. My report's data source is a WCF web service. I can run normal queries fine, but I have found that without the know-how to pass parameters to my query I am limited with my capabilities.

Assume a string parameter named "Name" with a specified default value of "Jmachol90", how would I pass that into the following query at the designated place:




          <Method Namespace="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09" Name="SecurityPrincipalsGetRequest">


          <Parameter Name="Criteria" Type="XML">









Using a Query as a data source for a grid

Pardon the elementary nature of this question.  I have a SQL 2005 database with several queries.  I want to use the queries as data sources for grids in VS2008.  I connect the SQL database but cannot see the queries as a data source.  I have tried building the queries in code within VS but I still cannot use the query results as a grid data source.  Any Help would be greatly appreciated.

Data Mining Project in BIDS: Why cant data source views be based on a query?

I've created an analysis services project in BIDS with the intention of creating a mining strucuture. I notice that you cannot create a data source view based on a query and that it must be based on linking actual tables (via the access looking GUI).  In my scenario I have a very long and complicated query which uses temp tables to retreive the data I want to use for mining.   Am I forced to insert this data into a table in order to create and use a mining structure?  Or are there other ways to do it?

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