DTS Newbie question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS Newbie question

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").Value

To 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 if

fObj.close
set 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) output
as
/*
select @path = 'c:\files\'
*/
set nocount on
create table #a (s varchar(2000), id int identity)
select @sql = 'dir /B /O-D ' + @Path + '*.*'
select @FileName = #a.s
from #a where id = 1
drop table #a
go

To 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").Value
set objCn = CreateObject("ADODB.Connection")
set objCmd = CreateObject("ADODB.Command")
objCn.open strCon
objCmd.ActiveConnection = objCn
objCmd.CommandText = "spGetFileName"
objCmd.CommandType = 4 'adCmdStoredProc
objCmd.Parameters.Append objCmd.CreateParameter("Path",200,1,100,"c:\files\")
objCmd.Parameters.Append objCmd.CreateParameter("FileName",200,2,3,"")
Set rs = objCmd.Execute
set rs = nothing

DTSGlobalVariables("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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources