Executing MAXL Scripts in Execute Process Task

Hello,

first of all a few words about our achitecture:

we have a windows 2003 server with sql sever 2005 and connect on this server with 3 clients (Visual Studio) on the Windows 2003 Server there is also an Hyperion Essbase Server installed.

In the Visual Studio I try to execute a batch script which is located on the server. But how i develop that? I have tried to rebuilt the servers File structure and save the Package on the Server. < didn't worked

My Question how can I make a package in which I execute a bat file which is located on the Server without developing it on the Server?

Thank you in advance!

Can you put the batch file in a share, so that you can use a UNC path to reference it? That will work from server or client.

Executing exe delphi program with Agent

Hi,
I'm trying to execute a program from de agent. The program executes(i can see it in the task manager), but it block in that step and still in the task manager.
I tried with the calc.exe command with de same result.
?only DOS commands can execute the agent or there is another way to make it work?
Thanks in advance
Lomu
Hi
If the program shows a GUI then you should not run it from a scheduled job.
If you make it a command line program that will run and exit correctly then
you may be ok. Depending on what you are wanting to do there could be other
alternatives, such as writing an extended stored procedure, making it a COM
object.
John
"Lomu" <Lomu@.discussions.microsoft.com> wrote in message
news:BCDDC329-81E3-457E-90E4-3C38A830AF92@.microsoft.com...
> Hi,
> I'm trying to execute a program from de agent. The program executes(i can
see it in the task manager), but it block in that step and still in the task
manager.
> I tried with the calc.exe command with de same result.
> only DOS commands can execute the agent or there is another way to make
it work?
> Thanks in advance
> Lomu

Executing exe delphi program with Agent

Hi,
I'm trying to execute a program from de agent. The program executes(i can see it in the task manager), but it block in that step and still in the task manager.
I tried with the calc.exe command with de same result.
¿only DOS commands can execute the agent or there is another way to make it work?
Thanks in advance
LomuHi
If the program shows a GUI then you should not run it from a scheduled job.
If you make it a command line program that will run and exit correctly then
you may be ok. Depending on what you are wanting to do there could be other
alternatives, such as writing an extended stored procedure, making it a COM
object.
John
"Lomu" <Lomu@.discussions.microsoft.com> wrote in message
news:BCDDC329-81E3-457E-90E4-3C38A830AF92@.microsoft.com...
> Hi,
> I'm trying to execute a program from de agent. The program executes(i can
see it in the task manager), but it block in that step and still in the task
manager.
> I tried with the calc.exe command with de same result.
> ¿only DOS commands can execute the agent or there is another way to make
it work?
> Thanks in advance
> Lomu

Executing exe delphi program with Agent

Hi,
I'm trying to execute a program from de agent. The program executes(i can se
e it in the task manager), but it block in that step and still in the task m
anager.
I tried with the calc.exe command with de same result.
?only DOS commands can execute the agent or there is another way to make it
work?
Thanks in advance
LomuHi
If the program shows a GUI then you should not run it from a scheduled job.
If you make it a command line program that will run and exit correctly then
you may be ok. Depending on what you are wanting to do there could be other
alternatives, such as writing an extended stored procedure, making it a COM
object.
John
"Lomu" <Lomu@.discussions.microsoft.com> wrote in message
news:BCDDC329-81E3-457E-90E4-3C38A830AF92@.microsoft.com...
> Hi,
> I'm trying to execute a program from de agent. The program executes(i can
see it in the task manager), but it block in that step and still in the task
manager.
> I tried with the calc.exe command with de same result.
> only DOS commands can execute the agent or there is another way to make
it work?
> Thanks in advance
> Lomu

Executing an SSIS package containing a Data Mining Query task from a SQL job

Hi, I'm new to this forum, so please bare with me.

I've created a mining model, i've tweaked it etc and i'm now happy with the results its producing. I'm now looking to automate the processing and exporting of the results of the model i've done this simply by creating an SSIS package with two tasks, one task being to process the model the other task is a Data Mining Query task.

This package works fine in visual studio and when i deploy it to the server.

The problem i'm having is when i then try to execute the package from a job, after a bit of investigating i have tracked it down to the Encryption of "sensitive" properties. By default the encryption is based on UserKey which is why the package works for me when i execute it from VS or even the server, but when the job trys to execute the package running under the sql agent account it fails.

