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.

Design Question: Dimension made up of several Tables, skip one Level in one Hierarchy

Greetings!

I wonder if someone can give a hint such a design task:

I have a dimension that consists of several tables each chained 1-n to the next one. "Country, City, Shop" maybe. Within this dimension I build a hierarchy down this path, and it works perfectly fine. My measures add up along this path, all good.

Now, say, each Shop has a property describing the location (lets name it LocationSurroundings), like "HighStreet" or "Mall" or "Suburb".

I'd like to get a 2nd look upon this dimension, with the levels being "Country, LocationSurroundings, Shop". I do want to skip City for this view, I want to able to see all Shops that are situated in Malls for a country.

If City was part of that view, I would always get totals to city levels, but not be able to compare shops from different citys in the same country. Is there a solution to this? I need the city property in some way to connect shop to country, but I do not want it to be visible or to generate subtotals at this level...

Thanks for your ideas!

Ralf

Hello. I am not sure about the problem here. If you are using SSAS2005 you have both attribute hierarchies, with each attribute not belonging to a hierarchy,and the user hierarchies, which are the ones you describe like Country-City-Shop.

Could you not simply add Locationsurrondings as an attribute(attribute dimension) in the dimension?

Regards

Thomas Ivarsson

|||

Hi Ralf,

Please expand a bit on your problem.

As I see it you can just make a second hierarchy and make city a hidden level. In the DIM designer, create the complete hierarchy (including city), choose the hierarchy, choose the level (city) right-mouse > properties > Visible - false

But when you are talking about a view, you mean a data source view or a SQL-view.

Kind regards,

Johan Blad (also somewhere in Europe)

|||

Thanks both of you, I got it working as I need.

What I meant to acomplish were really 2 goals:

a) A hierarchy with a level missing. This can be done by chaining together the attributes as they need to be, but on the hierarchy you just leave out the one you do not want. SSAS is smart enough to understand that. I now got 2 hierarchies in this dimension, one that includes all levels and another one that skips some levels.

b) Include another attribute (here: the shop area description) into the hierarchy. This is not possible, as it has no relations. I solved that by just making it an attribute, as suggested. Works fine!

That is solved. Thanks VERY MUCH.

But another question that now came up:

I have a self-referencing dimension table (a) that contains a hierarchy of levels. The lowest level items are NOT the ones that join to the fact table... the lowest level joins to another table (b) where a lot of attributes for a complete different type or real life object are listed, and this table finally joins the fact data (c).

What I mean: Imagine a company employee hierarchy down from manager to department leaders to regular employees (that is the hierarchy, a). Now each employee has several accounts to work with (stored in the account table, b). Finally, these accounts are referenced in the fact table c.

I would not want to make this one table, an employee is a complete different thing than an account with a different set of attributes.

Whatever I try, I cannot build a dimension that contains a self-referencing table AND a normal one... is that so? Is there a way to do that? How? What? Where?

|||

As far as I know, you cannot blend a parent-child and a normal dimension.

One option would be to use a referenced dimension relationship (from "fact table c" to account to employee).

Another option would be creating a fact-less fact table that shows the relationships between employees and accounts. So employee and accounts are related to new fact EmployeeAccounts. Then create a many-to-many dimension relationship from your "fact table c" to Employees.

Third (and simplest) option - just add both dimension keys to the fact.

None of these will give you a single dimension representing Employees and accounts. But, if they are two different entities, is that really what you want to do?

Design opinion?

