1. NMS DTS Snapshot Acquisition

The NMS Import DTS package is organized into a series of connection types, tasks, precedence steps and completion paths. This document specifies how to identify and configure each DTS package element for NMS snapshot acquisition.

1.1 Connections

These specify data sources and destinations. There are two types used in NMS DTS packages: Text File Sources and a single OLE DB Provider that specifies the SQLServer that contains the destination table for the NMS acquisition. SQLServer connection information is stored in the OLE DB Provider connection object. It is best to fully specify the SQLServer network name when providing the connection info. On Meet’s SQLServer, I used:

This property dialog appears when right clicking on the OLE DB Provider Connection Object for SQLServer shown in the center of the NMS DTS package drawing. This connection object shouldn’t change greatly once it’s established. A new table is created for each NMS import and the tablename to use is written in a global variable that is part of the the DTS package start up object (Dynamic Properties Task at the top of the package drawing). The global variable is also used to specify a tablename for each Transformation Data task that conveys the NMS snapshot data from Text File Sources.

1.1.1 Text File Sources

The Text File Sources are DTS Connection objects. New ones are created by selecting a Text File Source from the Connection menu within the Package Designer. It is item 7 on the Connection menu.

The DTS package designer must be able to read and parse the supplied server, path and filename in order to add the Text File Source to the package. I have to go in there and let Gelder hook me back up the NOE domain in order to access the snapshot format on Last Friday, these folders didn’t contain any snapshots anyway. While creating a new Text File Source, an actual file must be supplied to the designer or it does not add the new Text File Source to the package. I had (what I believe) were an old NMS snapshot format that I could access here that I used in order to let the DTS package designer continue. The designer will open and parse the file supplied and will use what ever format it finds. The new OXN2 Text File Sources will have to have actual NMS snapshot server, path and file names specified and parsed.

The Text File Source format to parse is specified for the designer by selecting a server, path and file name from the Text File Source File name Properties:

Clicking Properties causes the designer to open the supplied file and parse its format. The designer will not add the Text File Source unless it can complete this step. The designer will parse the actual OXN2 file formats once they appear in

1.2 Tasks

The NMS DTS Package uses two main task types: Execute SQL Tasks and Data Transformation Tasks.

1.2.1.1 Execute SQL Tasks

These invoke SQLServer Stored Procedures to perform specific processing at set times in the package execution. A given Execute SQL Task runs once for each invocation of the NMS Import DTS package. Given in order of execution, the Execute SQL Tasks are:

Execute SQL Task #1. The NMS Tablename to create is specified in the global variable.

Execute SQL Task #2. This task and the remainder run when all the Text File Sources have copied all their rows into the SQLServer table stored in the global variable.

Execute SQL Task #3. This tasks excersizes the MLS Linked Server to get remote data. The Linked Server to MLS is configured using an Oracle ODBC Datasource to Oracle Instance X018. The stored procedure refreshes a local PRODUCT lookup table using the Linked Server. The Linked Server is configured in Enterprise Managers’ Security treeview node.

Execute SQL Task #4. This Stored Procedure accomplishes most of the actual data transformations. Its steps are detailed in a related Doc that Mike knows about.

1.2.2 Data Transformation Tasks

These Tasks are actually the Dark Black Lines you see on the drawing. They specify data acquisition copies from a Source to a Destination. They are created by selecting item 3, Transform Data Task from the Task menu in the package designer. The designer changes its mouse icon and asks you to a pick a Data Source. Click on a Text File Source. Next, it asks for a Destination. Click on the SQLServer OLE DB Connection object in the center of the package drawing. Right click on the newly created Black line to reveal its Properties.

Give the Transform Data Task a name like OXN2 Active. I think you can specify the package Global Varible name in the Destination tab amidst the list of SQLServer tablenames to choose from. You must select the Transformations tab and take the default transformations (none) in order to cause a Text File Source to write its data SQLServer when the package runs. Press OK after clicking the Transformations tab.

1.3 Precedence Steps

Precedence Steps are the Dashed Blue and White Lines you see on the package drawing. They are what give the package an execution sequence while the Dark Black Lines are Data Transformation Tasks that show how to parse and copy columns in the Text File Sources to the global tablename in SQLServer.

Precedence Steps are created by first clicking on the SQLServer OLE DB Connection object in the center of the drawing while holding down the CTRL key and then selecting a Text File Source. This selects two objects on the drawing:

Next, select a Blue Completion Line from the Workflow menu. The designer draws a Dashed Blue and White Line where you can specify when the Text Source File will copy its data to SQLServer.

Right click on a Blue Completion Line. A list of Transformation Data Tasks appears. This specfies when a task in the package runs.

The last Transformation Data Task to run before invoking Stored Procedures at the bottom of the drawing was TAC Failed. I modified this precedence so that it specified the last OXN2 task. I connected TAC Failed into the first OXN2 task (OXN2 Failed). This effectively splices the 3 new Transformation Data Tasks into the NMS Import package using theirCompletion lines.

It should be noted that none of the Transformation Data Tasks (Source Steps) from any dependencies for any of the others and they could be started in any sequence. The only requirement is that Execute SQL Task #1 and subsequent ones don’t run until all the Transformation Data Tasks have.

A precedence step could be formed from a single adjacent Source Step or you could specifiy all of them as Precedences. When any one of them completes, the Transformation Data Task being configured runs. Forming sequential Transformation Data Tasks from Text File Sources helps to miminze the amount of high database bandwidth the package could create in parallel. Delete the precedence steps you don’t want from the list of Source Steps in this dialog.

1.4 What’s next?

I haven’t tested a lot of this because I can’t access. The Package Designer won’t complete unless it can access the actual source files. The new OXN2 Text File Sources will have to have actual NMS snapshot server, path and file names specified and parsed as detailed in section 1.1.1 Text File Sources and then saved.

I am building a VB front end that references the DTS object library. This lets me specfiy Tasks, Precendences, and Workflows programmatically. The front end will get a folder of NMS snapshots from Stephaine. It will validate them to confirm that they contain righteous formats for NMS snapshots.

For those it finds, it will loop around the DTS object library adding Tasks and Precedences to it. Finally, it will write the package to SQLServer so we can open it up in Enterprise Manager and inspect it/run it.

Since this is mostly likely a few days out (it's not hard tho), I drew the new ONX2 Failed, Passive, Active paths by hand for now.

Testing

Play with adding Text File Sources, Transformation Data Tasks (Black Lines), Precedence Steps (Dashed Blue Lines) to see how the designer forms those on the drawing. Then we need to get or dummy up some NMS snapshots in the Proc area and run the package inspecting inputs and outputs for missing or mangled data. The NMS DTS Front End when ready, will greatly simplify adding and deleting NMS snapshots in the package since it will dynamically grow tasks and precedences for the NMS formatted snapshots in a given folder and write the resulting DTS package into SQLServer suitable for opening with the Package Desginer (I’ve already tried most of it and it seems to work!!)

Jeff