Problem with logging/System::ErrorDescription variable

Hi,

I've come up against a problem in my error handling.

I have a package-level OnError handler which is a SQL Execute task. The SQL execute task constructs a SqlStatementSource expression to insert the error details into a table. This works fine and I've tested it by introducing various errors in the package.

I have another SQL Execute task, which executes a SQL script from a file connection. This is a very long script (100s of lines) and works fine. I introduced a deliberate error into it and got the following errors:

Error: The variable System::ErrorDescription contains a string that exceeds the maximum allowed length of 4000 characters.
Error: Reading the variable "System::ErrorDescription" failed with error code 0xC0047100.
Error: The expression <expression> on property "SqlStatementSource" cannot be evaluated. Modify the expression to be valid.

The problem is obviously the length of the script, since SSIS attempts to copy all of it into the ErrorDescription, but this seems to occur before I can do anything about it. I've tried putting a SUBSTRING function around the ErrorDescription variable without success. If I introduce an error into a shorter script the error handling works fine.

Is this a bug in SSIS? Is there any sort of workround that anybody knows of?

thanks
- Jerzy

It sounds like it could be a bug. It needs validating by someone from the SSIS dev team but unfortunately they don't seem to appear on here as much as they used to.

Try logging it at the feedback center with repro steps: http://lab.msdn.microsoft.com/productfeedback/default.aspx

-Jamie

|||Thanks. I've done that. Bug ID FDBK45894

- Jerzy
|||Sounds a bit funny, but there is a known limit of 4000 characters on expressions, so for long errors you may hit this. The other issue you can hit is that the message may contain single or double quotes. Best case, it fails, worst case you have a SQL injection attack. For this reason alone I'd say just use a stored procedure, and the built in parameter support. The ErrorDescription variable should map to a procedure parameter, which should solve both the size limit (if that is your issue), and prevent injection.

Problem with Group By

Hi there,

I am a novice SQL Server Programmer. I have got a task to select multiple fields from multiple tables...I have successfully used joins to get the result. but I have got 2 questions

1. Is there any way of combining (concatenating) three strings as one in the query using "AS"

2. The final result had to be GROUPED by one particular field I am using in select statement...But, for every field in select statement, it lets me run the query without error, only if the field is included Group By clause.

Is there any way to avoid it?

yes, you can concatenate multiple strings into a single field with as...

select (f1+ f2 + f3) as singleString

and you to include any fields in the query in the group by clause...just put the field you are concerned with first. -- jp

|||

hi jp

Thanks for your reply...