Hi
I am starting the task of creating a data warehouse for my company's OLTP
system and we would like to offload reporting to a reporting schema at a
data warehouse. We have 2 kinds of reports: operational (used daily and
need data just about real time) and analytical (used to determine sales
trends, etc).
For the analytical reports, I think we can just do a nightly ETL load since
the data doesn't need to be too recent. However, I was thinking of using
replication to replicate the table data used in the operational reports,
capturing the data in stored procedures as they come across the pipe and
then performing ETL within the stored procs. That way I think we can have
real time data available for reporting.
Does anyone have an opinion on this scheme?
Thank youI've done this kind of configuration before on SQL 2K. Transactional
replication is about the best you can do since log shipping takes the DB
offline. We preferred to pause replication during the staging portion of
the ETL cycle to avoid blocking and dead locking. This also gave us a
consistent point in time snap shot of the source to work from. Using TSQL
as an ETL tool is fast but has limitations around data transformation and no
access to the bulk load api.
Good luck,
Danny
"Dodo Lurker" <none@.noemailplease> wrote in message
news:BIKdneofYum6-2reRVn-pQ@.comcast.com...
> Hi
> I am starting the task of creating a data warehouse for my company's OLTP
> system and we would like to offload reporting to a reporting schema at a
> data warehouse. We have 2 kinds of reports: operational (used daily and
> need data just about real time) and analytical (used to determine sales
> trends, etc).
> For the analytical reports, I think we can just do a nightly ETL load
> since
> the data doesn't need to be too recent. However, I was thinking of
> using
> replication to replicate the table data used in the operational reports,
> capturing the data in stored procedures as they come across the pipe and
> then performing ETL within the stored procs. That way I think we can have
> real time data available for reporting.
> Does anyone have an opinion on this scheme?
> Thank you
>|||Hello,
Timing is everything.
I would work on getting your data warehouse overnight processes
completed.
In a short period of time new replication options will be available in
SQL Server 2005.
Even the existing options may meet your needs, but with the right
timing you may
have better options available to you.|||Depending on what method you use to migrate data to the warehouse (and also
the volitility of the OLTP data), this may impact the performance of the
OLTP system. Also, once the user expects analysis in real time, any
interruption will be perceived as a system malfunction, and thus your
maintenance requirements and responsilbility have increased. Do they really
need real time analysis? Also, what type of analysis; OLAP?
"Dodo Lurker" <none@.noemailplease> wrote in message
news:BIKdneofYum6-2reRVn-pQ@.comcast.com...
> Hi
> I am starting the task of creating a data warehouse for my company's OLTP
> system and we would like to offload reporting to a reporting schema at a
> data warehouse. We have 2 kinds of reports: operational (used daily and
> need data just about real time) and analytical (used to determine sales
> trends, etc).
> For the analytical reports, I think we can just do a nightly ETL load
> since
> the data doesn't need to be too recent. However, I was thinking of
> using
> replication to replicate the table data used in the operational reports,
> capturing the data in stored procedures as they come across the pipe and
> then performing ETL within the stored procs. That way I think we can have
> real time data available for reporting.
> Does anyone have an opinion on this scheme?
> Thank you
>

Design opinion?

