I am working on building an app for logging credit card transactions. I
made a small excel file that the users edit and the user edits the file and
then the file is DTS'd into a SQL Server database. I have the following
tables,

CREATE TABLE [dbo].[Cardholder] (
[CardholderID] [varchar] (20) -- NT username
[CardholderName] [varchar] (50) -- Name of cardholder
[Department] [varchar] (50) -- Department of Cardholder
[CardNumber] [varchar] (15) -- Credit Card Number (not full #, i.e,
***********1234)
[CardTypeID] [int] -- Type of card
[RuntimeUserID] [varchar] (20) -- If the Cardholder doesn't do his own data
entry (i.e. secretaries, etc), this is the username of the data entry
person.
[ExcelLink] [varchar] (50) -- Link to Excel file on server (same server as
SQL Server)
[DiffRuntimeUser] [bit] -- Flag to tell if there is a different user that
does the data entry
[Admin] [bit] -- Flag to tell if user is admin
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CreditCardLog] (
[UID] [int] IDENTITY (1, 1) -- Unique ID
[CardholderID] [varchar] (20) -- CardholderID from above
[PONum] [varchar] (10) -- Number for the Purchase order
[PODate] [datetime] -- Date for Purchase order
[Vendor] [varchar] (30) -- Vendor
[Description] [varchar] (200) -- Description of purchase
[Amount] [money] -- Amount of purchase
[CCCObjectCode] [varchar] (15) -- Code
[Complete] [bit] -- Flag to tell if the PO is complete
[Carryover] [bit] -- Flag to tell if the PO is carried over
[Credit] [bit] -- Flag to tell if the PO was credited back
) ON [PRIMARY]
GO

Is it possible to dynamically build a DTS package that reads the Cardholder
table to find out what and where to get the files to be imported? The Excel
files are stored as follows,

SERVER1/Reports/CreditCardLog/*Username*/*username*.xls

so my username is dlaing, so it would be
SERVER1/Reports/CreditCardLog/dlaing/dlaing.xls.

Thanks,
Drew Laing

Hi Drew

"Drew" wrote
that's possible. You have to query the card table to get a list of files to
import - define the result of the sql task as rowset global variable to use
later.
Then you have to loop through an import/transform section where the
starting ActiveX task changes the file path of the excel connection.
Take care to set the close connection on completion workflow property for
the transform, because otherwise you won't be able to change the excel file
from loop to loop.
Look here for examples http://www.sqldts.com/default.aspx?246
http://www.sqldts.com/default.aspx?298

HTH
Helge

Ok... I have been looking at http://www.sqldts.com/default.aspx?298 and have
been trying to follow it, but am getting the following error when I try to
run the package,

Step 'DTSStep_DTSActiveScriptTask_BuildMsgBoxString' was not found.

The BuildMsgBoxString is in the package and everything is spelled correctly.
The package runs fine if the SQL Task only returns 1 record, but if there
are more than 1 it fails. What have I done wrong?

Thanks,
Drew

"Helge C. Rutz" <news050424 (AT) nurfuerspam (DOT) de> wrote

Hi Drew,

Drew wrote:
there is one special thing to consider. If you want to manipulate DTS steps and tasks, you have to address them by there internal
name, which is by default build from there task type and an incremented number. The above step name is manually changed by Darren to
allow easier and unique addressing of step - your step will have a diferent name if you haven't copied the example.
You can see and change the step and task names with the "disconnected Edit" option, when you right click inside the designer.
Just go there and drill down to get a feeling how DTS works.

Helge

Nice... I see now! I have used DTS in the past, but just for quick imports,
nothing else... I will take a look, you will probably see more questions
from me on this topic, so I hope you all don't mind.

Thanks,
Drew

"Helge C. Rutz" <news050424 (AT) nurfuerspam (DOT) de> wrote

Ok... I changed the name of the Step and now it works, but when I changed
the name, the Workflow arrow went away.. How can I get this back? It's not
necessary, but nice to see what is happening.

Thanks,
Drew

"Drew" <drew.laing (AT) NOswvtc (DOT) dmhmrsas.virginia.SPMgov> wrote

Hi Drew,

Drew wrote:
thats because the precedense constraints are based on the names too.
Now you have a constraint for a non existing step, which you should change also.
Generally you should change names, if you ever need to, before you define the workflow to avoid this.

Helge

Thanks Helge! I am starting to get my head around this. This little
tutorial (as simple as it may be) has blown me away. Thanks for your help!

Drew

Ton be clear, the constraint is held in the PrecedenceConstraints collection
of the constrained step, and it has a property StepName that refers to the
preceding step.


--
Darren Green
http://www.sqldts.com
http://www.sqlis.com
"Helge C. Rutz" <news050424 (AT) nurfuerspam (DOT) de> wrote