Finding Containers and Variables from Script task?

Hi,
* Here's my use case:
I have 20 odd event handlers in various containers that all do the exact same thing. Even though they're simple (a Script and an Execute SQL Task), its a right pain to copy them & keep them synced. I could probably create a custom task for the content of each handler, but I would still need to copy & configure it across 20 event handlers.
My (maybe impossible) idea to handle this was to:
Create a single event handler at the global scope. When invoked, use System::SourceID or System::SourceName to either
- GetContainer(SourceID).Variables("myVar") (had there been such a function call)
- Traverse the package object model to find the container, i.e:
Package.Executables(SourceName).Variables("myVar")
Is it true that there is no practical way of doing this?

* "Variables on the container ... are visible to the event handler that handles the event on that container"
As I understand it, this is only true when attaching an event handler to the specific container generating the event. If a container has no event handler, the event will propagate up through the hierarchy all the way to the package scope if needed. As soon as the event has propagated even once, the invoked event handler no longer has access to the local variables of the source container. Is this a reasonably correct description?
Event handlers and propagation would be _strikingly_ more useful if the handler had access to the source container environment that created the event, or am I missing something obvious?

* As a second possibility, can I create a _custom task_ (as opposed to a script) that _can_ traverse the object model of a package created in the designer (i.e. I'm not creating the whole package in my own code)?

If you lasted this long, thanks!-)
KI think I answered this on the NG, but the variables on the parent of the eventhandler that raised the event are visible to the event handler.
You can also create a package that handle the events and uses parent package configurations to pass in the values for the events. Then use an execute package task in all your event handlers that references that shared package.
Tasks cannot traverse the object model. They are prohibited and variables will refuse to hold a reference to IS object model objects with an error.|||Although I did implement using those 20 event handlers, I've now ditched that in favour of parsing the needed info out of sysdtslog90, which does away with the need for all those event handlers. Always a good feeling when stripping out half the 'code' but none of the functionality
Very useful to understand how & when to use the event handlers though, thanks!
K

Find Missing data from table

Here is an issue that has me stumped for the past few days. I have a
table called MerchTran. Among various columns, the relevant columns for
this issue are:

FileDate datetime
, SourceTable varchar(25)

SQL statement:
SELECT DISTINCT
FileDate
, SourceTable
FROM
MerchTran
ORDER BY
FileDate
, SourceTable

Data looks like this:
FileDate DataSource
-----------
2005-02-13 00:00:00.000S1
2005-02-13 00:00:00.000S2
2005-02-13 00:00:00.000S3
2005-02-14 00:00:00.000S1
2005-02-14 00:00:00.000S2
2005-02-14 00:00:00.000S3
2005-02-15 00:00:00.000S2
2005-02-15 00:00:00.000S3
2005-02-16 00:00:00.000S1
2005-02-16 00:00:00.000S2
2005-02-16 00:00:00.000S3
2005-02-17 00:00:00.000S1
2005-02-17 00:00:00.000S2
2005-02-18 00:00:00.000S1
2005-02-18 00:00:00.000S2
2005-02-18 00:00:00.000S3
2005-02-19 00:00:00.000S1
2005-02-19 00:00:00.000S3

We run a daily process that inserts data in to this table everyday for
all 3 sources S1, S2, S3

Notice how some data is missing indicating the import process for that
particular source failed.
Example: Missing record
2005-02-15 00:00:00.000S1
2005-02-17 00:00:00.000S3
2005-02-19 00:00:00.000S2

Can someone please help me with a SQL Statement that will return me the
3 missing records as above.

Thanks in advance for all your help!

DBA in distress!
Vishal[posted and mailed, please reply in news]

SQLJunkie (vsinha73@.gmail.com) writes:
> Here is an issue that has me stumped for the past few days. I have a
> table called MerchTran. Among various columns, the relevant columns for
> this issue are:
>...
> We run a daily process that inserts data in to this table everyday for
> all 3 sources S1, S2, S3
> Notice how some data is missing indicating the import process for that
> particular source failed.
> Example: Missing record
> 2005-02-15 00:00:00.000 S1
> 2005-02-17 00:00:00.000 S3
> 2005-02-19 00:00:00.000 S2
> Can someone please help me with a SQL Statement that will return me the
> 3 missing records as above.

The below assumes that daily means daily and not only Monday to Friday.
It will catch if a date is missing entirely, although if the first or
last day is missing.

