Problem with DTS package

[Page Horton]

I have a ASP.NET (VB) web application running. The DTS package is suppose to
generates a flat file to a file path (aka: \\myStation\outputFiles\). When
it runs it generates the following error on one of the steps (the others run
successfully):

Step 'Copy Data from Results to H:\Data\outfilename.txt Step' failed

Step Error Source: Microsoft Data Transformation Services Flat File Rowset
Provider
Step Error Description:Error opening datafile: The referenced account is
currently locked out and may not be logged on to.

I am using DTS active X in code behine to execute the package. The web
server is on one machine and the sqlserver is on another. Also I am
impersonating a domain account (aka: mydomain\RequestAccount ) that has
verified access to the path. I have run the package with DTSRun.exe
successfully from DOS and successfully from Enterprise Manager (with the
afore mentioned impersonating account). I know I'm successfully running
under the impersonated account because it shows it in the DTS log header:

Package Name: PG_WCSOUTPUTFILE
Package Description: DTS package description
Package ID: {6CC1492D-C5A4-427D-9D1A-7A4BE050ED56}
Package Version: {515B6214-F967-413D-AE1C-35FB78692E3E}
Package Execution Lineage: {36DD6EC4-190E-4900-BD8A-28F1AA95CBA3}
Executed On: ISBSA-WS05
Executed By: RequestAccount
Execution Started: 1/7/2005 10:50:28 AM
Execution Completed: 1/7/2005 10:50:29 AM
Total Execution Time: 1.063 seconds

Any idea why this is taking place?

Code:

Dim pkg As New DTS.Package2
Dim DTSServerName As String =
ConfigurationSettings.AppSettings(Constants.DTS_SERVER_NAME)

Dim impObj As New Impersonate(Constants.ImpersonateAccount, _

Constants.ImpersonatePassword, _

Constants.ImpersonateDomain)

Dim DTSUserName As String =
ConfigurationSettings.AppSettings(Constants.DTS_USER_NAME)
Dim DTSPassword As String =
ConfigurationSettings.AppSettings(Constants.DTS_PASSWORD)

impObj.BeginImpersonation()

pkg.LoadFromSQLServer( DTSServerName, _
DTSUserName, DTSPassword, _

DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, _
"", _
"", _
"", _
Constants.DTS_PACKAGE_NAME, _
Nothing )
pkg.WriteCompletionStatusToNTEventLog = True
pkg.Execute()
pkg.UnInitialize()

impObj.EndImpersonation()
Thanks.

Page

 

[Scott Allen]

It sounds as if everything is properly prepared. Can you enable security
auditing on the various servers to see which account is having the difficulty
(and where)?

http://www.pluralsight.com/keith/book/html/howto_enableauditing.html

 

[Page Horton]

Thanks for responding Scott, I have auditing on and checked the event logs
on the particpating machines and nothing. As a side note when I utilized
DTSRun from DOS to test the package (which ran successfully) it was from the
same machine that is acting as the web server (using same account that I
impersonated).

Page

 

[Scott Allen]

Interesting. How do you do the impersonation? With a username and password
in web.config?

Also, most DTS components should not need this setting, but try turning on
asp compatibility mode for your page. This adjusts the environment slightly
to use COM components, which is how DTS exposes functionality.

<%@ Page ... AspCompat="true" %>

HTH,

 

[Page Horton]

Scott,

Add AspCompt="True" to the page header and it made no difference. In as far
as the username/password, for the impersonated account, is for now in plain
text in the executable (for testing purposes -- note: the account only works
in our lab environment which is a closed network). Once I get it all to
work properly our security department will put the account info into our
encypted repository where I will pull it from at execution time (I will pass
the impersonate object and the repository object will populate it for me).

Page

 

[Scott Allen]

Hi Page:

I'm running out of ideas..

Any chance H: is a mapped drive? Impersonation doesn't load a profile with
such settings.

 

[Page Horton]

Actually H is a mapped drive, but its never used in the DTS package (no
drive letters are referenced in the package). To be honest that is the one
that has really thrown me. Our H drive is mapped as our My Documents area.
Even though the dts package creates the file with the path \\mynode\file...
does it still try to write to My Document path? But if impersonation
doesn't load a profile it wouldn't have the path would it? I do find it
puzzling because the initial output file is called outfilename.txt (then
renamed in the next step).

Page