Hi,
I need assistance with DTS. I'm able to set up a package
that reads from a Text file and transforms everything
correctly into a SQL Server 2000 DB.

Unfortunately, I'm unable to:

a) Set up said package to accept varying text file names
as the source. (I can't find how to get Dynamic
Properties Task to accept Global variables. Everything I
have seen isn't very comprehensive)

b) Pass the filename via ASP to the DTS package.(Tried
passing Global variables through stored proc, but no luck)

c) Execute the DTS package from ASP. (I tried doing this
from a stored proc, xp_cmdshell, but to no avail... lack
of permissions)


I'm obviously a NooB to DTS. Does anyone know a place
that will hold my hand as I try to complete these steps?
I've scoured sqldts.com, dbforums and others, but I am
unable to find something comprehensive that will assist
me in working with ASP and DTS. Thanks you for your
assistance!!!

Add a dynamic properties task to the package.
Right click properties
click add button
expand connections
select the text file(source)
select the datasource(property name from right window)
click the the set button
click the drop down of source and select the global
variable.
If you already created a global variable select that
variable from the source inforamtion drop down if not
click the createglobalvariables button and create a
variable.
make sure datasource of textfile is pointed to some
filename.
You can finally pass the filename outside of the package.

Sesha

code was stripped.


Const DTSSQLStgFlag_Default = 0
Const DTSStepExecResult_Failure = 1

Dim oPkg, oStep, sErr, bSuccess

Dim sServer, iSecurity, sUID, sPWD, sPkgName,
sPkgPWD

' Get Form Variables
sServer = "localhost"
sUID = "Test"
sPWD = "test"
iSecurity = "0"
sPkgName = "pkgConvertFlatFile"
sPkgPWD = ""

' Validate Variables
If Len(sServer) = 0 Then sErr = "<li>Invalid
Server Name"
If iSecurity = 0 And Len(sUID) = 0 Then _
sErr = sErr & "<li>Invalid Username for
SQL Security"
If Len(sPkgName) = 0 Then sErr = sErr
& "<li>Invalid Package Name"


If Len(sErr) = 0 Then
Response.Write "<p>Executing " & sPkgName
& " from " & _
sServer & "</p>"

' Load the Package
Set oPkg = Server.CreateObject
("DTS.Package")
oPkg.LoadFromSQLServer sServer, sUID,
sPWD, iSecurity , sPkgPWD, "", "", sPkgName

' Execute the Package
oPkg.Execute

bSuccess = True