Hi
I am starting the task of creating a data warehouse for my company's OLTP
system and we would like to offload reporting to a reporting schema at a
data warehouse. We have 2 kinds of reports: operational (used daily and
need data just about real time) and analytical (used to determine sales
trends, etc).
For the analytical reports, I think we can just do a nightly ETL load since
the data doesn't need to be too recent. However, I was thinking of using
replication to replicate the table data used in the operational reports,
capturing the data in stored procedures as they come across the pipe and
then performing ETL within the stored procs. That way I think we can have
real time data available for reporting.
Does anyone have an opinion on this scheme?
Thank you
I've done this kind of configuration before on SQL 2K. Transactional
replication is about the best you can do since log shipping takes the DB
offline. We preferred to pause replication during the staging portion of
the ETL cycle to avoid blocking and dead locking. This also gave us a
consistent point in time snap shot of the source to work from. Using TSQL
as an ETL tool is fast but has limitations around data transformation and no
access to the bulk load api.
Good luck,
Danny
"Dodo Lurker" <none@.noemailplease> wrote in message
news:BIKdneofYum6-2reRVn-pQ@.comcast.com...
> Hi
> I am starting the task of creating a data warehouse for my company's OLTP
> system and we would like to offload reporting to a reporting schema at a
> data warehouse. We have 2 kinds of reports: operational (used daily and
> need data just about real time) and analytical (used to determine sales
> trends, etc).
> For the analytical reports, I think we can just do a nightly ETL load
> since
> the data doesn't need to be too recent. However, I was thinking of
> using
> replication to replicate the table data used in the operational reports,
> capturing the data in stored procedures as they come across the pipe and
> then performing ETL within the stored procs. That way I think we can have
> real time data available for reporting.
> Does anyone have an opinion on this scheme?
> Thank you
>
|||Hello,
Timing is everything.
I would work on getting your data warehouse overnight processes
completed.
In a short period of time new replication options will be available in
SQL Server 2005.
Even the existing options may meet your needs, but with the right
timing you may
have better options available to you.
|||Depending on what method you use to migrate data to the warehouse (and also
the volitility of the OLTP data), this may impact the performance of the
OLTP system. Also, once the user expects analysis in real time, any
interruption will be perceived as a system malfunction, and thus your
maintenance requirements and responsilbility have increased. Do they really
need real time analysis? Also, what type of analysis; OLAP?
"Dodo Lurker" <none@.noemailplease> wrote in message
news:BIKdneofYum6-2reRVn-pQ@.comcast.com...
> Hi
> I am starting the task of creating a data warehouse for my company's OLTP
> system and we would like to offload reporting to a reporting schema at a
> data warehouse. We have 2 kinds of reports: operational (used daily and
> need data just about real time) and analytical (used to determine sales
> trends, etc).
> For the analytical reports, I think we can just do a nightly ETL load
> since
> the data doesn't need to be too recent. However, I was thinking of
> using
> replication to replicate the table data used in the operational reports,
> capturing the data in stored procedures as they come across the pipe and
> then performing ETL within the stored procs. That way I think we can have
> real time data available for reporting.
> Does anyone have an opinion on this scheme?
> Thank you
>

Design of Scheduled Task data schema

