Solved

automate importing txt files through dts into a sql db

Posted on 2004-04-07
12
498 Views
Last Modified: 2012-05-04
I need to automate importing a daily txt file into a sql db using dts.  I used the File Transfer protocol task to copy the files from a ftp site to a local directory.  Daily I only need to import the latest file into the db.  The txt files are like this in the directory.

dailye20040401
dailye20040405
dailye20040406


Today is the 20040407, so the latest file I would import would be dailye20040406.  If it's a Monday I would go back to the Friday's date.  

I can do the above manually in dts, but need to automate the process.  I have been playing around with the active x task to do this, but am not experinced with the active x language.  I got this far - so far I can only see if the file exists.  

Function Main()
      
      Dim objFSO, strFullNm
      Set objFSO = CreateObject("Scripting.FileSystemObject")
      
      StrFullNm = "c:\FSOExamples\daily_edf_NF_NC_NB" & CStr(Year(Now)) & "0"&CStr(Month(Now)) & CStr(Day(Now)) & ".txt"

If objFSO.FileExists(strFullNm) Then
      MsgBox "The File Exists"
else
      MsgBox "You still need to create the file"
End If

Set objFSO = nothing
Main = DTSTaskExecResult_Success
End  Function


Thanks.


0
Comment
Question by:elucero
  • 6
  • 6
12 Comments
 
LVL 34

Expert Comment

by:arbert
Comment Utility
If you setup a text file connection on your page with the name of a file that exists, you can change the file name later with this code:

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

Brett
0
 

Author Comment

by:elucero
Comment Utility
Thanks, Brett.  I was able to write something similar to this last night. And it's working.  Do you have any idea to modify this script to retrieve the file from an FTP site?  Thanks.  
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
The same link above applies--just change this to be your ftp connection:

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

Author Comment

by:elucero
Comment Utility
ok, I've done this and it makes selects the next file, but the when I get to the point where the source file needs to be read I get "error opening datafile".  I do have a userid and pw, but not sure where to put it.

sFilename = "ftp://kmv.com/daily_edf_NF_NC_NB" &  Right(Year(mydate), 4)
If Month(mydate) < 10 Then sFilename = sFilename & "0" & _
Month(mydate) Else sFilename = sFilename & Month(mydate)
If Day(mydate) < 10 Then sFilename = sFilename & _
"0" & Day(Mydate) Else sFilename = sFilename & Day(mydate)
sFilename = DTSGlobalVariables("LogFilePath").Value & _
sFilename &  ".txt"
'Set oConn = DTSGlobalVariables.Parent.Connections("SourceFile1")
'oConn.DataSource = sFilename
Set oConn = DTSGlobalVariables.Parent.Connections("SourceFile1")
 oConn.DataSource = sFilename
Set oConn = Nothing
Main = DTSTaskExecResult_Success
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
So this:

Set oConn = DTSGlobalVariables.Parent.Connections("SourceFile1")


Is your FTP connection?  I would do a msgbox (just for testing--be sure and take it out if you schedule this) to make sure the filename is what you expect.  If you already have the User/Password entered on the FTP connection, you don't have to reenter it in your code.

msgbox(sfilename)
Set oConn = DTSGlobalVariables.Parent.Connections("SourceFile1")
0
 

Author Comment

by:elucero
Comment Utility
From the active x script task to the textfile (source) task, in this case it's sourcefile1.  The correctpath is moved into sourcefile1, but then I get the error "Could not open the specified file for reading.  Make sure that the file exists and you have read access to it."  Just for testing purposes I used the files transfer protocol task to make sure I could download the files from this ftp site to a directory and it worked.  The same ftp site that can't read the files from sourcefile1.  Any ideas?  The code is below.  Thanks, elizabeth

'************************
'  Visual Basic ActiveX Script
'************************
Function Main()
Dim oConn, sFilename
'Business day calculation
mydate=now()-1

if datepart("w",mydate) = 2  then
mydate=mydate-2
else
mydate=mydate
end if

sFilename = "ftp.kmv.com\daily_edf_NF_NC_NB" &  Right(Year(mydate), 4)

If Month(mydate) < 10 Then sFilename = sFilename & "0" & _
Month(mydate) Else sFilename = sFilename & Month(mydate)
If Day(mydate) < 10 Then sFilename = sFilename & _
"0" & Day(Mydate) Else sFilename = sFilename & Day(mydate)
sFilename = DTSGlobalVariables("LogFilePath").Value & _
sFilename &  ".txt"
'Set oConn = DTSGlobalVariables.Parent.Connections("SourceFile1")
'oConn.DataSource = sFilename

Set oConn = DTSGlobalVariables.Parent.Connections("SourceFile1")
msgbox(sfilename)

     oConn.DataSource = sFilename


Set oConn = Nothing
Main = DTSTaskExecResult_Success

End Function
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 34

Expert Comment

by:arbert
Comment Utility
I'm guessing that the path to the text file and the FTP path would be different.

If you actually take the value fro sFilename and run it as a command on the server, does it pull the file up in notepad?  Give a file not found error?
0
 

Author Comment

by:elucero
Comment Utility
I tried the path ftp.kmv.com/daily_edf_NF_NC_NB20040415 (which is in my code) from notepad directly on the server, but got error.  Then I tried ftp://kmv.com/daily_edf_NF_NC_NB20040415.txt,  got nothing.  But then I tried ftp://kmv.com/ which is the real ftp site and it came up fine - prompt me for user name, etc.  

The reason why I changed it from ftp://kmv.com/  to ftp.kmv.com was that the file transfer protocol task did not recognize ftp://kmv.com/ , but recognized ftp.kmv.com  (had no problem reading the files).  Maybe a textfile source cannot connect to an ftp site.  Any ideas?  Thanks, Elizabeth

0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
ya, you can't connect directly to an FTP site with the text source.  I thought you were downloading a file and then trying to process it...
0
 

Author Comment

by:elucero
Comment Utility
so then I need add another active x task in my dts package to copy the file from the ftp site to a directory and then go with my code above?  

What about this?
I could actually use the file transfer protocol task,  but a new file comes in nightly.  It will not pick up the new file unless I manually select it.  Could I use the Dynamic Properties task to add the new nightly file to download into the source?  
0
 
LVL 34

Accepted Solution

by:
arbert earned 500 total points
Comment Utility
Exactly, you need to transfer it with FTP, and then use the text file task to get it.  You're 100% on track, the dynamic properties task will let you change the source on the FTP task to grab a new file nightly....
0
 

Author Comment

by:elucero
Comment Utility
I understand it, but don't know where to start.  Do you know where I can find an example.  
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now