' Report Step status
For Each oStep In oPkg.Steps
sErr = sErr & "<p> Step [" &
oStep.Name & "] "
If oStep.ExecutionResult =
DTSStepExecResult_Failure Then
sErr = sErr & "
failed<br>"
bSuccess = False
Else
sErr = sErr & "
succeeded<br>"
End If
sErr = sErr & "Task """ & _
oPkg.Tasks
(oStep.TaskName).Description & """</p>"
Next

If bSuccess Then
sErr = sErr & "<p>Package [" &
oPkg.Name & "] succeeded</p>"
Else
sErr = sErr & "<p>Package [" &
oPkg.Name & "] failed</p>"
End If
End If

' Clean Up
oPkg.UnInitialize
Set oStep = Nothing
Set oPkg = Nothing

Response.Write sErr
Response.Write "<p>Done</p>"

In article <9f6301c35c3c$f475f470$a001280a (AT) phx (DOT) gbl>, Jeff Borden
<jeff_borden (AT) hotmail (DOT) com> writes
Yes. Just prior to calling the execute method you can set the package's
global variables, e.g.

oPkg.GlobalVariables("VariableName").Value = "ASPValue"


If using xp_cmdshell then you can use DTSRUN with the /A parameters to
set global variable values. DTSRUN is fully documented in Books Online
including the /A parameter. If you want help with the dtsrun command
line syntax, try the dtsrunui.exe tool. Select the package and click
Advanced. Use the command line generation options available.

Totally different security context and execution location. DTS is a
client side tool so just like any other COM object called via ASP it
executes under the IUSER_account on the web server, not on the SQL
server or your desktop as when developing through Enterprise Manager.

There is some stuff in this article, when I suggest using a COM object
set under COM+ with a named account. Also includes sample code.

Execute a package from Active Server Pages (ASP)
http://www.sqldts.com/default.aspx?6,104,207,7,1

You really need to get the error message from the step but this requires
a string typed language. This is another reason why I like the COM+
approach.


The change security context and execution location is also explained in
this article albeit in a different context-

INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/?kbid=269074

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

Darren,

Thanks for taking the time to reply. This is my first
stab at DTS, and it's been a rocky one. When I try and
execute the package from ASP, sans the Global variables,
it gives the error I previously posted. I just wanted to
post the error that the package log returned to me:

Step Error Source: Microsoft OLE DB Provider for SQL
Server
Step Error Description:Login failed for
user 'BORDEN\IWAM_JBENNETT'.
Step Error code: 80040E4D
Step Error Help File:
Step Error Help Context ID:0

Obviously there is a permissions error. I will now look
over the articles you posted previously, and hopefully
put this behind me. Thanks!

In article <033501c35c57$a826efa0$a501280a (AT) phx (DOT) gbl>, Jeff Borden
<jeff_borden (AT) hotmail (DOT) com> writes
The error tells us that the package is executing under the security
context of the BORDEN local machine account IWAM_JBENNETT.
Does this has access to your SQL Server? Unless the SQL Server is
installed on BORDEN then no, and there is no way to grant it access
either if IIS is on a separate machine to SQL Server.

Sorry to harp on but by using COM+ you can run the COM object under a
domain account which can of course be granted access to logon to SQL
Server.

The other alternative is of course to use a SQL login.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

In article <0beb01c35cff$5cbc3540$a601280a (AT) phx (DOT) gbl>, Jeff Borden
<jeff_borden (AT) hotmail (DOT) com> writes
sPackageName is not the package object so you cannot set the global
variable collection directly.

I would expand the COM object to accept a third parameter for the file
name.

Then add some code in the object to take the parameter and set the
variable, something like this, with the bits filled in of course-

ASP-
bResult = oExecDTS.Execute(sServer, sPackageName,
Request.Form("txtServer"))

VB-

Public Function Execute(ByVal sServer As String, ByVal sPackageName As
String, ByVal sTextFile As String) As Long

Dim oPkg As DTS.Package2
......
oPkg.LoadFromSQLServer sServer ... sPackageName

oPkg.GlobalVariables("VariableName").Value = sTextFile

oPkg.Execute

.....

End Function




--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

In article <063401c3601a$b7693f10$a001280a (AT) phx (DOT) gbl>, Jeff Borden
<jeff_borden (AT) hotmail (DOT) com> writes
If using .Net then I wouldn't bother with COM at all, just do it in .Net
if that is where your skills are. I believe Net has some impersonation
capabilities so why not use those directly from ASP.net in a similar way
to how previously you coudl use COM+ to abstract the execution context
of teh package away from the IUSER_MACHINE account. Never tried it
myself, but seems like a good idea.

As for global variables in .Net there is an issue-

DTS Global Variables and Visual Basic .Net
http://www.sqldts.com/default.aspx?6,104,265,0,1

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

Thanks again for the reply. I've got IIS and SQL Server
here on my test machine. Would it be easiest to change
SQL user permissions and call the dts package directly
from ASP? If so, what would I need to change to get this
to work? Thanks...

I reverted back to having asp call the dts package. I
changed the asp code to use the "sa" account. It fails,
but it doesn't show the failure in the package log. Any
reason for this?

my asp code is as follows:

Const DTSSQLStgFlag_Default = 0
Const DTSStepExecResult_Failure = 1

Dim oPkg, oStep, sErr, bSuccess

Dim sServer, iSecurity, sUID, sPWD, sPkgName,
sPkgPWD

' Get Form Variables
sServer = "localhost"
sUID = "sa"
sPWD = "********"
sPkgName = "pkgConvertFlatFile"

' Validate Variables
If Len(sServer) = 0 Then sErr = "<li>Invalid
Server Name"
If iSecurity = 0 And Len(sUID) = 0 Then _
sErr = sErr & "<li>Invalid Username for
SQL Security"
If Len(sPkgName) = 0 Then sErr = sErr
& "<li>Invalid Package Name"


If Len(sErr) = 0 Then
Response.Write "<p>Executing " & sPkgName
& " from " & _
sServer & "</p>"

' Load the Package
Set oPkg = Server.CreateObject
("DTS.Package")
oPkg.GlobalVariables
("DTSTextSource").Value = Request.Form("txtfile")

oPkg.LoadFromSQLServer sServer, sUID,
sPWD, iSecurity , sPkgPWD, "", "", sPkgName

' Execute the Package
oPkg.Execute

bSuccess = True

' Report Step status
For Each oStep In oPkg.Steps
sErr = sErr & "<p> Step [" &
oStep.Name & "] "
If oStep.ExecutionResult =
DTSStepExecResult_Failure Then
sErr = sErr & "
failed<br>"
bSuccess = False
Else
sErr = sErr & "
succeeded<br>"
End If
sErr = sErr & "Task """ & _
oPkg.Tasks
(oStep.TaskName).Description & """</p>"
Next

If bSuccess Then
sErr = sErr & "<p>Package [" &
oPkg.Name & "] succeeded</p>"
Else
sErr = sErr & "<p>Package [" &
oPkg.Name & "] failed</p>"
End If
End If

' Clean Up
oPkg.UnInitialize
Set oStep = Nothing
Set oPkg = Nothing

Response.Write sErr
Response.Write "<p>Done</p>"




Which returns:

Executing pkgConvertFlatFile from localhost

Step [DTSStep_DTSDataPumpTask_1] failed
Task "Transform Data Task: undefined"

Step [DTSStep_DTSDynamicPropertiesTask_1] succeeded
Task "Dynamic Properties Task: undefined"

Package [pkgConvertFlatFile] failed

Done


Thanks for your help