I am designing a table structure for scheduled tasks. Tasks must be
scheduled either on a fixed interval or at specific times. Those specific
times can be either every day or specific days of every w.
I have come up with the plan I'll paste in here, but the NULL's and one
field telling how to interpret data from another table leave me suspecting
there's a better way. I'm designing this from scratch so am free to change
as needed.
Suggestions?
Use Pubs
Create Table SchedTask(
TaskCode nvarchar (6) not null Primary Key,
TaskDescription nvarchar (30) ,
ScheduleType char(1) not null Check ( ScheduleType in ('I', 'D',
'W')), --for Interval, Daily, Wly
ScheduleInterval SmallInt NULL
)
go
Create Table SchedDay(
TaskCode nvarchar (6) Foreign Key References SchedTask (TaskCode),
DayOfW TinyInt not null Check ( DayOfW Between 0 and 7), --0
indicates it doesn't matter, 1-7 map to Sunday-Saturday
TimeOfDay SmallDateTime not null
)
go
--Add a task to repeat every 10 minutes
Insert Into SchedTask (TaskCode, TaskDescription, ScheduleType,
ScheduleInterval)
Values ('10M', 'Repeat every 10 minutes', 'I', 10)
--Add a task to occur at 9:00 AM every day
Insert Into SchedTask (TaskCode, TaskDescription, ScheduleType,
ScheduleInterval)
Values ('9D', 'occur at 9:00 AM every day', 'D', NULL)
Insert Into SchedDay (TaskCode, DayOfW, TimeOfDay)
Values ('9D', 0, '9:00')
--Add a task to occur at 9:00 AM every Monday and 11:45 every Friday
Insert Into SchedTask (TaskCode, TaskDescription, ScheduleType,
ScheduleInterval)
Values ('2W', 'Do this twice a w', 'W', NULL)
Insert Into SchedDay (TaskCode, DayOfW, TimeOfDay)
Values ('2W', 2, '9:00')
Insert Into SchedDay (TaskCode, DayOfW, TimeOfDay)
Values ('2W', 6, '11:45')
--Show the data
Select * From SchedTask
Select * From SchedDay
--Now clean up the DB
drop table SchedDay
go
drop table SchedTask
go
Thanks.
Daniel Wilson
http://Embtrak.com Development TeamSee if you can get some inspiration from the sysjobschedules database in the
msdb database. It's the table that SQL Server Agent uses to schedule jobs
and it is documented in SQL Server Books Online.
Jacco Schalkwijk
SQL Server MVP
"Daniel Wilson" <d.wilson@.Embtrak.com> wrote in message
news:edmiKCcyFHA.1252@.TK2MSFTNGP09.phx.gbl...
>I am designing a table structure for scheduled tasks. Tasks must be
> scheduled either on a fixed interval or at specific times. Those specific
> times can be either every day or specific days of every w.
> I have come up with the plan I'll paste in here, but the NULL's and one
> field telling how to interpret data from another table leave me suspecting
> there's a better way. I'm designing this from scratch so am free to change
> as needed.
> Suggestions?
> Use Pubs
> Create Table SchedTask(
> TaskCode nvarchar (6) not null Primary Key,
> TaskDescription nvarchar (30) ,
> ScheduleType char(1) not null Check ( ScheduleType in ('I', 'D',
> 'W')), --for Interval, Daily, Wly
> ScheduleInterval SmallInt NULL
> )
> go
> Create Table SchedDay(
> TaskCode nvarchar (6) Foreign Key References SchedTask (TaskCode),
> DayOfW TinyInt not null Check ( DayOfW Between 0 and 7), --0
> indicates it doesn't matter, 1-7 map to Sunday-Saturday
> TimeOfDay SmallDateTime not null
> )
> go
> --Add a task to repeat every 10 minutes
> Insert Into SchedTask (TaskCode, TaskDescription, ScheduleType,
> ScheduleInterval)
> Values ('10M', 'Repeat every 10 minutes', 'I', 10)
> --Add a task to occur at 9:00 AM every day
> Insert Into SchedTask (TaskCode, TaskDescription, ScheduleType,
> ScheduleInterval)
> Values ('9D', 'occur at 9:00 AM every day', 'D', NULL)
> Insert Into SchedDay (TaskCode, DayOfW, TimeOfDay)
> Values ('9D', 0, '9:00')
> --Add a task to occur at 9:00 AM every Monday and 11:45 every Friday
> Insert Into SchedTask (TaskCode, TaskDescription, ScheduleType,
> ScheduleInterval)
> Values ('2W', 'Do this twice a w', 'W', NULL)
> Insert Into SchedDay (TaskCode, DayOfW, TimeOfDay)
> Values ('2W', 2, '9:00')
> Insert Into SchedDay (TaskCode, DayOfW, TimeOfDay)
> Values ('2W', 6, '11:45')
> --Show the data
> Select * From SchedTask
> Select * From SchedDay
> --Now clean up the DB
> drop table SchedDay
> go
> drop table SchedTask
> go
> Thanks.
> Daniel Wilson
> http://Embtrak.com Development Team
>
>|||But whatever you do, don't use ints for date and time values. :-)
A
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23gJJ2VcyFHA.2932@.TK2MSFTNGP10.phx.gbl...
> See if you can get some inspiration from the sysjobschedules database in
> the msdb database. It's the table that SQL Server Agent uses to schedule
> jobs and it is documented in SQL Server Books Online.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Daniel Wilson" <d.wilson@.Embtrak.com> wrote in message
> news:edmiKCcyFHA.1252@.TK2MSFTNGP09.phx.gbl...
>|||Thanks. I'll think that over.
And I'll definitely NOT use integers for times!
dwilson
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23gJJ2VcyFHA.2932@.TK2MSFTNGP10.phx.gbl...
> See if you can get some inspiration from the sysjobschedules database in
the
> msdb database. It's the table that SQL Server Agent uses to schedule jobs
> and it is documented in SQL Server Books Online.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Daniel Wilson" <d.wilson@.Embtrak.com> wrote in message
> news:edmiKCcyFHA.1252@.TK2MSFTNGP09.phx.gbl...
specific
suspecting
change
>