DTS in Stored Procedures
-
Help!
I am attempting to run a T-SQL Stored Procedure that calls 4 different DTS packages that read in four different text files, then copies that data into four different tables in SQL Server 2000, one table per text file. The text files are on a mapped network drive. If I set up the DTS packages to read the text files locally, the data gets copied, if I point the package to the network drive, no data is read. I am aware of the permissions issue, but still can't seem to solve the problem.
For my own testing/learning purposes, I've created a new user on the server named 'sqltest'. I've given him administrative privleges, set the sqlserveragent/mssqlserver services to start with this new account, and given full control permissions on the network drive, as well as the child directory that holds the text files. I've even tried using the UNC name of the server in the DTS package instead of the mapped drive letter and it tells me that it can't find the specified file for reading and to make sure the file exists and that I have access to it.
From what I can see, I have my permissions set up properly. It's just the text files can't be read if I'm pointing my DTS packages to the mapped drive.
Any ideas?
My thanks in advance...
-
If you're 100% certain that the path and filenames are correct and that the proper permissions on the folder the file resides in have been set, then most likely the SQL Server does not have the drives mapped for the account that SQL Server and SQL Server Agent services run under.
-
Thanks for the quick reply! Let me make sure I understand what your saying...
Are you saying that the 'sqltest' account I created needs the mapped drive for it's user profile? I've done that, unfortuneatly... Logged into the server as sqltest, and mapped the drive to the according folder. I even leave sqltest logged in while running the Stored procedure. Nada...
Thanks for the reply!
-
>>while running the Stored procedure
How are you "running" the stored procedure ? Via query analyser ? Via a scheduled agent job ?
How is the stored procedure calling the DTS packages ? Via xp_cmdshell and DTSRun.exe ? Or using the sp_OA* automation stored procedures ?
All the variations above can impact the security context under which the DTS package is actually executing.
-
You should be able work around this by using a UNC path rather than a mapped drive eg \\server\share\path\file.txt
-
The stored procedure is ran inside of Query Analyzer. It calls the DTS packages via sp_OA* to create, execute, and destroy the objects.
Even if I manually run the DTS package (from outside the stored procedure), the package executes successfully but doesn't populate the table with any data as long as the package is pointing to the mapped drive. If the drive is local, the table gets populated either from the stored procedure or if I run the package manually.
On a side note, we have discussed trying to do this with bcp/xp_cmdShell to populate a temporary table, then using the substring function (the files aren't delimited
) to parse out each individual column. Basically, use bcp to populate the temp table, then use a insert/select to set up all the individual columns into the database table.Thanks for the help...
-
Sometimes I have not been able to run the DTS packages via OA_; sometimes it stopped working in production; I looked and looked and could never find the problem; finally I caved in and used DTSRUN as follows:
dtsrun /SServerNameWherePackageLives /UYourSQLUserID /PYourSQLpassword /NPackageName /AGlobalVariableName:8=GlobalVariableValue
-
Hi,
I'm not sure this next stuff will help with your problem but I have had unexpected results when trying to load files into SQLServer tables in the past. After much pulling of hair, I found a check box that I needed to check.
I assume you have a transformation task in you package to move the text file to SQLServer.
1. Right click on the transformation line.
2. Click Workflow Properties
3. Click the Options Tab
4.Click the box that says: Close connection on completion.
In my case, I was trying to delete the file immedietely after successfully loading it into the SQLServer table
but Windows had the file locked.
Teague
-
Interesting, I tried what you suggested Teague and now the DTS package works! Even with the package pointing to a mapped drive, if I run it manually, the table gets loaded. However, if I execute the stored procedure, nothing happens.
In other words, the DTS package will populate the table if it's ran manually, but I get the same results as earlier if I execute the stored procedure. Strange.
Any ideas???
Thanks!
-
I've never used any of those sp_OaXXX. I need to play with them sometime.
Can you get any log information that might point the the internal error? Either creating a log file from the DTS package OR, from my quick 10 second lesson in sp_Oaxxxx; using that sp_OAGetErrorInfo to get the most recent error information.
t.
-
Thanks for the quick reply...
sp_OAGetErrorInfo isn't firing. I've got it set up to fire only if an error should occur, here's part of my code:
--------------------------------------------------------------
-- Method call to execute the DTS package
exec @hr = sp_OAMethod @object, 'Execute'
If @hr 0
Begin
exec sp_OAGetErrorInfo @object, @source OUT, @description OUT
select hr = convert(varbinary(4), @hr), Source=@source, Description = @description
return
End
Print 'Package executed'
----------------------------------------------------------------
When I run the script in query analyzer, it just returns the 'Package executed' message. There wasn't anything unusual in the DTS log file either. It looked normal and had no errors listed.
-
Me again.
My other inkling is the same as Eric who posted the initial reply. Might be a rights issue. Is that sqltest an SA account? Copying from BooksOnLine:
Permissions
Only members of the sysadmin fixed server role can execute sp_OACreate.
teague
-
sqltest is a member of the System Administrator fixed server role. That is what you meant, right?
Thanks...
-
Hmmm,
I'm about guessed out.
You may be forced to get responses from the smart subscribers.
Is using those sp_Oaxxxx procedures yall's standard way of execting DTS packages or are you the pioneer?
I'm wondering if there might be something at the server level that might need to be enabled for those to work?
Teague
-
Hehe, I'm pretty much the pioneer with sp_oa*.
My boss has suggested just using bcp/xp_cmdshell to read this file into a table I created that only has one large column, (this text file isn't delimited.) Then we'll use the substring function to individually sort through the large column and set up individual columns, which is selected into the correct table. Let me show you what I mean, here's some code I've been working on that I don't have working yet:
-----------------------------------------
declare @exec_stringvarchar(400)
select @exec_string = 'bcp manatron.dbo.bcp in C:\pt61_actor.TXT -Usa -Pxxxx'
execute master.dbo.xp_cmdshell @exec_string --This should load the text file into the bcp table
INSERT into pt61_actor
select
substring(LINE_TYPE, 1, 1),
substring(FILING_ID, 2, 20),
substring(ACTOR_ROLE, 22, 2),
substring(BUSI_FLAG, 24, 1),
substring(LAST_NAME, 25, 100),
substring(FIRST_NAME, 125, 70),
substring(MIDDLE, 195, 70),
substring(ADDRESS_1, 265, 200),
substring(ADDRESS_2, 465, 200),
substring(CITY, 665, 70),
substring(STATE, 735, 100),
substring(ZIP, 835, 50),
substring(CNTRY_ABV, 885, 3),
substring(COUNTRY, 888, 50),
substring(ADDR_TYPE, 938, 1)
from bcp
-----------------------------------
Where the table with the one, large column is bcp, and the real table is pt61_actor. Any thoughts on this method? It seems like it should work in theory...
Thanks again everyone for all of your help! You guys have been great!
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply