Author |
Topic |
SamC
White Water Yakist
3467 Posts |
Posted - 2002-09-23 : 11:00:06
|
I've implemented my first DTS package to import users by following the article[url]http://sqlteam.com/item.asp?ItemID=10627[/url]I've got a folder on my internet site that my customer FTPs CSV files for user updates. The article neatly allows me to FTP that file to my workstation, and then transform the file to a table "ClientImport" in the database, execute a script to integrate the ClientImport into the DB.I'd like to take this to the next step of automation so my workstation isn't involved. Nightly checks of the FTP customer folder, no hands-on updates.The first problem to solve is that the DTS Textfile source doesn't seem to be able to point to a folder on my internet site. It prefers to point to my hard drive. Is there a way around this?Next is how to schedule the DTS package to run at midnight nightly - even if my workstation is powered down?SamC |
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-23 : 11:13:09
|
If your workstation is powered down you won't be able to ftp the file to it.You can change the file that dts is using by usin the object model.objPackage.Connections("SourceFile").DataSource = DTSGlobalVariables("SourceFile").ValueTo get the value for the file either put it in a globle value from whatever loads the package or get it from the database or something within the package.Note that the dts package will run on the client so the path needs to be relative to that.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-09-23 : 11:29:57
|
quote: Note that the dts package will run on the client so the path needs to be relative to that.
OK. If I understand you correctly the big swing is that the DTS package will always run on the client.From reading the Email Queue article[url]http://sqlteam.com/item.asp?ItemID=5908[/url]I had the impression that DTS packages "could" be run on the server, as an autonomous process, completely independent of the client PC. This impression came from the VB script in the article which I'd understood DTS was running on the server??I had hoped to setup something autonomous, server based, which would check a web based folder for a new CSV text file to import, bring it into a table, execute a sp to integrate the results, email me when done.. Would it be possible to setup a server Job to kick off a DTS package on the server??Sam |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-23 : 11:56:06
|
Yep - if you schedule it from the agent then the server itself is the client.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-09-23 : 12:30:04
|
nr,So far so good.I looked at the job step properties, checked BOL.My missing link: I need a T-SQL statement to initiate a DTS package.Maybe there's another way outside of T-SQL?SamC |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-23 : 12:44:03
|
Set up a job to run the DTS package, then use sp_start_job to run it. You can right-click the DTS package in Enterprise Manager, choose Schedule Package, and do it that way very easily. Just remove the schedule afterwards.Or, use dtsrun. Call it using the xp_cmdshell procedure. Books Online has more details. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-23 : 18:51:18
|
Or you can probably use the sp_oa... procs to load and run it.It might sound silly but you could run a VB app which loads and sets properties of the package - that'll probably give the best control.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-09-25 : 08:07:03
|
I'd like to get this one pushed through to completion, and a few dark spots remain in my way. 1 - If the DTS package is executed from a job on the server, then the server is the client, and the 'Text File (Source)' in the package would refer to a folder on that server. I can get the physical path from my server provider. (I believe this is true from reading the previous responses in this thread. If I'm wrong though, please let me know.) 2 - VERY IMPORTANT: I need a way to test if an import file exists in the folder and exit the DTS package if it isn't there. (This import is an absolute listing of all users) Even better, a method that would retrieve the filename and apply this filename to the DTS package 'Text File (Source)' filename. 3 - I would like to use Rob/Nigel's suggestion to apply a schedule to the DTS package (this seems preferable to using job_start [dtspackagename]) by right clicking on the DTS package and selecting schedule. When the DTS package runs on schedule, is my PC the client or is the server the client? (When I re-read Nigel's response he's pretty direct that the server is the client when run on schedule) We've established that this is critical since the source filename is relative to the running client context. 3.A - OK, I've convinced myself by writing 3 questions above that the server is the client when DTS runs on schedule. Does DTS offer a way for the text source file to be browsed [defined] in the server environment? (This sounds like a rehash of (2) above. Sorry) 4 - Recommendations on DTS books??Thanks,SamC |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-09-25 : 08:14:47
|
In regards to number 2.To check if a file exists, I have used the undocumented master.dbo.xp_FileExist stored proc with much success...Also, you may want to look at - How can I change the filename for a text file connection? - for a method of dynamically setting your source name ...Jay White{0} |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-09-25 : 08:19:43
|
For number 3 and (3a) ...When you schedule a package, you are really just creating a SQL Server Agent Job that makes a cmdshell call to DTSRun on the server. So yes, the package will run on the server. If you execute the package from EM or DTSRun on some other box, the package will run on the other box. If you make your file paths relative to the SQL Server, then you can only run the package from a)the console or b)a job calling DTSRun for the package. However, if you use the UNC file path, you should be able to run the package from anywhere on you network.Jay White{0} |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-25 : 09:09:24
|
This will test for existence of a text file.Put it in an activex script task and take success/error exits.I would suggest the filename be a global variable.This opens a text file - reads the first line and tests if it's strFileName = "c:\TestFile.txt"Set fSysObj = CreateObject("Scripting.FileSystemObject")'Set fObj = CreateObject("File")Set fObj = fSysObj.OpenTextFile(strFileName, 1, 0) strLine = fObj.ReadLine if trim(strLine) = "" then else end iffObj.closeset fObj = nothing==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 09/25/2002 09:12:09 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-25 : 09:33:55
|
To find a filename - I take it you want one from that directory.Could call a stored proc - get the filename as an output parameter then use that to set the source.Then the SP would be checking for the existence of the file too.I would use something like (there are other ways)create procedure spGetFileName@Path varchar(1000) ,@FileName varchar(100) outputas/*select @path = 'c:\files\'*/set nocount oncreate table #a (s varchar(2000), id int identity)select @sql = 'dir /B /O-D ' + @Path + '*.*'select @FileName = #a.sfrom #a where id = 1drop table #agoTo call it something like.strCon = "Provider=SQLOLEDB.1;Integrated Security =SSPI;Persist Security Info=False;Initial Catalog=" & DTSGlobalVariables("DatabaseName").Value & ";Packet Size=4096;Data Source=" & DTSGlobalVariables("ServerName").Valueset objCn = CreateObject("ADODB.Connection")set objCmd = CreateObject("ADODB.Command")objCn.open strConobjCmd.ActiveConnection = objCnobjCmd.CommandText = "spGetFileName"objCmd.CommandType = 4 'adCmdStoredProcobjCmd.Parameters.Append objCmd.CreateParameter("Path",200,1,100,"c:\files\")objCmd.Parameters.Append objCmd.CreateParameter("FileName",200,2,3,"")Set rs = objCmd.Executeset rs = nothingDTSGlobalVariables("FileName").Value = objCmd.Parameters("FileName")==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-09-25 : 11:34:36
|
Thanks for your replies. Noone has said this, but I get the picture that using the GUI interface as described in the article on importing users [url]http://sqlteam.com/item.asp?ItemID=10627[/url] won't do. I had hoped to modify the Text File (Source) created in the GUI using minimal Active X. Oh well.Solving this problem on the server side will require a complete Active X / SP script. I've got enough DTS scripting down to identify the source, but now I need scripting to identify the target table, setup the datapump(?), execute it and so on.DTS scripting is interesting, although I'm sorry that the GUI came so close to solving my problem but couldn't be spun off autonomously.Sam |
|
|
|