CREATE TABLE demo (date datetime NOT NULL,
src char(2) NOT NULL,
CONSTRAINT pk_demo PRIMARY KEY (date, src))
go
-- Creates a numbers table in a somewhat casual way that
-- is not guaranteed to work. The MAXDOP turns of parallelism
-- to improve our chances.
SELECT TOP 8000 n = identity(int, 1, 1)
INTO numbers
FROM Northwind..Orders a
CROSS JOIN Northwind..Orders b
OPTION (MAXDOP 1)
go
INSERT demo (date, src)
SELECT '2005-02-13 00:00:00.000', 'S1' UNION
SELECT '2005-02-13 00:00:00.000', 'S2' UNION
SELECT '2005-02-13 00:00:00.000', 'S3' UNION
SELECT '2005-02-14 00:00:00.000', 'S1' UNION
SELECT '2005-02-14 00:00:00.000', 'S2' UNION
SELECT '2005-02-14 00:00:00.000', 'S3' UNION
SELECT '2005-02-15 00:00:00.000', 'S2' UNION
SELECT '2005-02-15 00:00:00.000', 'S3' UNION
SELECT '2005-02-16 00:00:00.000', 'S1' UNION
SELECT '2005-02-16 00:00:00.000', 'S2' UNION
SELECT '2005-02-16 00:00:00.000', 'S3' UNION
SELECT '2005-02-17 00:00:00.000', 'S1' UNION
SELECT '2005-02-17 00:00:00.000', 'S2' UNION
SELECT '2005-02-18 00:00:00.000', 'S1' UNION
SELECT '2005-02-18 00:00:00.000', 'S2' UNION
SELECT '2005-02-18 00:00:00.000', 'S3' UNION
SELECT '2005-02-19 00:00:00.000', 'S1' UNION
SELECT '2005-02-19 00:00:00.000', 'S3'
go
SELECT *
FROM (SELECT date = dateadd(DAY, n - 1, mindate)
FROM numbers n
CROSS JOIN (SELECT mindate = MIN(date),
maxdate = MAX(date)
FROM demo) d
WHERE n BETWEEN 1 AND datediff(DAY, mindate, maxdate) + 1) AS a
CROSS JOIN (SELECT src = 'S1' UNION SELECT 'S2' UNION SELECT 'S3') AS s
WHERE NOT EXISTS (SELECT *
FROM demo
WHERE demo.date = a.date
AND demo.src = s.src)
go
DROP TABLE numbers
DROP TABLE demo

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the quick response Erland. I see what you are trying to do,
but I have data in the table starting 2003. Would be a lot of work if I
have to insert 3 records in table numbers for everyday (including
saturday and sunday) starting 2003. But I will try this nonetheless.

Thanks!

Vishal|||SQLJunkie (vsinha73@.gmail.com) writes:
> Thanks for the quick response Erland. I see what you are trying to do,
> but I have data in the table starting 2003. Would be a lot of work if I
> have to insert 3 records in table numbers for everyday (including
> saturday and sunday) starting 2003. But I will try this nonetheless.

Numbers need to have as many rows as there days in the timespan.

In fact, it does have to be a numbers table. I used a numbers, because
1) it was easier to compose one on the fly.
2) a numbers table is applicable other problems as well.
However, in our system we don't have a numbers - but we have a dates table,
and here is how we fill it in a safe way:

CREATE TABLE dates (
thedate aba_date NOT NULL,
CONSTRAINT pk_dates PRIMARY KEY (thedate)
)

