The Execute SQL Task

by Allan Mitchell 9 Oct 2005 14:00

In this article we are going to take you through the Execute SQL Task in SQL Server Integration Services for SQL Server 2005 (although it appies just as well to SQL Server 2008).  We will be covering all the essentials that you will need to know to effectively use this task and make it as flexible as possible. The things we will be looking at are as follows:

  • A tour of the Task.
  • The properties of the Task.

After looking at these introductory topics we will then get into some examples. The examples will show different types of usage for the task:

  • Returning a single value from a SQL query with two input parameters.
  • Returning a rowset from a SQL query.
  • Executing a stored procedure and retrieveing a rowset, a return value, an output parameter value and passing in an input parameter.
  • Passing in the SQL Statement from a variable.
  • Passing in the SQL Statement from a file.

Tour Of The Task

Before we can start to use the Execute SQL Task in our packages we are going to need to locate it in the toolbox. Let's do that now. Whilst in the Control Flow section of the package expand your toolbox and locate the Execute SQL Task. Below is how we found ours.

Find

Now drag the task onto the designer. As you can see from the following image we have a validation error appear telling us that no connection manager has been assigned to the task. This can be easily remedied by creating a connection manager. There are certain types of connection manager that are compatable with this task so we cannot just create any connection manager and these are detailed in a few graphics time.

Drop

Double click on the task itself to take a look at the custom user interface provided to us for this task. The task will open on the general tab as shown below. Take a bit of time to have a look around here as throughout this article we will be revisting this page many times.

General

Whilst on the general tab, drop down the combobox next to the ConnectionType property. In here you will see the types of connection manager which this task will accept.

ConnectionTypes

As with SQL Server 2000 DTS, SSIS allows you to output values from this task in a number of formats. Have a look at the combobox next to the Resultset property. The major difference here is the ability to output into XML.

ResultsetTypes

If you drop down the combobox next to the SQLSourceType property you will see the ways in which you can pass a SQL Statement into the task itself. We will have examples of each of these later on but certainly when we saw these for the first time we were very excited.

SourceType

Next to the SQLStatement property if you click in the empty box next to it you will see ellipses appear. Click on them and you will see the very basic query editor that becomes available to you.

QueryEditor

Alternatively after you have specified a connection manager for the task you can click on the Build Query button to bring up a completely different query editor. This is slightly inconsistent.

AlternateEditor

Once you've finished looking around the general tab, move on to the next tab which is the parameter mapping tab. We shall, again, be visiting this tab throughout the article but to give you an initial heads up this is where you define the input, output and return values from your task. Note this is not where you specify the resultset.

ParamBlank

If however you now move on to the ResultSet tab this is where you define what variable will receive the output from your SQL Statement in whatever form that is.

ResultsetBlank

Property Expressions are one of the most amazing things to happen in SSIS and they will not be covered here as they deserve a whole article to themselves. Watch out for this as their usefulness will astound you.

ExpressionsTabBlank

For a more detailed discussion of what should be the parameter markers in the SQL Statements on the General tab and how to map them to variables on the Parameter Mapping tab see Working with Parameters and Return Codes in the Execute SQL Task.

Task Properties

There are two places where you can specify the properties for your task. One is in the task UI itself and the other is in the property pane which will appear if you right click on your task and select Properties from the context menu. We will be doing plenty of property setting in the UI later so let's take a moment to have a look at the property pane. Below is a graphic showing our properties pane.

PropertiesPane

Now we shall take you through all the properties and tell you exactly what they mean. A lot of these properties you will see across all tasks as well as the package because of everything's base structure The Container.

BypassPrepare

Should the statement be prepared before sending to the connection manager destination (True/False)

Connection

This is simply the name of the connection manager that the task will use. We can get this from the connection manager tray at the bottom of the package.

DelayValidation

Really interesting property and it tells the task to not validate until it actually executes. A usage for this may be that you are operating on table yet to be created but at runtime you know the table will be there.

Description

Very simply the description of your Task.

Disable

Should the task be enabled or not? You can also set this through a context menu by right clicking on the task itself.

DisableEventHandlers

As a result of events that happen in the task, should the event handlers for the container fire?

ExecValueVariable

The variable assigned here will get or set the execution value of the task.

Expressions

Expressions as we mentioned earlier are a really powerful tool in SSIS and this graphic below shows us a small peek of what you can do. We select a property on the left and assign an expression to the value of that property on the right causing the value to be dynamically changed at runtime.

PropertyExpressions

One of the most obvious uses of this is that the property value can be built dynamically from within the package allowing you a great deal of flexibility

FailPackageOnFailure

If this task fails does the package?

FailParentOnFailure

