Determine Previous Task Executed

I have the failed path of many tasks pointing to a single Script Task that logs a failed entry into my database. In this script task, I need to be able to determine the task that faliled as I need to write a unique error message depending on the task that failed.

In my VB script, how would I determine which task was the one that failed?

Thank You!!!

a SSIS newbie

There is no way I can see to do that.

Have you investigated the logging available in SSIS before you write your own?

If you do want a task for additional error handling work, then try the on error event handler. Event handlers can host an entire workflow of tasks if you want, and are a better way of capturing this type of event compared to workflow constraints. You have some system variables scoped to the event handler that should give you all the information you want.

|||What I do is add a derived column to each error flow. Then in that derived column, I add a new column, ErrorReason (or whatever you like), which is where I store a small string of what the error was. So then in your script component, you could look at that column and act accordingly.

However, it sounds like you can do all of this outside of a script component. (You are inside the data flow, correct? I assume so because you talk of "failed paths.") If you're talking about a failed precedence constraint, then I'll defer to Darren above.|||

Hi AspUser123,

I agree with Darren.

Recently, I implemented a custom error logging mechanism for a client using the onError Event Handler. They wanted the package to continue executing, but capture information about each fault.

Since the Error events bubble, I implemented the entire custom logging solution in the Package onError Event Handler. You can use the SourceName system variable to identify the name of the task that originally generated the error - even if it's nested inside several layers of containers. You can also collect information such as the ErrorDescription and ErrorCode (error number).

In this case I ended up writing the information out to a SQL Server database for storage, reporting, and analysis - but you could easily implement a flat file output. You can use an Execute SQL Task (calling a stored procedure) to write to SQL Server, or add a Data Flow to the onError Event Handler to store the system variable values to a flat file destination.

Hope this helps,
Andy

|||I agree with Darren.

Details of SubscriptionClass Class

I just have a question around the SubscriptionClass class. I'm not grasping why there would be multiple rules for a single SubscriptionClass. Can someone give me a good use case for it? It seems to me that since the primary use of Rules are to generate Notifications from Subscriptions. The only reason I can see then is if we used multiple Rules to generate different Notifications. However, then I would assume the Subscription class to be associated with the Subscriber and Notification, not the Subscriber and SubscriptionClass.

A much-related question is: Why is the SubscriptionEventRules (and the SubscriptionConditionEventRules, etc.) a collection? While maybe I could understand being able to choose from a SubscriptionEventRule and a SubscriptionConditionEventRule, I don't understand the multiple SubscriptionEventRules. (And even in the other case, the Subscription doesn't seem to specify a particular rule association).

One final thought is the possibility that the design is for the Subscriber to subscribe to a particular SubscriptionClass and receive all Notifications associated with the SubscriptionClass. Is that the case? If anyone could clarify this for me, it'd be much appreciated.

-Francis

Note that I did see the RuleName property of the Subscription class, but in the SDK it says it's for rules with condition actions.

Desperate for help.......

I want to incorporate fields from 2 tables in a single crystal report.I have no problems in incorporating from a single table. But when it comes to >1 table fields in a single report, I am stuck up..

For eg.:

Table 1 :"GENINFO"
-------
custno | mail id
--------

Table 2 :"DETAILS"
--------
custno | Listofcompaniesowned
----------

Now, I would like to write the RECORDSELECTIONFORMULA as part of the code,without using the editor.

I intend the user to choose the customer he wants to see, based on a customer number that he will provide.

Report.DataDefinition.RecordSelectionFormula = "{GENINFO.custno}=" &i
[i is a variable which takes userinput for customerno.]

Report.DataDefinition.RecordSelectionFormula = "{DETAILS.custno}=" & i

This doesnot work...
The first problem is how to couple these both...tables in the formula.

I used "And". ...doesn't work..

The second problem is that I want only custno field from GENINFO table to be part of my report.The custno field of DETAILS table is just meant for selecting the other fields in the table such as "listofcompniesowned" etc...
----------------
CrysReport1.rpt
----------------
custno | GENINFO.CUSTNO
mailid | DETAILS.MAILID
listofcompniesowned | DETAILS.LISTOFCOMPNIESOWNED
----------------
----------------

I tried dataset for this...by creating xsd file, but it gives me a "query engine error"......in a "c:/......./......./temp/......................rpt file".

I could not understand what it is this error all about ??

In general, please guide me as to the ways of using more than 1 table in a single report?//

Fast Replies welcome.

