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

I want create a Incrementing row through a variable in Derived Column Transmition,For that i created a variable called 'Increment'..in the derived column iam creating a new column with this expression

@.[User::INCREMENT] == @.[User::INCREMENT] + 1

Is anything wrong with this statement..bcoz iam getting 0 or the value which i assigned (1000)to all the rows.

Can you pls suggest some solution ?

Niru

Yeah, that won't do what you want. if you want to generate contiguous IDs then look here: http://www.sqlis.com/default.aspx?37

-Jamie

|||Thanks for the quick reply Jamie.

If I have 4 incrementing fileds in a table ,can I create 4 variables with in a single script and map those variables to respective fileds (Or) I have to create seperate script for each and every variable?

Thanks
Kumar
|||

You can do it all in one script.

-Jamie

sql

Problem with IF

It is a procedure that does the paging on the friends table, the variable @.PageWay is the ordering that the return table have to appear, ASC or DESC.

Why it's accusing error on if clause? "if(@.PageWay= 1)"

createProcedure teste2

@.user_idint,

@.PageIndexint,

@.PageSizeint,

@.PageOrdervarchar(10),

@.PageWaybit

As

Begin

Declare @.FirstRowint,

@.LastRowint,

@.Recordsint,

@.Paginasfloat,

@.Pagesint

Select @.FirstRow=( @.PageIndex- 1)* @.PageSize+ 1,

@.LastRow= @.PageSize+(@.PageIndex- 1)* @.PageSize;

With invitationas

(

Select*,

Row_Number()over(orderby friend_idasc)as RowNumber

from friendswhere [user_id]=(@.user_id)and invited=(1)

)

if(@.PageWay= 1)

Begin

Select*from invitationwhere RowNumberbetween @.FirstRowand @.LastRoworderby

casewhen @.PageOrder='creation'then creationendasc,

casewhen @.PageOrder='e_mail'then e_mailendasc

End

else

Begin

Select*from invitationwhere RowNumberbetween @.FirstRowand @.LastRoworderby

casewhen @.PageOrder='creation'then creationenddesc,

casewhen @.PageOrder='e_mail'then e_mailenddesc

End

Set @.Records=(SelectCount(*)as'amigos'From friendswhere [user_id]=(@.user_id)and invited=(1))

Set @.Paginas=(Convert(Float,@.Records)/Convert(Float,@.PageSize))

Set @.Pages=Ceiling(@.Paginas)

return @.Pages

End

Go

Thank you very much.

I think as you must put a semicolon before you start WITH statement, you must put something after.|||

I discovered the error. After you define CTE you must use it in the next statement, otherwise you will got a message error. So I just put the following query before the If statement "Select Count(*) from invitation", and worked just fine.

There is the message when you put a Select query that not use the CTE,

Msg 422, Level 16, State 4, Procedure teste2, Line 30

Common table expression defined but not used.

Thank you very much, for had seemed my post.

Problem with FILENAME as @variable

Hi.

I am trying to create a database based on variables from xp_regread.
Somehow I cant pass @.dBDir as FILENAME.

I get the following error-message :
Server: Msg 170, Level 15, State 1, Line 24
Line 24: Incorrect syntax near '@.dBDir'.
What am I doing wrong ?
I think the problem is somehow related to a datatype-problem or
it is simply not possible to pass a variable to a "create
database"-statement.

I have checked the xp_regread-output...naturally :) ...and it is correct.
Hope someone can help :)

regards
Michael

------------
Source-code is as follows:

USE master
GO

DECLARE @.dBDir nvarchar(128)
DECLARE @.dBlog nvarchar(128)

EXECUTE xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\School-Project\IRPF
Database\','dBDir',@.param = @.dBDir OUTPUT

SET @.dBDir =@.dBDir+'\IRPF.mdf'
SET @.dBlog =@.dBDir+'\IRPF.ldf'

CREATE DATABASE IRPF
ON PRIMARY
( NAME = IRPF_db,
FILENAME = @.dBDir)
LOG ON
( NAME = 'IRPF_log',
FILENAME = @.dBlog)
GOMichael (agentmikie@.cFJERNDETTE.dk) writes:
> I get the following error-message :
> Server: Msg 170, Level 15, State 1, Line 24
> Line 24: Incorrect syntax near '@.dBDir'.
> What am I doing wrong ?
> I think the problem is somehow related to a datatype-problem or
> it is simply not possible to pass a variable to a "create
> database"-statement.

The latter. You will have to use dynamic SQL. The short story is:

SELECT @.sql = 'CREATE DATABASE IRPF ON PRIMARY ( NAME = ''IRPF_db'', ' +
' FILENAME = ' + quotename(@.dBDir) + ' LOG ON ( NAME = ' +
''IRPF_log'', FILENAME = ' + quotename(@.dBlog) + ')')
EXEC (@.sql)