If this task fails does the parent container? A task can he hosted inside another container i.e. the For Each Loop Container and this would then be the parent.

ForcedExecutionValue

This property allows you to hard code an execution value for the task.

ForcedExecutionValueType

What is the datatype of the ForcedExecutionValue?

ForceExecutionResult

Force the task to return a certain execution result. This could then be used by the workflow constraints. Possible values are None, Success, Failure and Completion.

ForceExecutionValue

Should we force the execution result?

IsolationLevel

This is the transaction isolation level of the task.

IsStoredProcedure

Certain optimisations are made by the task if it knows that the query is a Stored Procedure invocation. The docs say this will always be false unless the connection is an ADO connection.

LocaleID

Gets or sets the LocaleID of the container.

LoggingMode

Should we log for this container and what settings should we use? The value choices are UseParentSetting, Enabled and Disabled.

MaximumErrorCount

How many times can the task fail before we call it a day?

Name

Very simply the name of the task.

ResultSetType

How do you want the results of your query returned? The choices are ResultSetType_None, ResultSetType_SingleRow, ResultSetType_Rowset and ResultSetType_XML.

SqlStatementSource

Your Query/SQL Statement.

SqlStatementSourceType

The method of specifying the query. Your choices here are DirectInput, FileConnection and Variables

TimeOut

How long should the task wait to receive results?

TransactionOption

How should the task handle being asked to join a transaction?

Usage Examples

As we move through the examples we will only cover in them what we think you must know and what we think you should see. This means that some of the more elementary steps like setting up variables will be covered in the early examples but skipped and simply referred to in later ones. All these examples used the AventureWorks database that comes with SQL Server 2005.

Returning a Single Value, Passing in Two Input Parameters

So the first thing we are going to do is add some variables to our package. The graphic below shows us those variables having been defined. Here the CountOfEmployees variable will be used as the output from the query and EndDate and StartDate will be used as input parameters. As you can see all these variables have been scoped to the package. Scoping allows us to have domains for variables. Each container has a scope and remember a package is a container as well. Variable values of the parent container can be seen in child containers but cannot be passed back up to the parent from a child.

Example1ParametersSet

Our following graphic has had a number of changes made. The first of those changes is that we have created and assigned an OLEDB connection manager to this Task ExecuteSQL Task Connection. The next thing is we have made sure that the SQLSourceType property is set to Direct Input as we will be writing in our statement ourselves. We have also specified that only a single row will be returned from this query. The expressions we typed in was:

SELECT COUNT(*) AS CountOfEmployees FROM HumanResources.Employee WHERE (HireDate BETWEEN ? AND ?)
Example1ShowStatement 

Moving on now to the Parameter Mapping tab this is where we are going to tell the task about our input paramaters. We Add them to the window specifying their direction and datatype. A quick word here about the structure of the variable name. As you can see SSIS has preceeded the variable with the word user. This is a default namespace for variables but you can create your own. When defining your variables if you look at the variables window title bar you will see some icons. If you hover over the last one on the right you will see it says "Choose Variable Columns". If you click the button you will see a list of checkbox options and one of them is namespace. after checking this you will see now where you can define your own namespace.

Example1ParamMap

The next tab, result set, is where we need to get back the value(s) returned from our statement and assign to a variable which in our case is CountOfEmployees so we can use it later perhaps. Because we are only returning a single value then if you remember from earlier we are allowed to assign a name to the resultset but it must be the name of the column (or alias) from the query.

Example1ResultSet

A really cool feature of Business Intelligence Studio being hosted by Visual Studio is that we get breakpoint support for free. In our package we set a Breakpoint so we can break the package and have a look in a watch window at the variable values as they appear to our task and what the variable value of our resultset is after the task has done the assignment. Here's that window now.

Example1WatchWindow

As you can see the count of employess that matched the data range was 2.

Returning a Rowset

In this example we are going to return a resultset back to a variable after the task has executed not just a single row single value. There are no input parameters required so the variables window is nice and straight forward. One variable of type object.

VariablesExample2

Here is the statement that will form the soure for our Resultset.

select 	
p.ProductNumber,	p.name,	pc.Name as ProductCategoryName
FROM	Production.ProductCategory pc
JOIN	Production.ProductSubCategory psc
ON	pc.ProductCategoryID = psc.ProductCategoryID
JOIN	Production.Product p
ON	psc.ProductSubCategoryID = p.ProductSubCategoryID

We need to make sure that we have selected Full result set as the ResultSet as shown below on the task's General tab.

Example2ResultsetOnGeneralTab

Because there are no input parameters we can skip the parameter mapping tab and move straight to the Result Set tab. Here we need to Add our variable defined earlier and map it to the result name of 0 (remember we covered this earlier)

Example2ResultsetMap

Once we run the task we can again set a breakpoint and have a look at the values coming back from the task. In the following graphic you can see the result set returned to us as a COM object. We can do some pretty interesting things with this COM object and in later articles that is exactly what we shall be doing.

Example2WatchWindow

Return Values, Input/Output Parameters and Returning a Rowset from a Stored Procedure

This example is pretty much going to give us a taste of everything. We have already covered in the previous example how to specify the ResultSet to be a Full result set so we will not cover it again here. For this example we are going to need 4 variables. One for the return value, one for the input parameter, one for the output parameter and one for the result set. Here is the statement we want to execute. Note how much cleaner it is than if you wanted to do it using the current version of DTS.

Example3ShowStatement

In the Parameter Mapping tab we are going to Add our variables and specify their direction and datatypes.

Example3ParamMap

In the Result Set tab we can now map our final variable to the rowset returned from the stored procedure.

Example3ResultSet

It really is as simple as that and we were amazed at how much easier it is than in DTS 2000.

Passing in the SQL Statement from a Variable

SSIS as we have mentioned is hugely more flexible than its predecessor and one of the things you will notice when moving around the tasks and the adapters is that a lot of them accept a variable as an input for something they need. The ExecuteSQL task is no different. It will allow us to pass in a string variable as the SQL Statement. This variable value could have been set earlier on from inside the package or it could have been populated from outside using a configuration. The ResultSet property is set to single row and we'll show you why in a second when we look at the variables. Note also the SQLSourceType property. Here's the General Tab again.

Example5ShowStatement

Looking at the variable we have in this package you can see we have only two. One for the return value from the statement and one which is obviously for the statement itself.

Example5ParametersSet

Again we need to map the Result name to our variable and this can be a named Result Name (The column name or alias returned by the query) and not 0.

Example5ResultSet

The expected result into our variable should be the amount of rows in the Person.Contact table and if we look in the watch window we see that it is.

Example5WatchWindow

 

Passing in the SQL Statement from a File

The final example we are going to show is a really interesting one. We are going to pass in the SQL statement to the task by using a file connection manager. The file itself contains the statement to run. The first thing we are going to need to do is create our file connection mananger to point to our file. Click in the connections tray at the bottom of the designer, right click and choose "New File Connection"

Example6FileConnection

As you can see in the graphic below we have chosen to use an existing file and have passed in the name as well. Have a look around at the other "Usage Type" values available whilst you are here.

Example6FileSetup

Having set that up we can now see in the connection manager tray our file connection manager sitting alongside our OLE-DB connection we have been using for the rest of these examples.

Example6ConnectionsTray

Now we can go back to the familiar General Tab to set up how the task will accept our file connection as the source.

Example6ShowStatement

All the other properties in this task are set up exactly as we have been doing for other examples depending on the options chosen so we will not cover them again here.

 

We hope you will agree that the Execute SQL Task has changed considerably in this release from its DTS predecessor. It has a lot of options available but once you have configured it a few times you get to learn what needs to go where. We hope you have found this article useful.

Comments (74) -

10/31/2008 6:03:05 PM #

Steve

These are great examples, but do you have articles that take the next step with these same examples? For instance, once I have a result set back from the package, what can I do with it?

Thanks,

Steve

Steve United States

10/31/2008 9:13:07 PM #

Allan Mitchell

Hi Steve
The resultset can, as you know, take a number of forms but will also be read into a variable.  Once in the variable you can do with it as you will.

If you use this transform www.sqlis.com/post/Row-Number-Transformation.aspx then it is very possible/probable that you will want to seed it with the MAX(Existing Value) which you could retrieve using an ExecuteSQL task and reading into a variable.  Using a property expression you would assign this variable to the Seed property of the transform (we expose this property through the parent Data Flow task)

Another use would be to read a resultset into a variable, of type object, and use that variable in a ForEachLoop enumerator.   Very similar to www.sqlis.com/post/Shredding-a-Recordset.aspx

hope this makes sense.

Allan Mitchell United Kingdom

11/5/2008 11:27:13 AM #

Jorge

You have saved many hours of my time!!. Thanks.

Jorge Argentina

11/5/2008 4:51:52 PM #

PedroCGD

Hi Alan!
How are you!? I hope you are good!

And how can I get the message returned from SQL statment like :
"
Msg 208, Level 16, State 1, Line 1
Invalid object name 't_data_notificacaso'.
"

OR
"
(13 row(s) affected)
"

PedroCGD Portugal

11/5/2008 6:17:10 PM #

Allan Mitchell

Pedro

For your first request.  Have a look in the Output Window and it will give you a big hint as to where to find this (Event Handler)

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "select * from INoThere" failed with the following error: "Invalid object name 'INoThere'

