SPROC only running first step of DTS package
-
So I have this stored procedure i've created that runs a DTS package. I need it to work this way because I need to be able to use PHP to run my DTS packge. I decided to do it using an OLE automation. I don't really know anything about OLE so this was mostly a copy and paste job for me.
However, I noticed that the DTS package wasn't running properly even though it comes back with a "The command(s) completed successfully." in Query Analyzer. I opened up profiler and filtered things down to this database and saw that only the first step in the package was getting executed. Here's my SPROC code:
CREATE PROCEDURE [dbo].[sp_Run_clickability_lyris] @path VARCHAR(150) AS
DECLARE @hr int
DECLARE @oPKG int
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
-- DTSSQLServerStorageFlags :
--- DTSSQLStgFlag_Default = 0
--- DTSSQLStgFlag_UseTrustedConnection = 256
EXEC @hr = sp_OAMethod @oPKG,
'LoadFromSQLServer("SOMESERVER", "some_login", "some_password", 256, , , , "myDTSPackage")',
NULL
IF @hr <> 0
BEGIN
PRINT '*** Load Package failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("DestinationPath").Value', @path
IF @hr <> 0
BEGIN
PRINT '*** GlobalVariable Assignment Failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN
END
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
GO
Can any one see any code that stands out in a "yeah here's your problem" kind of way? I'd really appreciate any help I could get with this one, as I've been trying to get this DTS package to execute from a sproc for some time. I've already tried using DTSRun, but getting permissions and network access to work from that is a serious hassle. Any help is appreciated. Thanks!
-
So I found out that it's not stopping after the first step, but actually stopping on my Transform Data Task.
This TDT takes data from a CSV on a network server and dumps it into a database on the SQL Server. My only guess as to why it's not working is because when I call the DTS in the OLE environment, it doesn't have permissions to access network drives or shared folders on network servers.
Can anyone confirm this? If this is so, I'm stuck in a rut, because I've already tried using DTSRun from a sproc but had the same problem with network access.
"Why is this DTS call different from all other DTS calls" (a joke for my fellow Jews on SSC)
**UPDATE**
I set it to enable package loggin and I was right. There's an error in the TDT when trying to get the file off the network server.
Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Error opening datafile: Access is denied.
Step Error code: 80004005
Step Error Help File: DTSFFile.hlp
Step Error Help Context ID: 0
Now I also realized I'm using a SQL Server login to run the DTS package, so that might be why I'm getting an access denied when trying to access files on another server. Any ideas for passing through a windows login to get files off the other server? Can I do that in a Text File (Source)?
-
I've never run one through a proc but I would think that it is using the agent profile or the dts owner profile to to get the file. You could check the event viewer on the machine that hold the file and see what identity is trying to login and give it the necessary permissions.
I also see that if you add a dynamic properties task that there is a place for ID and password on the text file connection. That may work as well.
Disclaimer: These are untested ideas.
-
Yeah, I saw that in the dynamics property task as well, but I haven't tested it yet. When I right click and choose "execute task" on the DTS package, it works fine, even when I'm logged in with a SQL Server login. I'm not sure what this runs under that way, but I can set up a log to track it and compre it to when I run it in the sproc.
The final and probably easiest alternative is just use all php (the whole point is the be able to call the DTS package from php) but then I may as well use MySQL!!
-
As an alternative, what about creating an unscheduled job to fire off the DTS package, and starting the job from osql? At least then the DTS package would be running under the SQL Server Agent account, which I'm assuming has the necessary privileges.
-
Ben,
If you have the appropriate permissions on the file server, just make sure that you specify a fully qualified UNC, i.e., \\someserver\somedirectory\\somesubdirectory\myfile.txt
-
Thanks guys. I have a fully qualified UNC, the only problem is I don't have credentials to acces this UNC when I run the DTS package as an OLE automation. I will give it a try with the unscheduled task thing, but I've been trying to get this working for the past 2 months since they decided to tell me "oh yeah, it's a two server solution" after I had it working on one.
-
Do you run the SQL Server Agent using a domain account? If so, have the network people give that account read access to the .csv folder (they may have to create a new share).
Another way would be to modify the DTS package to read from a .csv file stored locally on the server. Then, add a step to the DTS package ahead of the import that copies the file to a local folder, like this:
EXEC master.dbo.xp_cmdshell 'net use m: \\remoteserver\share /USER:domain\userid password'
EXEC master.dbo.xp_cmdshell 'copy m:\thefile.csv c:\data\thefile.csv' -- C: drive of SQL Server machine
EXEC master.dbo.xp_cmdshell 'net use m: /d'
Of course, the userid use above would have to have read access to the .csv folder. The DTS package would read the .csv file from (in this example) c:\data
An variation of this is to put the NET USE and COPY commands in a batch file, and execute the batch file using xp_cmdshell.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply