.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

OLE DB Destination SQL command - how can I select from recordset variable?

Posted By:      Posted Date: October 13, 2010    Points: 0   Category :Sql Server

How do I insert records from connection1 into a a table with the same name on connection2 where the records in connection2 do not already exist?

The approach I'm trying is this: I have an OLE DB Source that selects records from connection1 (successfully). The flow into a recordset destination in the Data Flow. But I can't figure out how to select from this recordset using the FROM CLAUSE of a SQL Command in the OLE DB Destination component. Can someone point me to an example of this?

It seems like this would be a common task but I can't find any examples.

View Complete Post

More Related Resource Links

Using a Variable in SSIS - Error - "Command text was not set for the command object.".

Hi All, i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables, enterName - String packageLevel (will store the name I enter) myVar - String packageLevel. (to store the query) I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName =  " + @[User::enterName] + " )" Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.   Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C.An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E0C  Description: "Command text was not set for the command object.". Can Someone guide me whr am going wrong? myVar variable, i have set the ExecuteAsExpression  Property to true too. Please let me know where am going wrong? Thanks in advance.

parse the Select command in C#

Please i need to find all words starts with '@'  such as input parameter in sql query  ex:(select * from table where date=@enddate)ineed to get the enddate to view it in messageboxAlaa

Using SELECT with LIKE % surrounding a variable

Hi  Can anyone give me some basic help please. I'm just trying to learn to write to SQL to make my job easier. I've started using variables but the SELECT statement does not work properly when I use it with LIKE and try to surround my variable with % wildcards. Can anyone tell me the correct syntax please :-   DECLARE @location varchar SET @location='L'   SELECT DESCRIPTION,ID,* FROM LOCATION WHERE UPPER(DESCRIPTION) LIKE ('%' + @location + '%')  Many thanks Matthew

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


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

Datagrid error Select Command


I am getting with this code so I assume what I am doing won't work

Here is the Code from the datagrid

SelectCommand="SELECT * FROM [location]WHERE LocationID = <%# getUserLocations.ToString() %> ORDER BY LocationName"></asp:SqlDataSource>

It is obvious what I am trying to do. Apprciate any suggestions



Select only works with datetime variable, not table



I am a learning-as-I-go person without formal training.  I have been working with Access databases for about 10 years, and have begun working with SQL in the last 5 years or so.  The system in user here is a 2K3 server (8xXeon 2.8Ghz, 12GB RAM, 1TB HDD) running SQL Server 2005 (32bit).  I am trying to develop a SQL query (view) which looks for values of one table' field at specific times based on a table of those times.  When I use a datetime variable, that particular value at that particular datetime will be returned.  When I try to replace the variable with the datetime result of a second query(view), no data is returned.  The code is below.

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '2010-09-30 04:59:00'		-- Need to replace this value with the the list of 'PriVolCk' datetimes

Execute DOS Command with Variable



I am using 2 task in control flow.

1st task is a "execute SQL task" which returning from DB into a variable called VAR1(i.e. VAR1 = C:\test_data\)

2nd task is "execute process task" where I wnat to delete all the files from the directory (i.e. value got in VAR1)

Pls help.

Many Thanks,





Gridview - Update with Dynamic SELECT Command?


Hi All...

We created a GridView-based webform with Update using the design wizards - very nice and it works great.  But then we wanted to the ability to create the SELECT command dynamically based on other controls on the page.  We added something like the following to Page_Load()...

            SqlDataSource1.SelectCommand = "SELECT [StationId], [IP] FROM [Stations]";

The GridView populates normally, but the Update no longer works.  It changes the "Edit" link to "Update", gives the user the edit boxes, but when "Update" is clicked it never seems to write the new data to the database.  No error - it just doesnt work...

Any thoughts?  How can we change the SELECT command (and Connection String) dymamically and still retain the Update functionality?

Many thanks - Curt.  Code follows...

    protected void Page_Load(object sender, EventArgs e)
        //  Doesn't work with or without this conditional
        //if (!IsCallback)
            SqlDataSource1.SelectCommand = "SELECT [StationId], [IP] FROM [Stations]";

Recordset Destination Component Error in SQL Agent


I am using the Recordset Destination Component to populate an Object Variable, and then using a For Each Component to loop over that Variable.  The package executes perfectly if I run it from within BIDS, however, when I execute as a Job Step within the SQL Server Agent, I get the following error:

Description: Component "component "Recordset Destination" (34)" could not be created and returned error code 0x80070005. Make sure that the component is registered correctly.  Code: 0xC0048021  Recordset Destination [34]     Description: The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "Recordset Destination;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0"  Code: 0xC0047017

I have tried executing in the Job Step every way I can think of...as command line from file system, from within SQL Server, as 32-bit, 64-bit, Windows Auth, SQL Server Auth...

Any Ideas?



Microsoft Visual Studio 2005
Version 8.0.50727.42  (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727 SP1

Installed Edition: IDE Standard

Select Top @variable


I want to do something like this: SELECT TOP @variable.
I cannot use stored procedure for this report, 'cause I do not have access to prod db, I'm just sending rdl for upload. How to make it work on dataset level or through the report layout?

I know SQLServer doesn't recognize "Select Top @variable" statement. 
SELECT    top &variable, Ticket.problem AS Issue, COUNT(Solutions.solution_date) AS [Tickets Touched]
FROM         Ticket INNER JOIN
                      Solutions ON Ticket.ticket_number = Solutions.ticket_number
WHERE     GROUP BY Ticket.problem


SELECT     Ticket.problem AS Issue, COUNT(Solutions.solution_date) AS [Tickets Touched]
FROM         Ticket INNER JOIN
                      Solutions ON Ticket.ticket_number = Solutions.ticket_number
WHERE     GROUP BY Ticket.problem
HAVING      (COUNT(Solutions.solution_date) > CAST(@num AS int))

Differences Between OLE DB Destination And OLE DB Command w/ parameterized input statement

Hey, I am using an OLE DB Destination to enter data into an a400 table and I was getting very slow performance (fastload in not available for this provider). It was doing about 140 rows per minute. In testing i discovered that if i used an ole db command with a parameterized input statement (insert into library.table(blah, blah blah) values(?, ?, ?)) the performance increased dramatically. To about 1400 rows per minute. My question is, does anyone know what else the ole db destination is doing that could cause this slow down? Does the destination verify that the row has been inserted afterwords and maybe that could explain why it is so much slower? If so it may be safe to use the ole db command if i simply verify table count after completing the insert to make sure that all of the rows were entered. Or is there something else that the ole db destination is doing to where it may not be safe to do an ole db command in this way?

Sharepoint SPDatasource select command where clause not working for some columns



       I'm using MOSS 2007. I'm using spdatasource to bind a custom list to gridview using webuser control(webpart). spdatasource select command working for all columns except the status column. my status column is a choice- radio with the following options. Pending Review, Checked and Approved. I have checked the field internal name, every thing is correct, but only for status column filtering not happening. Gridview coming as blank

i tried using 

             <FieldRef Name='Status'/>
             <Value Type='Choice'>Pending Review</Value>

Any idea?????




SP Datasource select Command not working for Status Column



    (MOSS 2007-Webuser control Web part)

    I'm having a cutom list that i'm binding using SPDatasource to a gridview. I want to filter the list based on column values. I tried the following code

            SPList taskList = site.Lists["Accounts"];
            SPDataSource mySPDS = new SPDataSource();

         mySPDS.SelectCommand = "<View><Query><Where><Eq><FieldRef Name='Vendor_x0020_Name'/><Value Type='Text'>"+ddlVendorName.SelectedItem.Text +"</Value></Eq></Where></Query></View>";


mySPDS.SelectCommand = "<View><Query><Where><Eq><FieldRef Name='Status'/><Value Type='Choice'>" + ddlStatus.SelectedItem.Text + "</Value></Eq></Where></Query></Vi

Command="Select" in GridView how to change color with css


How to change the selected row in GridView? Currently displayed yellow
I want to change the CSS color of selected row in the GridView.

I Command = "Select" when choosing a row, is shown in yellow.
I want to change that color.
What do I do?

Copying rows using a dynamic SQL Command variable


I have a table with the Source TableName and Source ColumnName. I am trying to use SSIS to extract data from my source based on the tables and columns. I am using a Script Task to dynamically create the SELECT statement, no problems with that.

Next, I have a data flow task with a Source OLE DB Connection and the Data Access mode is using this dynamically created select statement. I am however unable to use this because this SELECT statement variable is not populated until runtime.

Basically I want to COPY ALL ROWS from the source to a destination. 

Is this possible? What is wrong with my assumptions? Any suggestions?


You are so wise...like a miniature budha covered in fur. -Anchorman

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
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