Looking at the security options i have for packages, i can either DontSaveSensitive, EncryptSensitiveWithUserKey or EncryptSensitiveWithPassword plus a few others.

DontSaveSenstive is clearly not an option as this just creates an unusable package.

EncrptSensitiveWithUserKey doesn't seem to be an option as the job runs under the SQL Agent account (also i'm thinking that the UserKey that the encryption is based on also incorporates other factors related to my profile that i can't impersonate? i might be wrong though)

EncryptSensitveWithPassword seems to be an option except that i can't get this to work either, there doesn't seem to be anyware in the job step to give it the password information.

Its frustrating me now because i've fallen at the very last hurdle, if anyone else has experienced this problem and knows how to resolve it that would great.

Thanks

Bob.

There is a comprehensive KB article that may cover your question:

http://support.microsoft.com/kb/918760

|||

Thanks, that has helped.

for reference i employed the DontSaveSensitive level of security and stored the Query String for the Data Mining Query task in an XML configuration file.

this is the only option on the KB article that worked for me.

Thanks.

Executing a whole container in one transaction

Hello all,

I am trying to build a package running a container that includes several tasks: 2 Execute SQL tasks and a data flow task. I would like to execute this container in a single transaction, which means that if any of the tasks fail, no changes are been made by the container execution.

To achieve this, I have followed the instructions of how to configure the DTC on the server and locally, and set the container's TransactionOption Property to "Required".

The problem I encounters is that when using a dataflow task in the container, the running process runs OK until it reaches the dataflow task, then it just gets stuck (marking the task in yellow). When removing the dataflow task, left with only Execute SQL tasks everything works fine.

Any idea?

Thanks,

Liran

does the dataflow task get "stuck" if you execute it by itself?

Note that some operation in dataflow - caching for lookup - may take a long time - so how long does it stay stuck ?
|||

Liran R wrote:

Hello all,

I am trying to build a package running a container that includes several tasks: 2 Execute SQL tasks and a data flow task. I would like to execute this container in a single transaction, which means that if any of the tasks fail, no changes are been made by the container execution.

To achieve this, I have followed the instructions of how to configure the DTC on the server and locally, and set the container's TransactionOption Property to "Required".

The problem I encounters is that when using a dataflow task in the container, the running process runs OK until it reaches the dataflow task, then it just gets stuck (marking the task in yellow). When removing the dataflow task, left with only Execute SQL tasks everything works fine.

Any idea?

Thanks,

Liran

You'll probably find that some operations are blocking others.

Are you using SQL Server? If so, execute sp_who2 and look in the Blk column for SPID=-2.

-Jamie

|||Liran,

Is the behaviour same when you set the TransactionOption to "Supported" or "Not Supported", i.e. when not using DTC?

Also, take a look at the data flow window for that DFT to see which tasks are in yellow.|||

Jamie Thomson wrote:

You'll probably find that some operations are blocking others.

Are you using SQL Server? If so, execute sp_who2 and look in the Blk column for SPID=-2.

-Jamie

Yes, there is indeed a blocking:

SPID: 67

Status: SUSPENDED

Login: liran

HostName: Liran-Computer

DBName: STG

Command: select * from [dbo].[STG_FACT_A]

Wait type: LCK_M_IS

BlkBy: 64

SPID: 64

Status: Sleeping

Login: LinkAdmin

HostName: DW

DBName: master

Command: sys.sp_getschemalock;1

So I understand process 64 is locking the schema preventing process 67 from running.

Why is it happening? why only when using dataflow?

This behavior only happens when the container is set to "Required" in the transactionOption, and stuck on the dataflow (yellow).

Thanks,

Liran

Executing a task with in script task

How can I execute a sql task from a script task. Both these tasks are part of the same package. The script task is actually part of the error handler. The execute sql task is part of control flow in the package.

Thanks

Why would you want to do this, as opposed to putting an Execute SQL Task in your error handler?|||Well, I dont know if I put a task specific error handler, will the package level error handler will fire or not. ( I want both error handlers to be fired). I'm trying it out now, let me see how it goes.|||Yes, the package error handler should fire as well. Events propagate up to the parent.

Executing a stored proc on another server from a Scheduled task

Ok, I thought this one would be easy.

I have a stored proc: master.dbo.restore_database_foo

This is on database server B.

Database server A backs up database foo on a daily basis as a scheduled
task.

What I wanted to do was, at the end of the scheduled task is then call the
stored proc on B and restore the database.