Thanks for the time.First of all, why are you trying to control the whole record selection formula from your VB.NET code? Why don't you just pass the value of "i" as a parameter to the CrystalReport and then your record selection formula would be something like this:

{GENINFO.custno}= <?i>

As far as linking the tables you should do that in your CrystalReport under Database>Visual Linking Expert. If you are only pulling data from 2 tables there really is no need to use datasets. DataSets make your report slower.

In many of my reports I pass all kinds of parameters like that and it works just fine.

Desparately need help parsing field

Hello,

I have to split data that is currently in a single field delimited by a space.

Problem is the field could be between 7 and 72 bytes long.
I have to parse the field for a character set and its corresponding number.
For instance: MyColumn = Index1 50 Index2 40 Index3 10

I need to parse this out to:

INDEX PERCENT
-------
Index1 50
Index2 40
Index3 10

Again, there may exist 1-4 sets of this data within the field.

Anyone have a stored proc or better T-SQL ?

Thanks so much in advance!

mzimmIs this a 1 time deal or will it repeat ? How many records exist ? How is the data currently stored ? What are the different combinations of datasets - please provide examples ?|||Originally posted by rnealejr
Is this a 1 time deal or will it repeat ? How many records exist ? How is the data currently stored ? What are the different combinations of datasets - please provide examples ?

Hello,

This will repeat many times.
Upwards of 1000 records exis t in this table.
The datasets will be in combination of
(Example) Index1 5 Index2 10 ... Indexn ##
where Indexn can be any number of characters long and ## is between 1 and 100. The characters and numbers are separated by a space.

Actual example:

Column1 = Lehman 100
Column2 = lehIUSGV 5 LehGVcr 10 MSCIEAFE 5 R2000Gro 5 RMCAPGro 10
Column3 = LehCredt 10 Lehmktgov 15 DnB 35 S&P 40

Thanks much for your help!

mzimm|||These records can have 1 combination of string and number up to n strings and n numbers. You reference the records as columns - how many columns exist - or are these actually records. Can you provide a snapshot of this table in em or qa and post it to help clear up the database design question ?|||Originally posted by rnealejr
These records can have 1 combination of string and number up to n strings and n numbers. You reference the records as columns - how many columns exist - or are these actually records. Can you provide a snapshot of this table in em or qa and post it to help clear up the database design question ?

I GREATLY aplogize, it's late.
Those are RECORDS, only 1 column.|||This is the first solution that came to mind (so there might be better ones). Use dts to "export" the data to a delimited file - use vbscript to transform the data (basically breaking up the space delimited records into sets of individual lines based on the spacing sequence). Then import that file into sql server into the appropriate table. If you need the vbscript for the dts part let me know and I will post.|||Originally posted by rnealejr
This is the first solution that came to mind (so there might be better ones). Use dts to "export" the data to a delimited file - use vbscript to transform the data (basically breaking up the space delimited records into sets of individual lines based on the spacing sequence). Then import that file into sql server into the appropriate table. If you need the vbscript for the dts part let me know and I will post.

Yes, Please, can you post the VBscript?
It would be thee answer to my problem.

Thanks again.|||Here you go (Let me know if you need additional help):

Function Main()
strString = DTSSource("b")
intTest = 1
intBegin = 1
while intTest <> 0
intTest = instr(intTest , strString, " ")
strFinal = strFinal & mid(strString, intBegin, intTest - intBegin) & " "
intTest = intTest + 1
intBegin = intTest
intTest = instr(intTest, strString, " ")
if intTest = 0 then
strFinal = strFinal & right(strString, len(strString) - intBegin + 1) & " "
else
strFinal = strFinal & mid(strString, intBegin, intTest - intBegin) & vbCrLf
intTest = intTest + 1
intBegin = intTest
end if
wend
DTSDestination("b") = strFinal
Main = DTSTransformStat_OK
End Function

Design Question - Tables/Foreign Keys with Potential Arrays of Dat

