sql server dts: Hi - I'm new to SSIS in SQL Server 2005. I've spent hours reading
documentation, viewing webcasts, etc., but my scenario seems to be pretty
unusual, so the example code I've been finding has not helped. Perhaps I'm
not looking in the right place, of course...but I would appreciate any and
all help.

The goal of my SSIS package is to regularly gather event data from several
custom event logs on several servers. These data will be used in a reporting
services app that will help us understand what kind of problems our deployed
apps are encountering in the field.

I know how to configure a connection to the database that contains the
EventLogName and EventData tables. After that I get lost. The steps would
go something like this:

1. Get the names of the event logs that will be queried. "Select LogName
from EventLogName" is the T-SQL I would use.
2. Find the most last time the package was run by reading the most recent
timestamp from the EventData table. Guard against the condition of no
records by using the ISNULL function, like this: "Select
ISNULL(MAX(timestamp), '06/30/2005') from EventData
3. For each LogName (from step 1), visit each server (hard-coded in the
package), open the event log, and read all events that have been logged since
the last time the package was run (from step 2).
4. Insert the data from step 3 into EventData table.

I've thought about putting this in a data flow, but I dont see any "FOR
EACH" logic available in a data flow, so I'm stymied.

My next idea is to make #1 and #2 an Execute SQL Task, and #3 could be a FOR
Loop Container (or FOREACH Loop Container) that would contain 2 steps: (a)
run a WMI Data Reader Task that would use the Win32_NTLogEvent class to
obtain the event data, and (b) a data flow that would have a SQL Server
Destination, where the event data gathered in (a) would go. However, several
aspects of how this would work are unclear to me:

* how would I get the list of EventLogNames out of step 1 into the FOREACH
container (step 3)? The FOREACH container does not seem to be able to accept
input from other steps; it only wants to iterate through lists of files, or
columns in a table, and so forth.

* How would I get the data out of step 2 into the WMI Data Reader Task?

* How would I get output data from the WMI Data Reader Task into the Data
Flow task, and from within that task flow it into the SQL Server Destination?

Of course, there could be some better way of doing this that I haven't even
considered.

Any suggestions?

Thanks in advance!
--

Hello,

You could read the SELECT statement into a Rowset variable (object) and
then n the ForEach loop use the ForEach ADO Enumerator type and add your
variable when prompted.

Also, you could use variable to get the timestamp and use it in dataflow to
insert table.

Some issues may require a bit more in depth attention and may fall under
the umbrella of Advisory Services. Microsoft now offers short-term and
proactive assistance for specific planning, design, development or
assistance with installing, deploying, and general "how to" advice via
telephone. For more information:

http://support.microsoft.com/default.aspx?scid=fh;en-us;advisoryservice

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.

Peter - Thanks for replying. Are you recommending Script Task(s)? They are
the only tasks I am aware of that allow the use of declared variables. And
if so, how would I transfer data out of a script task into a ForEach
container? Or into a DataFlow? Has MS published any tutorials or how-tos on
using variables in SSIS packages? Thanks
--
- Chris


[quoted text, click to view]