your first answer fetched me correct results but regarding the Group By, I still have the problem :(

|||when you use group by you have to include all the fields from the query statement in the group by clause...|||

thank you jp..............

Problem with Failure Constraint

Hi,

As part of my SSIS package I have a script task, that set its result based on the value of a package variable. From this task I have two precedence constraints, a sucess constrain and failure constraint that lead to two different tasks.

When the script task ends with a success result, the task that is connected by the success constraint is initiated, but when the script task ends with a failure result, it is marked with red color and the execution of the whole package stops (the next task that is connected by the failure constraint is not initiated).

All tasks are assigned with False to the properties: FailPackageOnFailure & FailParentOnFailure

Please assist.

Hmmmm....strange!

Can you post the contents of the .dtsx file up here so we can repro?

If you could take out all external references (e.g. connection managers, configurations, etc...) it'd be a big help!

Thanks

Jamie

|||Is there a way to attach the file?|||Have you increased MaxErrorCount property? (I don't remember the details of interactions between it and other two properties, but try increasing it).|||

Thanks for you help.

I believe I solved the problem: the task, that was connected to the script task by the failure constraint, could be executed also as an error handling task of another task, therefore all faliure constraints should have LogicalAnd property set to False.

Problem with Execute SQL Task

I am having problems creating an "Execute SQL Task" which calls a stored procedure.

I have tested the procedure successfully using parameters that I have hardcoded on the command line (i.e., EXEC procedure_name 1, 2). This works fine, but I'm having problems using variables(i.e., EXEC procedure_name @.VAR1, @.VAR2). I'm using a ConnectionType of OLE DB.

When I parse the Query I get an error message that says "The query failed to parse. Must declare the variable '@.VAR'".

However, I have this variable declared and assigned a value. I have played around the Parameter Mapping pane but I'm not getting anywhere.

Can anyone shed some light on this particular problem and how I go about fixing this?

thanks

John

John,

Place 'EXEC procedure_name ?, ?' as the SQL Statement. Then, under Parameter Mapping, add a variable select User::Var1 (or whatever the name of Var1 is) as the variable name and enter '0' (zero) as the Parameter Name. Add another variable, select User::Var2 as the variable name and enter '1' as the Parameter Name. That should do it.

Also, I've never been able to parse a query with parameters in it.

Eric

|||I could not reproduce your problem. However, I could use "Execute SQL task" to execute a stored procedure by passing a variable as the input parameter.

I had sp_GetDetails in my db that took one input (varchar type). I created a variable called "inputVal" and assigned a value to it. In my "Execute SQL task", I had "Execute sp_GetDetails @.inputValParam" as my SQLStatement. I also created a mapping between inputVal and inputValParam using "Parameter Mapping" in my task. This task works if the connection type is ADO.Net. If I change that to OLE DB, it does not work.|||Thank you so much. This has been very frustrating at best. I don't think some of these transform tasks are that intuitive at all...|||How would you pass a mix of variables and hardcoded values (i.e., @.var1, @.var2, null, null, 2, "test") without using the parameter mapping?|||

Simply use "EXEC procedure_name ?, ?, 1, NULL, 'Yes', ?, ?". The question marks serve as placeholders for the parameters which you are going to map on the 'Parameter Mapping' page. The parameter list is a 0-based array. So your parameter names will be 0, 1, 2, 3, ..., n respectively, and they're placed into the SQL in the order they're named, so the first '?' corresponds to parameter 0, the second '?' corresponds to parameter 1, etc.

So, if I have User::var1 with a value of 'A' mapped to 0, User::var2 = 'B' -> 1, User::var3 = 'Jim' -> 2, User::var4 = 'Bob' -> 3 the SQL sent via the SQL task would be "EXEC procedure_name 'A', 'B', 1, NULL, 'Yes', 'Jim', 'Bob'".

Hope that doesn't confuse things more.

Eric

|||

Kaarthik,

Kirk has a useful post which may help you here: http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

-Jamie

|||Thanks for the great info. I'll see if I can get this to work for me. Great help again ...

Problem with Execute Process Task

Hi,

I have what should be a very simple process.

I have a FOREACHLOOP container, that is set up to loop through files in a directory:

Enumerator: Foreach File Enumerator

Folder: c:\myfolder

Files: *.txt

Retrieve: Name and extension

Variable mappings: User::file_name, Index 0

So far, so good...

Then, inside the FOREACHLOOP, I have an Execute Process Task, which is simply a .bat file that I am running, and passing the name of the file to:

For example: myBat.bat file_name

In the Execute Process Task, I have:

RequireFullFileName: false

Executable: c:\myFile\myBat.bat

WorkingDirectory: c:\myFile

StandardInputVariable: User::file_name

Everything else is set to the defaults.

However, when I execute my container, I get the following error:

Error: 0xC0029151 at Execute Process Task, Execute Process Task: In Executing "c:\myFile\myBat.bat" " " at "c:\myFile", The process exit code was "1", while the the expected was "0".

Is there anything I am obviously doing wrong here at the package level? When I run the .bat file from the command line, it works fine.

Thanks

Even when I remove the Standard Input Variable, and just put file name as the Argument, I still get an error. This is just running the Execute Process Task, not the loop, for one file only.

|||

Ok, I set the SucessValue = 1, instead of 0, and now it's running ok with the single file name argument.

However, when I change it back to the StandardInputVariable with the User::file_name, it doesn't run the batch file correctly, that is the batch file does not work as expected, which makes me wonder what the User::file_name variable looks like?

I set a breakpoint, but how can I see my watch variable?

Thanks!

|||Type User::file_name into the watch window.|||In the execute process task, if the processes uses command line arguments, as in
some_executable.exe arg1
or
some_batch_file.bat arg2

use the Arguments property.

If the process to execute is properly invoked as below, then it is expecting and will use the standard input file stream, not a command line argument, so use the StandardInputVariable property. Note setting the StandardInputVariable to the "User::file_name" is streaming in the name of the file, NOT the contents of the file (as in the echo "x" case below).

echo "x" | some_executable.exe

If the process expects both command line arguments and standard input, as follows:

echo "x" | some_executable.exe arg1

then use both task properties as well.

problem with DTS package

have a DTS package which utilizes an "Execute Process
Task". This task calls a batch file on my server. I keep getting this error trying to run the DTS from sqlserver:

"CreateProcessTask 'DTSTask_DTSCreateProcessTask_1': Process returned code -1, which does not match the specified SuccessReturnCode of 1:

Does anyone know what this error mean? TIAWhat is the level of service pack on SQL & OS?|||Possibly the batch job you are calling is producing an error. Try executing your batch job from a command window and inspect its result.

kbk|||Also enable DTS package logging for more information on this behaviour.