file creation/transfer

Question

file creation/transfer

mgraulich

Ten Centuries

Points: 1294
More actions
September 22, 2005 at 12:03 pm

#76427

I know most answers I will get to this will have something to VB scripting within DTS. The problem is that I don't have time to learn it right now and I need to get this package created asap . I have a simple sql statement I need to export to a dynamically named file based upon todays date and than ftp that new file out. Any ideas? THANK YOU!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

You will have to learn VBS, but someone can probably help you get the filething done rather quickly if you would post your requirements for the file. and Also calling FTP operations you'll have to do it in VBS

mgraulich Ten Centuries Points: 1294 More actions · Answer 2

First I have a sql statement... say select message from test.

The sql statement results would have to be written to a file dynamically using todays date... say 20050922.txt

Lastly, that newly created file (20050922.txt) than needs to be ftp'd offsite.

Ray M SSC-Insane Points: 21093 More actions · Answer 3

Check this out

http://www.sqldts.com/default.aspx?200

This will help U out alot.

mgraulich Ten Centuries Points: 1294 More actions · Answer 4

Function Main()

Dim oConn, sFilename

' Filename format - exyymmdd.log

sFilename = "ex" & Right(Year(Now()), 2)

If Month(Now()) < 10 Then sFilename = sFilename & "0" & _

Month(Now()) Else sFilename = sFilename & Month(Now())

If Day(Now()) < 10 Then sFilename = sFilename & _

"0" & Day(Now()) Else sFilename = sFilename & Day(Now())

sFilename = DTSGlobalVariables("LogFilePath").Value & _

sFilename & ".log"

Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Source)")

oConn.DataSource = sFilename

Set oConn = Nothing

Main = DTSTaskExecResult_Success

End Function

I understand this part but once I set oConn.DataSource = sFilename, how do I actually run my sql statement to extract the info to that dynamic file.

Also, is there any good documents on FTPing the file also? THANK YOU!

philcart SSC-Forever Points: 47796 More actions · Answer 5

This script resides in an ActiveXScript task that executes before your datapump. You set this up using the workflow. I've always found this article to be a good example of whats possible with workflow http://www.sqldts.com/default.aspx?246

For push FTP, you can hack together a DOS batch command to use the Windows FTP functionality. Better option is to get the DTS FTP Task from http://www.sqldts.com/default.aspx?302

--------------------
Colt 45 - the original point and click interface

mgraulich Ten Centuries Points: 1294 More actions · Answer 6

Unfortunately, I can't seem to download the product and the article is to advanced for me to try and incorporate. Just a newbie here. Could I call the xp_cmdshell to do the transfer for ftp? Not sure how I would do that. Another thing I just thought of is how am I going to zip this file. I guess I could use the External Process task but how do I pass the global file name I generate to that task?