-- Get a temptable with numbers. This is a cheap, but not 100% reliable.
-- Whence the query hint and all the checks.
SELECT TOP 80001 n = IDENTITY(int, 0, 1)
INTO #numbers
FROM sysobjects o1
CROSS JOIN sysobjects o2
CROSS JOIN sysobjects o3
CROSS JOIN sysobjects o4
OPTION (MAXDOP 1)
go
-- Make sure we have unique numbers.
CREATE UNIQUE CLUSTERED INDEX num_ix ON #numbers (n)
go
-- Verify that table does not have gaps.
IF (SELECT COUNT(*) FROM #numbers) = 80001 AND
(SELECT MIN(n) FROM #numbers) = 0 AND
(SELECT MAX(n) FROM #numbers) = 80000
BEGIN
DECLARE @.msg varchar(255)

-- Insert the dates:
INSERT dates (thedate)
SELECT dateadd(DAY, n, '19800101')
FROM #numbers
WHERE dateadd(DAY, n, '19800101') < '21500101'

SELECT @.msg = 'Inserted ' + ltrim(str(@.@.rowcount)) + ' rows into
#numbers'
PRINT @.msg
END
ELSE
RAISERROR('#numbers is not contiguos from 0 to 80001!', 16, -1)
go
DROP TABLE #numbers

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||CREATE TABLE Readings
(collection_date DATETIME NOT NULL,
source CHAR(2) NOT NULL
CHECK(source IN ('S1', 'S2', 'S3')),
PRIMARY KEY (collection_date, source));

This is a quick way to see what you do have on the days with missing
data:

CREATE VIEW Shortdates(collection_date, source)
AS
SELECT collection_date, source
FROM Readings
GROUP BY collection_date
HAVING COUNT(*) < 3;

We can now use this view or make it into a derived table. We will also
another table, which probably exists already.

CREATE TABLE Sources (sources CHAR(2) NOT NULL PRIMARY KEY, ..);

SELECT R1.collection_date, S1.source
FROM Readings AS R1,
Sources AS S1
WHERE S1.source
NOT IN (SELECT R2.source
FROM Shortdates AS R2 -- or use a derived table
WHERE R1.collection_date
= R2.collection_date);

-- Untested

Find floats with exponential notation

I'm having trouble identifying a column that is a float data type that uses
exponential notation. I've tried Casting and Converting to various data
types in order to find the 'E' but the 'E' never comes through. Is there a
better way to identify any float that displays with exponential notation?
TIA
Matt
The float column is stored as a binary number with a binary exponent.
It may be displayed with an E, but this is simply the way the front
end shows it. Internally there is no E.
The size at which a float is displayed using the E notation is built
into the front end program performing the display. It is not an
attribute of SQL Server.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 10:57:21 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:

>I'm having trouble identifying a column that is a float data type that uses
>exponential notation. I've tried Casting and Converting to various data
>types in order to find the 'E' but the 'E' never comes through. Is there a
>better way to identify any float that displays with exponential notation?
>TIA
>Matt
>
|||> The size at which a float is displayed using the E notation is built
> into the front end program performing the display. It is not an
> attribute of SQL Server.
I understand that, but I still need to identify those that display using exp
notation. What is the internal mechanism that causes SQL server to display
the number as exponential? In our case it is negative numbers with a decimal
and a leading 0. The issue I'm facing is that someone queried a table,
copied and pasted the results into a text file and imported the data into a
program. The columns that had exponential notation are off by 2-3 decimal
places because of that. There were probably a hundred thousand rows
extracted and the data was heavily manipulated prior to the import. There
are relatively few rows that had the data in exp notation but they have a
major effect on the dataset. It would be much easier to re-query the data
and cast it to varchar or decimal and just extract and import those rows
instead of trying to do the whole thing (which was about a weeks worth of
work) again but in order to do that, I need a way to identify the rows where
that field is exponential notation. So, if there is ANY way to determine if
a float will display using exponential notation, I'd really like to know
how. If anyone has other suggestions on how to handle this situation whether
it be SQL or otherwise, I'm open to suggestions.
TIA
Matt
|||If you use a client that displays floats in scientific notation (which
is quite common), and this notation is a problem, then you shouldn't use
float.
One way to do that, is to create a view that casts the float to a
decimal, and instruct your users / applications to use that view (and/or
disallow selects from the base table).
As mentioned by Roy, the value that is transferred to the client does
not contain an "E".
Gert-Jan
Matt Williamson wrote:
>
> I understand that, but I still need to identify those that display using exp
> notation. What is the internal mechanism that causes SQL server to display
> the number as exponential? In our case it is negative numbers with a decimal
> and a leading 0. The issue I'm facing is that someone queried a table,
> copied and pasted the results into a text file and imported the data into a
> program. The columns that had exponential notation are off by 2-3 decimal
> places because of that. There were probably a hundred thousand rows
> extracted and the data was heavily manipulated prior to the import. There
> are relatively few rows that had the data in exp notation but they have a
> major effect on the dataset. It would be much easier to re-query the data
> and cast it to varchar or decimal and just extract and import those rows
> instead of trying to do the whole thing (which was about a weeks worth of
> work) again but in order to do that, I need a way to identify the rows where
> that field is exponential notation. So, if there is ANY way to determine if
> a float will display using exponential notation, I'd really like to know
> how. If anyone has other suggestions on how to handle this situation whether
> it be SQL or otherwise, I'm open to suggestions.
> TIA
> Matt
|||There is no single answer because this is not done by SQL Server but
by whatever front-end program processes the result of the SELECT.

>The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that.
All I can suggest is find out what tool that person used, along with
whatever configuration settings were in place, and experiment until
you understand what that tool does to display exponential data. If
they repeat that very first step perhaps you can pick the problems out
there.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 13:19:47 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:

>I understand that, but I still need to identify those that display using exp
>notation. What is the internal mechanism that causes SQL server to display
>the number as exponential? In our case it is negative numbers with a decimal
>and a leading 0. The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that. There were probably a hundred thousand rows
>extracted and the data was heavily manipulated prior to the import. There
>are relatively few rows that had the data in exp notation but they have a
>major effect on the dataset. It would be much easier to re-query the data
>and cast it to varchar or decimal and just extract and import those rows
>instead of trying to do the whole thing (which was about a weeks worth of
>work) again but in order to do that, I need a way to identify the rows where
>that field is exponential notation. So, if there is ANY way to determine if
>a float will display using exponential notation, I'd really like to know
>how. If anyone has other suggestions on how to handle this situation whether
>it be SQL or otherwise, I'm open to suggestions.
>TIA
>Matt
>