?
Solved

automate importing txt files through dts into a sql db

Posted on 2004-04-07
12
Medium Priority
?
513 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 2000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 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