If I go into Query Analyzer and log into database A, then exec
b.master.dbo.restore_database_foo works.

But if I take the same command and make it part of the scheduled task it
fails.

Error is:

OLE DB provider 'SQLOLEDB' reported an error. [SQLSTATE 42000] (Error 7399)
[SQLSTATE 01000] (Error 7312). The step failed.

To me this seems like a permissions issue, but nothing I've tried seems to
have helped.

Suggestions?

--
--
Greg D. Moore
President Green Mountain Software
Personal: http://stratton.greenms.comGreg D. Moore (Strider) (mooregr@.greenms.com) writes:
> I have a stored proc: master.dbo.restore_database_foo
> This is on database server B.
> Database server A backs up database foo on a daily basis as a scheduled
> task.
> What I wanted to do was, at the end of the scheduled task is then call the
> stored proc on B and restore the database.
> If I go into Query Analyzer and log into database A, then exec
> b.master.dbo.restore_database_foo works.
> But if I take the same command and make it part of the scheduled task it
> fails.
> Error is:
> OLE DB provider 'SQLOLEDB' reported an error. [SQLSTATE 42000] (Error
> 7399) [SQLSTATE 01000] (Error 7312). The step failed.
> To me this seems like a permissions issue, but nothing I've tried seems to
> have helped.

SELECT * FROM master..sysmessages where error in (7399, 7312) gives me

"Invalid use of schema and/or catalog for OLE DB provider '%ls'. A four-part
name was supplied, but the provider does not expose the necessary interfaces
to use a catalog and/or schema." and "OLE DB provider '%ls' reported an
error. %ls"

Doesn't tell me a whole lot.

Can't you take the easy way out and make the step a command-line
step that invokes OSQL?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93EAE9E441CB8Yazorman@.127.0.0.1...
> SELECT * FROM master..sysmessages where error in (7399, 7312) gives me
> "Invalid use of schema and/or catalog for OLE DB provider '%ls'. A
four-part
> name was supplied, but the provider does not expose the necessary
interfaces
> to use a catalog and/or schema." and "OLE DB provider '%ls' reported an
> error. %ls"
> Doesn't tell me a whole lot.

Yeah, same here. It seems to be one of those things that SHOULD be easy.
:-)

> Can't you take the easy way out and make the step a command-line
> step that invokes OSQL?

I could, but that just seems to "dirty" :-)

But that may be the way I go if I can't find a cleaner solution.

> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||"Greg D. Moore \(Strider\)" <mooregr@.greenms.com> wrote in message news:<wM95b.29715$yG2.21729@.twister.nyroc.rr.com>...
> "Erland Sommarskog" <sommar@.algonet.se> wrote in message
> news:Xns93EAE9E441CB8Yazorman@.127.0.0.1...
> > SELECT * FROM master..sysmessages where error in (7399, 7312) gives me
> > "Invalid use of schema and/or catalog for OLE DB provider '%ls'. A
> four-part
> > name was supplied, but the provider does not expose the necessary
> interfaces
> > to use a catalog and/or schema." and "OLE DB provider '%ls' reported an
> > error. %ls"
> > Doesn't tell me a whole lot.
> Yeah, same here. It seems to be one of those things that SHOULD be easy.
> :-)
> > Can't you take the easy way out and make the step a command-line
> > step that invokes OSQL?
> I could, but that just seems to "dirty" :-)
> But that may be the way I go if I can't find a cleaner solution.
>
> > --
> > Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> > Books Online for SQL Server SP3 at
> > http://www.microsoft.com/sql/techin.../2000/books.asp

If you are restoring database remotely you should give local admin
privileges on target server to the SQLServerAgent from the primary
server. Just executing stored procedure from the Query Analyzer does
not simulate fully your situation. You should assign one Global
Windows-based account to the SQLServerAgent on primary server and give
it local admin privileges on target server instead of using generic
Local System account. If Network Admins are not cooperative you can
create Local Windows accounts with the same name and password on both
servers and add these accounts to both database servers.

Sinisa Catic

Executing a batch script from "Execute Process" Task

Hello,

I have an "execute process" task which executes a perl script. When I run the task, it shows a prompt with the message "the publisher can not be verified". It gives the option to continue the task or to cancel.

