Bad Directory- DTS - Global Variables

I am trying to set Global Variable that will open a txt file and import the
data into a sql table.
I am currently using the sample I found at sqldts.com (Looping, Importing
and Archiving).
When I set the variable for filename = dbo.cadfilepath.filename, I get a Bad
Directory error.
My filename data is stored in another table - I am looking for the best
approach to calling the filename from the table = cadfilepath and opening
the actual text file and importing the data into my project table.
ie.
Table = CADFilepath(pathname, projectnum)
Pathname ProjectNum
\\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
\\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
\\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
I want to access the info in CADFIlePath and then open the first record's
text file and import this data into my Project Table, then repeat the
process until .EOF.
I know this sounds confusing and I can email you my documentation - since
this website does not allow for attachments.
Any assistances is greatly appreciated.
KarenHi
This is a stored procedure which will run a DTS and assign a variable to
file. Modify it for your needs.
I assumed you created the DTS and defined the global varaible.
CREATE proc spDTS
as
declare @.packagename varchar(255)
declare @.userpwd varchar(255) --login pwd
declare @.intsecurity bit --use non-zero to indicate integrated security
declare @.pkgPwd varchar(255) --package password
declare @.hr int
declare @.object int
set @.packagename='PackageName'
set @.userpwd=null
set @.pkgPwd=''
set @.intsecurity=0
--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
Begin
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
end
declare @.svr varchar(15)
declare @.login varchar(15)
select @.login = SUSER_NAME()
select @.svr = @.@.servername
declare @.flag int
select @.flag = 256
EXEC @.hr = sp_OAMethod @.object,
'LoadFromSqlServer',NULL, @.ServerName=@.svr, @.ServerUserName=@.login,
@.PackageName=@.packagename, @.Flags=@.flag, @.PackagePassword = @.pkgPwd
exec @.hr = sp_OAsetproperty
@.object,
'GlobalVariables
("FileLocation").Value', 'c:\MyFile.txt' --set location GV
EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
END
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @.object, @.hr
RETURN
END
GO
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>|||One thing you can do is to load the Global variable directly from the table.
This is done by adding a Dynamic properties task.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>