Your second request

Imagine the statement

UPDATE dbo.dimCurrency
SET CurrencyName = CurrencyName

Now add

SELECT @@ROWCOUNT as rtn

set your resultset to Single Row and read into a INT32 variable.

Allan Mitchell United Kingdom

11/7/2008 7:37:18 PM #

PedroCGD

Thanks Allan...
The need is not the error message or the count of rows... what i need is exactly the message inside SQL Server... the first line... Msg 208, Level 16, State 1, Line 1
regards!
See you next SQLBits! :-)
Pedro

PedroCGD

11/19/2008 7:18:16 AM #

Sriwantha Sri Aravinda Attanayake

Great article, keep up with the work! Contains all the information. Thanks buddy, by the way your country list does not have my country, Sri Lanka. hic hic

Sriwantha Sri Aravinda Attanayake

12/6/2008 3:07:34 PM #

Manikandan

Hi,

This is really very much helpful. What i want to get to know is, in case of script task following by the Execute SQL Task and if the Execute SQL task is returning full result set how can i use it Script Task???

Let me know as soon as possible.

Thanks,
Manikandan

Manikandan India

1/1/2009 9:23:55 AM #

Sanjeev

Can we more than on result sets for an XML result set.

Sanjeev India

1/19/2009 3:19:23 PM #

Brian Cooper

How do you pass a variable into a SQL Task that has executes inline SQL within the task?

Brian Cooper

1/22/2009 1:10:39 PM #

Allan Mitchell

personally Brian I would be inclined to create a variable that holds the Expression (EvaluateAsExpression) of the statement you want to run.  In the ExecSQL task you use that instead of a statement

Allan Mitchell Germany

2/7/2009 7:40:21 PM #

Mani V S

Excellent Article... Great Job!!!!

Thanks,
ManI V S

Mani V S India

2/12/2009 2:25:16 AM #

Bob Fidelman

Hello,
What I would like to do is handle a full resultset (created by the execute sql task in the control flow) in a data flow, which has more sophisticated transformation items and variable definitions. Currently there is no way to 'bridge' the gap from control flow to data flow without writing the resultset to disk. What I would like to see is the equivalent of a 'dataset' source in a dataflow. I realize that saving the data as an 'object' loses any metadata, so each column would have to be manually defined, if possible.

Also, using a resultset as a datasource would allow me to use the 'decimal' variable value, which is not defined in the control flow foreach control.

Any suggestions would be appreciated.

Bob F.

Bob Fidelman United States

2/18/2009 9:57:37 AM #

DesC

Hi, great article, but how did you set the watch variables?  I can only get the watch window to appear (there are 4 of them) but I cannot set anything.  Neither can I see anything in the locals window.

DesC

DesC United Kingdom

2/18/2009 10:03:33 AM #

DesC

Also, if instead of CountOfEmployees in the above example I create a datetime variable and try to populate by (for simplicity) a statement like 'SELECT GETDATE() 'MyDate'' it does not work.  I think the datetime format must be somehow intrinsically wrong or incompatible between SQL and SSIS but I'm not sure how.

DesC United Kingdom

3/17/2009 4:57:51 AM #

SDangol

Great article.
For some reason Returning a Rowset from Stored Procedures didn't work.

SDangol United States

3/18/2009 11:15:05 PM #

Mamata

How can I change the value of the input variable to the stored procedure at run time without opening the package. The input variable I need to pass is a varchar field and I'm not sure how to set this in the expression. Can you help?

Mamata United States

3/26/2009 8:14:44 PM #

Jerry

Thank you for the fine discussion of the workings of this container.
It was very helpful.

Jerry United States

4/17/2009 1:08:33 PM #

Laurent

Thank - Merci

Laurent France

5/22/2009 8:22:50 AM #

Jeswanth

Good work, really help full thanks a million

Jeswanth India

5/28/2009 8:18:40 PM #

Puran

Great!!! Thanks a lot...it made my life very easy...

Though I am still facing an issue. I run the query to get single row, also while executing the sql I have added breakpoints and watch. I see it returns the right value, but just before the task turns green it initiliazes to -1.

Any body any idea???

Puran United States

6/10/2009 1:27:56 PM #

Rashmi Patankar

I found the article quite useful, however...I coudnt see any output in the Watch window. Probably, I am missing something on setting the breakpoint part. Can you please brief on it?

Rashmi Patankar India

6/11/2009 4:57:00 PM #

Pat

This exemple was exactly what i needed.
Well done and clear.
i'll use this site a lot in the next few month i think.
Thank

Pat Canada

6/15/2009 6:26:43 PM #

Hemant patel

Just what I was looking for. Thanks

Hemant patel United Kingdom