The feature demonstrated here, is one to be used with care. For this
kind of thing, it is the right thing, but there are many cases where
dynamic SQL is not the answer. So there is a full story on
http://www.sommarskog.se/dynamic_sql.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> The latter. You will have to use dynamic SQL. The short story is:
> SELECT @.sql = 'CREATE DATABASE IRPF ON PRIMARY ( NAME = ''IRPF_db'', ' +
> ' FILENAME = ' + quotename(@.dBDir) + ' LOG ON ( NAME = ' +
> ''IRPF_log'', FILENAME = ' + quotename(@.dBlog) + ')')
> EXEC (@.sql)
> The feature demonstrated here, is one to be used with care. For this
> kind of thing, it is the right thing, but there are many cases where
> dynamic SQL is not the answer. So there is a full story on
> http://www.sommarskog.se/dynamic_sql.html.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Thank you for your reply.

The link seems to be exactly what I was looking for. :)

Regards
Michael.

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

I am try to populate a table based on a variable (it holds the table name).
I have managed to create the table using the EXEC command but am having
problems populating it with the following command. I receive "Error 156:
Incorrect syntax near the keyword 'CONVERT'". Could anyone offer a solution
.
Thanks.
BEGIN
EXEC ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
CONVERT(DATETIME, @.EndDate, 102) + ')')
END
However the following works fine into a regular table.
BEGIN
INSERT INTO SubsList (SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = @.PubCode) AND (DateEntered >= CONVERT(DATETIME,
@.StartDate, 102)) AND (DateEntered <= CONVERT(DATETIME, @.EndDate, 102))
ENDHi pete
try this:
BEGIN
EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
CONVERT(DATETIME, @.EndDate, 102) + ')')
END
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:

> I am try to populate a table based on a variable (it holds the table name)
.
> I have managed to create the table using the EXEC command but am having
> problems populating it with the following command. I receive "Error 156:
> Incorrect syntax near the keyword 'CONVERT'". Could anyone offer a soluti
on.
> Thanks.
> BEGIN
> EXEC ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
> CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
> CONVERT(DATETIME, @.EndDate, 102) + ')')
> END
>
> However the following works fine into a regular table.
> BEGIN
> INSERT INTO SubsList (SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = @.PubCode) AND (DateEntered >= CONVERT(DATETIME,
> @.StartDate, 102)) AND (DateEntered <= CONVERT(DATETIME, @.EndDate, 102))
> END|||Thanks Chandra but still no joy.
Now getting "Error 170: Line 125: Incorrect syntax near 'INSERT INTO'"
"Chandra" wrote:
> Hi pete
> try this:
> BEGIN
> EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
> CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
> CONVERT(DATETIME, @.EndDate, 102) + ')')
> END
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Pete" wrote:
>|||Hi pete
try now:
EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
CONVERT(DATETIME,'+ @.EndDate +', 102)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> Thanks Chandra but still no joy.
> Now getting "Error 170: Line 125: Incorrect syntax near 'INSERT INTO'"
>
> "Chandra" wrote:
>|||Hi pete
just observed and found that, "(" was missing before "DateEntered"
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> Thanks Chandra but still no joy.
> Now getting "Error 170: Line 125: Incorrect syntax near 'INSERT INTO'"
>
> "Chandra" wrote:
>|||'Fraid not. Same error.
"Chandra" wrote:
> Hi pete
> try now:
> EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
> CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
> CONVERT(DATETIME,'+ @.EndDate +', 102)
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Pete" wrote:
>|||my mistake:
EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
CONVERT(DATETIME,'+ @.EndDate +', 102)'
i missed a ' at the end.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> 'Fraid not. Same error.
> "Chandra" wrote:
>|||Nope :-(
"Chandra" wrote:
> Noe then,
> this should work
> EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
> CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
> CONVERT(DATETIME,'+ @.EndDate +', 102)')
> too many quotes and brackets, these are confusing..
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Pete" wrote:
>|||Why don't you try printing your query instead of executing it?
Then you'll see the error yourself. With what you're doing
now, you make a change, ask the parse "is this right yet?", and
listen to the parser's particular complaint, over and over.
If you've never looked at the query yourself, are you going to
assume it's correct once the parser stops complaining about it?
Once you get it right, then you can execute it instead of print
it.
Steve Kass
Drew University
Pete wrote:
>Getting closer..
>The following gives "Syntax error converting datetime from character string
"
>SET @.SqlString = N'INSERT INTO ' + @.NewSubsList + ' (SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND (DateEntered >= ' +
>CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
>CONVERT(DATETIME, @.EndDate, 102) + ')'
>EXECUTE sp_executesql @.SqlString
>
>"Pete" wrote:
>
>|||hi
or do one thing,
first create the string that you want to execute.
just see if it clear in the syntax. and then pass it to sp_executesql
does this make sence.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> Nope :-(
> "Chandra" wrote:
>