Hello,
I have a situation where I have a table that may depend on no, one, or
multiple rows of another table for a single Transaction ID, and I'm not sure
how to design it.
Here is a simpler version of my schema:
CREATE TABLE MyTransactionTable
(
ID INTEGER IDENTITY NOT NULL,
Name VARCHAR(40),
SomeData INTEGER,
TransType INTEGER,
CONSTRAINT PK_TransTable PRIMARY KEY(ID),
CONSTRAINT FK_TransTableType(TransType) REFERENCES TransactionTypes(ID),
CONSTRAINT FK_SomeDataTable(ID) REFERENCES SomeDataTable(ID)
)
CREATE TABLE TransactionTypes
(
ID INTEGER IDENTITY NOT NULL,
Description VARCHAR(20) NOT NULL,
Priority INTEGER DEFAULT 0,
CONSTRAINT PK_TransactionTypes PRIMARY KEY (ID)
)
CREATE TABLE SomeDataTable
(
ID INTEGER IDENTITY NOT NULL,
SomeData VARCHAR(50) NOT NULL,
CONSTRAINT PK_SomeDataTable PRIMARY KEY (ID)
)
The problem is, I want a single row in MyTransactionTable that references 0,
1, or more rows in the SomeDataTable.
Thanks,
PAGatesWhy do you "...want a single row in MyTransactionTable that references 0, 1,
or more rows in the SomeDataTable"? That's not a normalized design.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"pagates" <pagates@.discussions.microsoft.com> wrote in message
news:722D2991-CAC4-4F02-BBAE-DDCFF1DE763C@.microsoft.com...
Hello,
I have a situation where I have a table that may depend on no, one, or
multiple rows of another table for a single Transaction ID, and I'm not sure
how to design it.
Here is a simpler version of my schema:
CREATE TABLE MyTransactionTable
(
ID INTEGER IDENTITY NOT NULL,
Name VARCHAR(40),
SomeData INTEGER,
TransType INTEGER,
CONSTRAINT PK_TransTable PRIMARY KEY(ID),
CONSTRAINT FK_TransTableType(TransType) REFERENCES TransactionTypes(ID),
CONSTRAINT FK_SomeDataTable(ID) REFERENCES SomeDataTable(ID)
)
CREATE TABLE TransactionTypes
(
ID INTEGER IDENTITY NOT NULL,
Description VARCHAR(20) NOT NULL,
Priority INTEGER DEFAULT 0,
CONSTRAINT PK_TransactionTypes PRIMARY KEY (ID)
)
CREATE TABLE SomeDataTable
(
ID INTEGER IDENTITY NOT NULL,
SomeData VARCHAR(50) NOT NULL,
CONSTRAINT PK_SomeDataTable PRIMARY KEY (ID)
)
The problem is, I want a single row in MyTransactionTable that references 0,
1, or more rows in the SomeDataTable.
Thanks,
PAGates|||pagates wrote:
> CREATE TABLE SomeDataTable
> (
> ID INTEGER IDENTITY NOT NULL,
> SomeData VARCHAR(50) NOT NULL,
> CONSTRAINT PK_SomeDataTable PRIMARY KEY (ID)
> )
> The problem is, I want a single row in MyTransactionTable that references
0,
> 1, or more rows in the SomeDataTable.
>
Add the Transaction ID to SomeDataTable as a foreign key.
As specified your table design is weak because you don't have any
alternate keys. IDENTITY is not a uniqueness constraint and it
shouldn't be the only key of a table.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||First you need relational keys instead of IDENTITY columns. You also
need to read a book on data modeling, so you will quit using vague data
element names. I will make a guess that transactions are really keyed
by a timestamp and a user id of some kind.
CREATE TABLE MyTransactions
(trans_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
user_name VARCHAR(40) NOT NULL,
foobar_id INTEGER NOT NULL
REFERENCES SomeData(foobar_id),
trans_type INTEGER NOT NULL
REFERENCES TransactionTypes(trans_type),
PRIMARY KEY (trans_timestamp, user_name)
);
CREATE TABLE TransactionTypes
(trans_type INTEGER NOT NULL PRIMARY KEY,
trans_type_description VARCHAR(20) NOT NULL,
trans_priority INTEGER DEFAULT 0 NOT NULL
CHECK (trans_priority >= 0));
CREATE TABLE SomeData
(foobar_id INTEGER NOT NULL PRIMARY KEY,
vague_stuff VARCHAR(50) NOT NULL,
.);
1, or more rows in the SomeDataTable. <<
Then you need a table with this relationship. Here is one way to keep
the cardinality of the relation in the 1 to 3 range:
CREATE TABLE SomeData
(trans_timestamp DATETIME NOT NULL,
user_name VARCHAR(40),
FOREIGN KEY (trans_timestamp, user_name)
REFERENCES MyTransactions (trans_timestamp, user_name)
ON DELETE CASCADE
ON UPDATE CASCADE,
foobar_id INTEGER NOT NULL
references SomeData(foobar_id),
seq_nbr INTEGER DEFAULT 1 NOT NULL
CHECK (seq_nbr IN (1, 2, 3)),
PRIMARY KEY (trans_timestamp, user_name, seq_nbr));