6/16/2009 8:47:42 PM #

Herbert Rogelj

Hi, great article  -  but my "execute sql task" doesn´t work? Do you have an idea? i just want to get returned from the query -1 or null. True or False: My Query:
SELECT    
(CASE WHEN do_export = 0  AND  data_controlling = 1
THEN -1
ELSE 0
END)  AS IMP  
FROM        Tablexxx
WHERE     (LEFT(userid, 2) = ?)

In the Result Set i named IMP to the VariableName - ist that correct? IMP ever showed -1.
I would be so lucky if you or anyone could help me? Thanks for the answer!!
Herb

Herbert Rogelj Austria

6/17/2009 7:50:16 AM #

Allan Mitchell

Herbert

Does it return a resultset in Management Studio?

In your example you will need a parameter in "Parameter Mapping" for the "?" in your statement.
You will need to define the "Resultset" as "Full Result set"

You will need a variable of type "Object" to hold the resultset.

On the Resultset tab you map the "Result Name" of "0" (zero, null) to the Object Variable.

Allan Mitchell Germany

7/14/2009 2:54:46 PM #

Amit goyal

Hi,
I just wanted to assign the maximum of one of my col. to a variable. I did the following:
Created a variable activity_max
Writing a SQL task
query: select max(id) as id from dbo.activities
Nothing in parameter mapping
Result set : Result name = ID , Variable name = User::activity_Max

My result set = single row

I ran the query which runs fine but the value of my variable is still zero ??

Help please !

Amit goyal India

7/21/2009 9:44:22 PM #

Marianne Daye

I had to pass parameters to a stored procedure and an update statement within a ForEach Loop Container.  This article helped me figure that out.  Thanks!

Marianne Daye United States

9/18/2009 11:11:17 PM #

c_kenth

How do I capture the result set from the following SQL task? I'm running multiple statements, but the Execute SQL Task wants to grab the result set from the first SQL statement, not the last.

create table #Temp (
   value int
   );

insert into #Temp select top 1 col from Permanent;

select value from #Temp;

c_kenth United States

10/22/2009 8:05:46 AM #

Fabien Lonardi

Very good document.
But how to call an Oracle stored procedure. When I put "exec my_proc" in "sql statment", it is not working.
Hhave you an answer or URL links "ssis-oracle"

Fabien Lonardi France

12/8/2009 9:38:18 PM #

Adela

Excellent article! Unfortunately, although I followed the instructions to the letter, I am receiving the following error:
[Execute SQL Task] Error: Executing the query "EXEC usp_helloworld ?,? output" failed with the following error: "Value does not fall within the expected range.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I have created a dummy stored proc:
create table mytest (stmt as varchar(50), stat as int);
CREATE procedure dbo.usp_helloworld
@proj_task_key as bigint,
@proc_status as int output
as
begin
declare
@myproj as bigint;
set @myproj = @proj_task_key;
set @proc_status = 1;
insert into mytest values('helloworld' + cast(isnull(@myproj,'empty') as varchar),@proc_status);
end;
go
then, in SSIS, configured an EXECUTE SQL TASK transform as follows:
ResultSet: None
ConnectionType: OLE DB
Connection: to the db with the usp_helloworld and the table needed forstored proc
SQLSourceType: Direct Input
SQLStatement: EXEC usp_helloworld ,? output
Parameters-
Var Name            Direction Datatype  Parm Name      Parm Size
User::proj_task_key Input LARGE_INTEGER @proj_task_key 0
User::proc_status Output LONG @proc_status 1
As Variables User::proj_task_key is declared as Int64, Value 0 and User::proc_stat is Int32, Value 0.

I do not know where I have gone wrong. Can u help?
Adela

Adela United States

12/8/2009 10:15:04 PM #

Adela

I figured it out! My version of SSIS has Parameter Name and Parameter Size. I was putting name strings in the Parameter Name column when I should have been putting the parameter numbers (0 & 1)!
Yay!

Adela United States

12/10/2009 6:29:30 PM #

Scott C

I made the same mistake as Adela - putting names in the Parameter Names columns instead of 0,1,2...   My next mistake was putting anything in Parameters when what I really wanted was the user variables in the Result Set tab, not the Parameter tab. Once I cleared out Parameters, it ran like a charm and I used my variables to build dynamic flat file names.  :)

Scott C United States

12/15/2009 2:06:51 AM #

Neel

Nice one!!..It helped me a lot. Now I am thinking redesign my data conversion SSIS packages. Thanks again for the efforts.

Neel United States

12/28/2009 9:43:24 PM #

Nits

When I assign my result set to a user variable (int32) which was defined at the package level, the value of the variable became {-1} once the execution was done. I did see correct value in the same value on execution when it stopped at a breakpoint.

