Solved

automate importing txt files through dts into a sql db

Posted on 2004-04-07
12
505 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10779249
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
ID: 10783687
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
ID: 10786389
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:elucero
ID: 10786607
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
ID: 10786705
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
ID: 10844743
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
 
LVL 34

Expert Comment

by:arbert
ID: 10845003
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
ID: 10845573
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
ID: 10845887
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
ID: 10845998
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
ID: 10846377
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
ID: 11044490
I understand it, but don't know where to start.  Do you know where I can find an example.  
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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 setup several different housekeeping processes for a SQL Server.

756 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