The problem for me is that I want to schedule this package to run automatically, and I don't want the automated process to show this dialog. I need to override the security setting and have SSIS execute my batch scripts without prompting. Assuming I don't want to rewrite the Perl code into a script task, is there a way to do acomplish this?

Thanks!

Arkadiy

Normally, Digitally Signing whatever is being called into question could resolve this type of error, but I have not seen this error occur in SSIS as of yet (usually see this in IE).

Here's some stuff you can try:

Try testing after lowering the internet security (on the test system) to a lower setting (for example low - or "custom level" and play around with the settings here). Be sure to set this back when you are done testing.

You could look into digitally signing the perl script. I'm not sure if this pertains to that or not:

http://www.cpan.org/authors/id/R/RO/ROODE/perlsign-0.04

"This program invokes GPG to digitally sign Perl source files. The GPG signature becomes embedded into the source file as POD paragraphs. The functionality of the program or module isn't affected. This may be useful to prove authorship of a program, or to prove that changes have or have not been made to a source file."

You could read this to see if it gives you any insight:

http://www.microsoft.com/technet/Security/bestprac/mblcode.mspx

Anyway, if this is not relavent, can you provide more details as to how you are executing the perl script? Like where the perl script is stored, etc.

|||

Thanks,

I found another way of solving this without changing the security settings. The trick is to invoke executable or dll (such as perl.exe) and not a batch script. So in the "execute process" task, the executable became perl.exe and everything else became command line argument. Then it works fine.

Thanks again for your help anyway!

Arkadiy

ExecuteSQLTask

Hey everybody,

Is there any ways that I could execute a "ExecuteSQLTask" from a script task inside the package. I mean both "ExecuteSQLTask" and "ScriptTask" are in the same package.

Any tips?

Thanks

Ryan

Just curious. Why do you want to execute "Execute SQL Task" from a script task?|||I have a set of rules in a table and depending on each rule type I have to execute a different step.|||

Why do you think that being able to fire an Execute SQL Task from within a Script task will help you with this?

Your best bet would be to loop over the rules using a ForEach loop and then use conditional precedence constraints within the ForEach loop to decide which Execute SQL Task to execute.

-Jamie

sql

ExecuteSQL task has changed

Since the last IDW.

The column "ParameterName" has been added to the ParameterMapping tab of the ExecuteSQL task.

I enter a statement of

SELECT * FROM TABLE WHERE COLUMN = ?

I map ? to a input variable. The default name of the parameter supplied is "NewParameterName"

My task now fails with

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "SELECT * FROM TheBigOne WHERE HostName = ?" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
What parameter? The one the task supplies?

Allan

Allan
I was able to get passed this by putting ? in the paramter name column.

Norman P.|||I cannot get it to work even using that and it would kinda make no sense either because what if I had

SELECT X FROM TABLE WHERE DATE BETWEEN ? AND ?

What would be the Parameter Names?

Allan|||Ok so i just got it to work

say I have a statement of

SELECT AddressID
FROM Person.Address
WHERE City = ?

The parmeter name should be @.City

I could not find this anywhere in the docs.

Allan|||Allan,

Are you using a managed provider?

ash|||Allan
I tried it your way which works but also used ? in both paramter names and it also worked provided the paramters where in the correct order.

Norman P.

ExecuteSQL task fails and I think it should not

I setup my ExecuteSQL task to have a "Single Row" resultset. The query returns no rows. It fails. I don't think it should but then maybe this is consistent with the lookup transform piping rows down the error output if there is not a lookup value returned.

The error returned is

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "Variable": "Single Row result set is specified, but no rows were returned.".
Thanks

AllanDoesn't sound right to me. If you were getting the MAX of something its perfectly plausible that no results would be returned (if no records in the table).

-Jamie|||Exactly |||Hmm... interesting. Can you open this on BetaPlace, please?

thanks!
ash|||I'm working with the realease version and having the same problem. Any answer to this?|||

The following discussion pertains to the Execute SQL Task Control flow.

THE SUGGESTIONS I MAKE ARE TO THE PROPERTIES WHICH YOU HAVE TO RIGHT CLICK AND GO TO PROPERTIES ON THE EXECUTE SQL TASK. DO NOT TRY TO CORRECT THIS PROBLEM IN THE EXECUTE SQL TASK EDITOR.

What you need to do is change the "ForceExecutionResults" property to "Success." This will fix your problem.

Secondly you need to be watchful of your MaximumErrorCount property else your system will fail out.

