sql server dts: I am writing a package that is dynamic based on a parameter passed in
via the DTSRUN command.

This needs to do the following, where the package parameter amend the
global parameter DataType before execution :

Loop until end of files:

Read 1st file in folder of name DataType (sequence number)
If unexpected sequence
Fail
else
Unload data from file into SQL table (different table
and columns for each DataType)
Move file to Completed folder
Next file
end


I am happy with most of this except how to define the destination
table and the transformation required for the data. This clearly needs
to be either a dynamic step that involves defining the transformation/
destination in some other code (how???) or a finite number of
conditional steps with each potential transformation/destination in
each step, thus the execution path will be different for each
DataType.

Any ideas/examples (particularly examples :))?

Cheers

Mark

Actually it is SQL 2000.

Thanks for your reply, however I have managed to simplify the design
and thus this particular question is now not an issue.

However, I have come up with another issue.

This DTS package is going to be reused for several different jobs. A
global parameter identifying the job will be passed in via a DTSRUN
parameter. This will set a global parameter, called AccountType on
opening the package.

On SQL reference tables, I am holding values, for each AccountType,
that I want to retrieve and hold in other global variables for use
throughout the package.

e.g.

ACCOUNT_TYPE is REDX

On reference table with ID 'REDX', there is a column Last_RUN_TIME
that I need to pick up and place in global variable LastRunTime.

I have used the Dynamic Properties Task and used queries in Add/Edit
Assignment in the past to set up such global variables. However, this
time, the query itself needs to use the passed in global parameter of
AccountType. It appears to me that I cannot use the Parameters Lookup
in Add/Edit Assignment. The use of '?' is only in the Execute SQL
task. I thought I may be able to use the format below but apparently
not:

Select Last_RUN_TIME from reference_table where ID = ?

The only other option I can see is to set up all global parameters in
the DTSRun command using appropriate queries, thus having to maintain
several parameters at this stage instead of just one. Is this the only
way I can do this?

Cheers

[quoted text, click to view]

Hi Mark,

Are you using SSIS (SQL 2005) or DTS (SQL 2000)? Based on your
description, I would assume it's SSIS and in this case you will need
to create a separate destination (and file source) for each datatype -
unless the files and tables have the same metadata (i.e. column
structure).

If the metadata is the same, you can set the source and destination
tasks dynamically quite easily. You should just need to put the table
name in a variable, and then in the destination task, select "Table or
view from variable". For the source you need to set the file
connectionstring dynamically using expressions.

In DTS it is possible to re-use the same source and destination
connections for tables with different metadata by changing your
connections and then dropping and recreating the datapump task at
runtime. This requires some familiarity with ActiveX scripting,
although writing the looping mechanism in DTS will probably cause you
more headaches than making the datapump dynamic ;)

Good luck!
J