Am I missing something here??

Nits

1/6/2010 5:09:28 PM #

vidyasagar

Hi Allan Mitchell,
This article is great and helped a lot.
I have small doubt, now iam using Rowset as FullResutltSet where iam able to get multiple rows in a object variable like Com_Object but how to read the data from the com_object

vidyasagar India

1/13/2010 11:16:55 PM #

Christian

Gracias, Thank you, the tutorial it's very cool and easy.
saludos.

Christian Mexico

1/20/2010 11:01:16 AM #

Darren Green

vidyasagar, the output of using FullResultSet is an ADO Recordset object. Use the ADO library to access it, or try something like the ForEach Loop which has an ADO enumerator built in. An example of this is shown in the post www.sqlis.com/post/Shredding-a-Recordset.aspx

Darren Green United Kingdom

1/21/2010 12:13:50 PM #

Bruno Pimenta

Hello Allan,

I have a question about the execute SQL Task regarding the TIMEOUT. My objective is to unpack a data file from an receive data folder, read it's content into a DB and them compact the result to a archive folder (in case of error a message is set).

So far I was able to do all the steps, if you need details feel free to ask.

The problem starts when the file becomes large (I'm using one with 2 GB of size) because the SSIS execute task does not wait for the full unpacking and sets the error task. I think this is a timout problem and I have setted the value to 3600 (being in seconds this would mean an hour and it does not take that long) aldo without any success, it keeps falling.

If you can help me or if you have had a similar problem plesa respond.

Thanks in advance,

Complements,

Bruno Pimenta Portugal

1/21/2010 3:05:31 PM #

Javier Mora

Hello everybody ...

I have a problem when execute this qry in Execute sql task:

[Execute SQL Task] Error: Executing the query "select *     from   SRVCP.SerivaCapaDatos.dbo.CDH_ADMINISTRACIONPORFORMAPAGO" failed with the following error: "Error no especificado (Excepción de HRESULT: 0x80004005 (E_FAIL))". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I used Linked Server to connect to other server (Link server name : SRVCP). When i execute de sentence ( elect *     from   SRVCP.SerivaCapaDatos.dbo.CDH_ADMINISTRACIONPORFORMAPAGO) in Sql query analyzer it works great ...

Thanks for your time ...

Javier Mora Colombia

1/28/2010 8:53:07 AM #

Darren Green

Javier, the error you quote is the generic one the task raises. I would expect there another error message that gives more detail. It is common for a single problem to be reported across several messages in SSIS. The source will of course be the task.

Start by simplifying the issue, don’t use any input or output variables or any result set options, just try and get the SQL to execute. Since you mentioned linked servers, security is an obvious candidate. Think what is different about the manual query test compared to when using SSIS. Is the location or user different for example?

Darren Green United Kingdom

4/22/2010 11:52:02 AM #

Darren

Vani, the lack of images was no doubt all part of our recent Hosting woes (http://www.sqlis.com/post/Hosting-woes.aspx).

Darren United Kingdom

5/3/2010 10:10:23 PM #

jenny

This was of great help. But i have one question:
What if you have a SQL command in ADO.NET source in data flow task and you have to query against oracle DB. How can you pass date as a variable in that dataflow without writing a stored Procedure.

jenny

5/5/2010 5:18:51 PM #

Kari Suresh

I can see help only for of type OLEDB. I don't find anything replated to ADO.NET. Is this so difficult?

Kari Suresh India

5/24/2010 10:46:06 PM #

Ravi

Can some one provide sample SSIS links to connect to Oracle using OLEDB provider passing parameterized query to Oracle tables * buidling dynamic variables then executing as variables using Oracle OLEDB have buffer size issues.. (it works fine in DTS using ODBC)

Ravi United States

6/13/2010 6:05:08 PM #

Vlad

SSis package contains two tasks . First creates .csv file with varaible name based on  user::Filename (generated as Expresion Name+Timestamp) type string. When I try to pass this parameter to SQL task to execute SP with parameter I got conversion errors.
Which Data type should I assign. Lengh of Filename>50. I tried varchar,nvarchar different lengths - all fails with errors of conversion

Vlad Australia

6/17/2010 9:21:54 AM #

Sivaprasad S

Getting errors below:

[Execute SQL Task] Error: Failed to acquire connection "MulitServer". Connection may not be configured correctly or you may not have the right permissions on this connection.

Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

[Connection manager "MulitServer"] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Login timeout expired".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. ".


Sivaprasad S Australia

6/18/2010 7:12:17 AM #

Jorge

Excelent tutorial. Thanks

Jorge Mexico

6/25/2010 9:34:51 AM #

glaudie

hi there,

I've followed the steps describe above 100%.  I've declared a value which i want to populate with the result of a query to use as a starting point for my etl flow.  However , when i look at the variable value at breakpoint execution the value just DOES NOT get updated. I've tried to convert, cast , alias , not alias , using 0 as the result set name. NOTHING works. My variable simply keep its initial value i assigned it.  Got the advice that leaving the variable blank when defined, it could create issues. But even when i left its initial value blank, it still just kept the value "blank" at runtime, and never populated it with the result from my query. I've been struggling with this for 5 days now, and i'm so frustrated. it seems so simple, but somehow i must be doing something wrong that is preventing the value from being populated to the variable.

I found this post from someone else having the exact same issue, and i have done the exact steps this guy did.

I am trying to populate variables in a new SSIS package using the Execute SQL Task but I am having an issue to get the actual values to update! Steps I have completed....Created Variables with Package wide scopeAdded an Execute SQL task.Pointed connection to ServerEdited (& Tested!) the SQL StatementChanged Results Set to Single RowUpdated the Results Set tab to correct column and variableIf you run the package the Execute SQL Task works fine but watching the variables shows that the value does not change at all..I need these to do some dynamic stuff later on..Seems pretty straight forward..Am I missing something??Extra Details of an example..--Execute SQL Task--SQL Statement:SELECT FinancialYear --Works fineFROM [RemusV2].[dbo].[tbl_Structural_Dates]WHERE DATE = DATEADD(dd, DATEDIFF(dd,0,GETDATE())-7, 0)Result Set:SingleRowResultName=FinancialYearVariableName:FinancialYear

glaudie South Africa

7/13/2010 12:35:06 PM #

Akshay

@Sivaprasad S:
Are you sure the data source server is accessible? Are you able to connect to it via SSMS?

Akshay India

7/20/2010 10:15:55 AM #

manoj N

Hi

I am using SQL  task control
where in first i am checking if exist table <table1> then delete it
and next sql stmnt is Create the table <table1>.
i have included  both sql stmnt one next the other

but Sql task is failing

any help

manoj N India

8/3/2010 1:51:47 PM #

Albert

Здравствуйте!

Не могу разобраться, помогите пожалуйста!

ExecuteSQLTask выставил настройки:  result set = None , ConnectionType = OLE DB, Connection = BD, SQLSourceType = Direct input, SQLStatement = "select Columns from Table"
Все работает. По крайней мере ошибок нет. Далее. Хочу увидеть выходные данные. меняю:
result set = Full result set
ResultSet.ResultName = 0
ResultSet.VariableName = User::Columns
Запускаю. Ошибка:
[Execute SQL Task] Error: Executing the query "select Columns from Table" failed with the following error: "Неопознанная ошибка". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Что не так? Хочу лицезреть данные по Columns

Albert Russia

8/3/2010 3:22:02 PM #

Albert

Всем спасибо :) разобрался. Как иногда с Вами полезно пообщаться....

Albert Russia

8/4/2010 6:05:24 PM #

Beth

glaudie, did you find a solution for your issue? I am having the same issue, did it the same way as instructions and value is not updating.

This is my SQL statement and I defined my Variable as String even though it is defined as bigint in the database because I was getting an error and another post said you had to define bigint resultset variables as string.

SELECT MAX(EventLogID)AS MaxEventLogID
from STAGING.ImportHistoryEventLog

Beth United States

10/15/2010 2:07:11 AM #

Sam

Even with SQL 2010 R2 out this is still badly needed.  I agree that the high level docs read fine and everything seems sensible and logical ...until you try to actually do it and notice that the details are very vague. This article saved me lots of frustration.
Thanks much!

Sam United States

11/12/2010 8:26:49 PM #

Mike Milligan

So frustrating...

I am trying to do this:
select asdf from sometable where somecol = 'something'
union all
select asdf from sometable where somecol = 'something2'
union all
select asdf from sometable where somecol = 'something3'

then I go to the result set and have 3 ResultNames 0,1,2 ea. going to differ user::variables.

It works perfectly in one package but for the life of me I can't get it to work in another.

Arrrgh!

Any ideas?

Thanks!

Mike

Mike Milligan United States

11/12/2010 8:53:53 PM #

Mike Milligan

I stand corrected.  It wasn't working in either.

Ended up doing something like this:

select sum(one),sum(two),sum(three)
from
(select one = asdf,two = 0,three = 0 from sometable where somecol = 'something'
union all
select one = 0, two = asdf, three = 0 from sometable where somecol = 'somethingelse'
....)  aliasy

Mike Milligan United States

11/15/2010 2:43:11 AM #

tom xie

Hi,
Can you pass parameters to the file connection?

tom xie United States

12/1/2010 4:40:16 PM #

laban

how do you get values from rowset in the script task?
ie select col1, col2 from table where col1 = value
set resultset in sql task to 0 and user::test

how do you use dts.variable(user::test) in sql script task
or how do you read

laban Kenya

12/9/2010 4:29:22 AM #

yoyo

nice example for execute sql task..
if it quiet good with a finish result in the last step or is there any other step ?

yoyo Indonesia

12/14/2010 8:59:31 AM #

Sid

very nice article. It helped me a lot. Keep up the good work.

Thank you so much.

Sid India

1/10/2011 5:37:38 PM #

Chris H

Excellent article and just what I was looking for.

Keep up the good work.  

Chris H Ireland

1/14/2011 10:39:54 AM #

Sid

this is the article i was looking for to understand execute sql task

Sid India

2/9/2011 12:13:37 PM #

Surendra Yadav

i really appreciate this site and author.

I need one help in execute sql task

its conditional

if file exists
then raise error and fail the task toward fail flow
else
flow the task in success direction

i have tried to raise the error through raiseerror in the sqlstatement however its not caching and forwarding the flow to task fail direction

please help

thanks in advance

Surendra Yadav India

3/28/2011 3:31:00 PM #

Biju Koshy

Hi

Can you please tell me how we can acheive the below scenario.

1. I have a txt file with 3 values(policy, term date, Name).
2. I need to UPDATE term date field in the table WHERE policy = ???(value from txt file) AND Name = ???(value from txt file).

Pleaseee Help....

Biju Koshy United States

3/30/2011 7:16:36 AM #

someguy198650

Hi,

how do you pass the value from the variable of the first Task to the second task ?

someguy198650 Philippines

4/7/2011 3:21:16 PM #

raj


Needed help...have a DTS package, where an execute store proc is executed and then "isql" statement is written to output the results from executed storeproc to External file (text file).

My question is how should i do this in SSIS....

I have a plan of putting Storeproc in execute sql task and then from there,  have no idea how should i result them, my aim is to FTP it(results)...can anyone help me

raj Lithuania

4/29/2011 8:58:01 AM #

Iñigo

Please help me!
I've a DTS package in SQL Server 2000 and i've converted to SSIS (SQL Server 2008) but I need to know how to run it from the SQL Server Query Analizer because when I did it in SQL Server 2000 I ran it with the next transaction:

exec master.dbo.xp_cmdshell dtsrun /s (local) /e /n PACKAGENAME

Could anybody tell me how can I run the SSIS now in SQL Server 2008?

Thanks in advance

Iñigo Spain

5/1/2011 12:28:50 PM #

Piyush

@Raj,

Just add a Data FLow task and execute the procedure in OLDEDB Source. U will get the resultset which u can pass to Flat FIle Destination

@Iñigo,
U can use the same xp_cmdshell command to execute an ssis package too using dtexec.exe utility. Just goto MSDN and search for dtexec.exe and u will get the answer to execute SSIS from SSMS.

Piyush India

5/1/2011 12:31:15 PM #

Piyush

@Biju Koshy,

Use the OLE DB Command transformation in your dataflow source.

Piyush India

5/10/2011 11:02:04 PM #

Eran

Hi Robert and Glaudie

I had the same problem. The Execute SQL Task has simply "decided" not to pass the values outside. I did everything right but the variables refused to be populated by any values, without any error message.

At the end i just deleted the Execute SQL task and the variables and build it from scratch. I built exactly the same like it was before (just did copy paste to the SQL statement and setting up again new variables... it solved the problem! now, everything was right.

I don't know what has created this problem at the first time. It seems to me like a "transparent" referencing to the cache memory where something forced the PC to get stuck with the initial values and do not let the SSIS do his job... Anyway, now its fine.

It worth trying.

Eran Israel

6/3/2011 9:11:15 AM #

Rufus

Great work.

Clearly expounds about passing variable in and out of SSIS tasks.

Rufus Kenya

5/15/2012 6:51:42 AM #

Manoj Gokhale

I want to execute a stored procedure with 2 input parameters and resultset and write the sqlstatement as
=> exe  dbo.test1 ?, ?
The procedure is compiled in SQLSERVER.
When I try to "parse query". The SSIS gives the message "The query failed to parse ...". Also, the user has the permissions to execute the procedure
Help e what cud be the reason

Manoj Gokhale United States

6/27/2012 4:51:11 PM #

Giulia Prandini

Hi all,
I follow the example but when I run the project the Sql task failed,cause:

The query failed to parse. Must declare the scalar variable "@variable".

Do you know solution?

Giulia Prandini Italy

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Widget Amazon Book not found.

Object reference not set to an instance of an object.X

RecentComments

Comment RSS