I hope this helps merry christmas.

|||

SELECT 0 + ISNULL((SELECT MAX( COLUMN1 ) FROM TABLE1 WHERE COLUMN2 = 'XXX'), 0)

OR

SELECT '' + ISNULL((SELECT COLUMN1 FROM TABLE1 WHERE COLUMN2 = 'XXX'), '')

doing so, you will always have a result the task can forward.

Not pretty but preferable vs. the ForceExecutionResults solution i think.

|||

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now. Any feedback about a permanent solution from the SSIS development team?

I also prefer the coded ISNULL workaround over setting the ForceExecutionResults property. You can also use the COALESCE function to the same effect, which I prefer for similar scenarios.

|||

highpockets wrote:

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now.

bug reports should be submitted here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68

ExecuteSQL task fails and I think it should not

I setup my ExecuteSQL task to have a "Single Row" resultset. The query returns no rows. It fails. I don't think it should but then maybe this is consistent with the lookup transform piping rows down the error output if there is not a lookup value returned.

The error returned is

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "Variable": "Single Row result set is specified, but no rows were returned.".
Thanks

AllanDoesn't sound right to me. If you were getting the MAX of something its perfectly plausible that no results would be returned (if no records in the table).

-Jamie|||Exactly |||Hmm... interesting. Can you open this on BetaPlace, please?

thanks!
ash|||I'm working with the realease version and having the same problem. Any answer to this?|||

The following discussion pertains to the Execute SQL Task Control flow.

THE SUGGESTIONS I MAKE ARE TO THE PROPERTIES WHICH YOU HAVE TO RIGHT CLICK AND GO TO PROPERTIES ON THE EXECUTE SQL TASK. DO NOT TRY TO CORRECT THIS PROBLEM IN THE EXECUTE SQL TASK EDITOR.

What you need to do is change the "ForceExecutionResults" property to "Success." This will fix your problem.

Secondly you need to be watchful of your MaximumErrorCount property else your system will fail out.

I hope this helps merry christmas.

|||

SELECT 0 + ISNULL((SELECT MAX( COLUMN1 ) FROM TABLE1 WHERE COLUMN2 = 'XXX'), 0)

OR

SELECT '' + ISNULL((SELECT COLUMN1 FROM TABLE1 WHERE COLUMN2 = 'XXX'), '')

doing so, you will always have a result the task can forward.

Not pretty but preferable vs. the ForceExecutionResults solution i think.

|||

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now. Any feedback about a permanent solution from the SSIS development team?

I also prefer the coded ISNULL workaround over setting the ForceExecutionResults property. You can also use the COALESCE function to the same effect, which I prefer for similar scenarios.

|||

highpockets wrote:

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now.

bug reports should be submitted here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68

ExecuteSQL task fails and I think it should not

I setup my ExecuteSQL task to have a "Single Row" resultset. The query returns no rows. It fails. I don't think it should but then maybe this is consistent with the lookup transform piping rows down the error output if there is not a lookup value returned.

The error returned is

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "Variable": "Single Row result set is specified, but no rows were returned.".
Thanks

Allan
Doesn't sound right to me. If you were getting the MAX of something its perfectly plausible that no results would be returned (if no records in the table).

-Jamie|||Exactly |||Hmm... interesting. Can you open this on BetaPlace, please?

thanks!
ash|||I'm working with the realease version and having the same problem. Any answer to this?
|||

The following discussion pertains to the Execute SQL Task Control flow.

THE SUGGESTIONS I MAKE ARE TO THE PROPERTIES WHICH YOU HAVE TO RIGHT CLICK AND GO TO PROPERTIES ON THE EXECUTE SQL TASK. DO NOT TRY TO CORRECT THIS PROBLEM IN THE EXECUTE SQL TASK EDITOR.

What you need to do is change the "ForceExecutionResults" property to "Success." This will fix your problem.

Secondly you need to be watchful of your MaximumErrorCount property else your system will fail out.

I hope this helps merry christmas.

|||

SELECT 0 +ISNULL((SELECT MAX( COLUMN1 ) FROM TABLE1 WHERE COLUMN2 ='XXX'), 0)

OR

SELECT '' +ISNULL((SELECT COLUMN1 FROM TABLE1 WHERE COLUMN2 ='XXX'), '')

doing so, you will always have a result the task can forward.

Not pretty but preferable vs. the ForceExecutionResults solution i think.

|||

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now. Any feedback about a permanent solution from the SSIS development team?

I also prefer the coded ISNULL workaround over setting the ForceExecutionResults property. You can also use the COALESCE function to the same effect, which I prefer for similar scenarios.

|||

highpockets wrote:

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now.

bug reports should be submitted here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68sql

ExecuteOutOfProcess calling a transactional child package causes Access is Denied.

I have a master package that contains an Execute Package Task whose ExecuteOutOfProcess flag is True, and that calls a child package whose TransactionOption = Required. The job is running in Sql Agent, and the step that calls the master package is configured to run under a certain domain account that is not in the local Administrators group. With this, I get the following:

messageText: Error 0x80070005 while loading package file "C:\program files\microsoft sql server\90\dts\Packages\ETL\Fact_Various_TransactionalChannels.dtsx". Access is denied.

When I add the domain account to the local Administrators group, this error does not occur. From a blog entry, I read that when a child package is executed out of process, the resultant OS process is called dtshost.exe (http://blogs.conchango.com/jamiethomson/comments/1414.aspx). Do I simply need to give my domain account permission to spawn this process? If so, what permission is it? Is there a group that contains this permission?

Is it possible that the domain account does not have access to "C:\program files\microsoft sql server\90\dts\Packages\ETL\"

-Jamie?

|||Unfortunately, no. The account has full control over that path.

Execute Stored Procedure via Execute SQL Task

Do you know of a bug in the June CTP of SSIS where you cannot, using an Execute SQL Task, execute a stored procedure with parameters via an OLE DB connection? For example, one combination I tried was in the SQL Statement, I have:

EXEC dbo.DimBuild ?,?

And in the parameter mapping, I added two date user variables, both with parameter name ‘?’

--

I also tried

EXEC dbo.DimBuild sd, ed

And in the parameter mapping, I added two date user variables, one with a parameter name ‘sd’ and the other ‘ed’

--

I tried many other combinations as well. The error I would get would say “parameter name unrecognized”. [Execute SQL Task] Error: Executing the query "dbo.DimBuild sd, ed" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Is there something wrong with my syntax? Interestingly, I tried executing the stored procedure using an ADO.Net connection, with similar parameter mappings, and it worked just fine.

Thanks,

- Joel

I was banging my head against the wall trying to figure this out also. I finally created a simple DTS package with an Execute SQL task running a parameterized query. When I migrated it to SSIS, I found the parameter name was 0.

I modified the package I was working on to use parameter names 0 and 1 (with "exec sproc ?, ?" as the query) and it succeeded.

Don't you just love the great documentation?|||I know the documentation lacks on this. In this post http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=57637 Kirk has given us indication which Parameters can be used with which connection manager etc.

Execute SQL Task: UDF not taking parameters

Hi,

I have an Execute SQL Task in my SSIS Package.
Now, this Execute SQL Task has the following query (Connection Type is OLE DB):

Code Snippet

SELECT dbo.udf_CommonDateTime_Get (GetDate(), ?) As User_Datetime

I want 2 things from this Task:
1) It should take the 2nd argument to the UDF from a variable.
2) It should store the value returned by this SELECT statement into another variable.

So, I go ahead and modify the Parameter Mapping for the Task. Here I add the Input variable name, Data type and I give the Parameter Name as 0.

I also modify the Result Set for the Task. Here, I specify the Result Name as User_Datetime and give the appropriate Variable Name.

I am getting an error here and I believe it is due to the input parameter. The UDF is not getting the 2nd argument correctly.

My questions:
1) Has the Execute SQL Task been designed to handle UDFs like this. If not, then where am I going wrong?
2) What is the work-around for this? I need to pass a parameter (variable) to the UDF.

Thanks in advance.

Regards,
B@.ns

The error message is:

Code Snippet

Execute SQL Task: Executing the query "SELECT dbo.udf_Common_DateTime_Get (GetDate(), ?)

As User_Datetime" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Set UserDateTime

As a workaround, you could you create a new variable to store your sql statement. Set the 'EvaluateAsExpression' property of the variable to 'true'. Then set the expression like this...

"SELECT dbo.udf_CommonDateTime_Get (GetDate()," + user::VariableNameHere + ") As User_Datetime"

Then in 'SourceVariable' property of the execute sql statement to 'Variable' and then choose the variable name.

|||

Hi Martin,

Thank you for the reply. At least it gives me some hope

Unfortunately, I am getting this error:

Code Snippet

The expression for variable "varQuery" failed evaluation. There was an error in the expression.

If I remove the user::VariableNameHere part, it works fine...

Any ideas?

Thanks again.

Regards,

B@.ns

|||

You replaced user::VariableNameHere with the actual name of your variable correct?

|||

Martin,

It worked!!

Thank you so much!!

I had to do this:

Code Snippet

"SELECT dbo.udf_CommonDateTime_Get(GETDATE(), " + (DT_WSTR, 1) @.[User::VariableName] + ") As UserDateTime"

The only thing worries me is that @.[User::VariableName] can be NULL.

I will have to handle that.

Thanks again.

Regard,

B@.ns

Execute SQL Task: String or binary data would be truncated

Hi!

I try to execute SQL Task with simple statement

insert T (comment) values (@.Comment)

where comment is varchar(1000). I map package variable User::Comment (type string) to parameter @.Comment in SQL Task properties. But when length of User::Comment greater than 10 characters SSIS returns errors:

Error: 0x0 at Execute SQL Task 1: String or binary data would be truncated.

Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "insert T (comment) values (@.Comment)" failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The similar messages from this forum suggest using the OLEDB connection. I try this method too, but get the same result.

Error: 0x0 at Execute SQL Task 1: String or binary data would be truncated.

Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "insert T (comment) values (?)" failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What is it? Is this a bug, I made a mistake or I have SQL Server old version?

I have SQL 2005 (Developer Edition) 09.00.3054

TIA, Max.Aren't you missining the 'INTO' in the insert statement?

|||

Rafael Salas wrote:

Aren't you missining the 'INTO' in the insert statement?

That's an optional clause, technically, but it can't hurt to try it. Maybe the Execute SQL Task is a bit more rigid.

Execute SQL Task: Error

I am running a Execute SQL Task which runs a script on a table. It gives me following error:

[Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

Thanks,

Your query in the execute sql task is returning more than one row. Pretty much just what the error states.

If you don't need to return the results of the query to a variable, select "none" instead of "single row" in the ResultSet box. If you do in fact need to return more than one row into a variable, you'll need to select "Full Result Set." The variable you populate with single row needs to match (or be able to cast) the data type of the returned value. If you are using "Full Result Set" you'll need to use a variable of "object" data type and then enumerate through that variable using a foreach loop.|||http://msdn2.microsoft.com/en-us/library/ms141003.aspx

Execute SQL task with update statement

I am running an update statement in an execute sql task, it will run one time but then fails after that. Whats going on?

Here is my query I'm running.

UPDATE encounter
SET mrn = r.mrn, resourcecode = r.resourcecode, resgroup = r.resgroup, apptdate = r.apptdate, appttime = r.appttime
FROM SCFDBWH.SigSched.dbo.EncounterNARaw AS r INNER JOIN
encounter ON encounter.encounter_number = r.Encounter

What I'm doing is pulling info from a flat file, inserting it into the encounter table, I then run this sql statement to pull additional info from another table on another server, and update the encounter table with the corresponding info. Pretty straight forward. But what is really getting me is that the package will run 1 time but if I wait ten minutes and try running it again it bombs. Any ideas?

Please provide any error messages. That usually helps us help you.|||Error: OLE DB provider "SQLNCLI" for linked server "SCFDBWH" returned message "Login timeout expired".

Error: OLE DB provider "SQLNCLI" for linked server "SCFDBWH" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

[Execute SQL Task] Error: Executing the query "UPDATE encounter SET mrn = r.mrn, resourcecode = r.resourcecode, resgroup = r.resgroup, apptdate = r.apptdate, appttime = r.appttime FROM SCFDBWH.SigSched.dbo.EncounterNARaw AS r INNER JOIN encounter ON encounter.encounter_number = r.Encounter " failed with the following error: "Named Pipes Provider: Could not open a connection to SQL Server [5]. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

This is what the SSIS package says. I'm not sure how helpful it is but I can run the update statement in management studio all day long and it works great. It just bombs through the SSIS package.

|||

Seems as though the package is having problems talking to the server, "login timeout expired"

|||

I have all the timeouts settings set to 60 seconds, which should be plenty of time, one would think. When it does work it runs real fast-less than 4 seconds.

The main frustration is that it will work 1 time when I do something to the query but then it doesn't work again.