Output FTP File
-
Hi Gurus.
My client drops for me many files like this on a shared drive M: daily
1_Test.csv
2_Test.csv
3_Test.csv
I would like you to advice on how to write a SQL code (that can include DTS if possible) that will take this files, merge them into one (since they have same columns) and send them to another client as one file. But it must only take the files of the same date and must not resend files i have send already. I need this to be an automated process since the files are dumped into M:drive midnite and I need this code so that I can schedule it as a job and run around 4h00am.
-
One of the simplest ways to merge files is by DOS copy. If it's desired to run by Transact SQL, it can be done through xp_cmdshell, though this type of task may also be done as a pure *.cmd file.
Combining files with the copy command
If you specify more than one source, separating entries with a plus sign (+), copy combines the files, creating a single file. If you use wildcards in source but specify a single filename in destination, copy combines all files matching the filename in source and creates a single file with the filename specified in destination.
You would also have to define your logic around the filenames, so you know if files have been sent already or not, what to do when something unexpected happens, errorhandling etc. It may very well be scheduled through the SQL Server Agent as well.
Good luck
/Kenneth
-
Create a File called export.csv with the same columns.
Create a DTS Package with a datapump task that will pump the various files from your client into the export.csv file.
Use a union query for your source in the Transform. Example:
SELECT * FROM 1_Test.csv
UNION
SELECT * FROM 2_Test.csv
UNION
SELECT * FROM 3_Test.csv
USE the export.csv as your target.
In a separate step, FTP the export.csv to your other client.
Hope this helps,
PEte
-
Hi Pete
Thanks a lot, I also used xp_cmdshell.
Both of us are still using a manual process. I want something that will check if the new files arrived and schedule a job or something to merge this files.
-
Check out the following site. This has some nice code examples for doing loops in DTS packages for importing files.
Andrew
-
I have a similar situation I resolved by
1) move the files from download directory to work directory. files are renamed with current datetime extension to avoid overwrite. the files I get have multiple "header" records so I have to split it into pieces as well.
2) do whatever needs to be done
3) archive the files (client is anal retentive - and sometimes I just have to CMB) using the same move process (from / to directories and rename boolean are global
For your needs it should be possible to modify the Active X task script below to only write one file (set the outFileName and simply append to it for all received files).
This is a quick cut 'n paste so there may be some mouse droppings for my logging and error checking routines. Tried to clean it up some and add a message here and there, but am trying to meet a Thurs deadline so it's a quickie.
The basic point is to open a file, read each record to end of line, write the record to the output file, loop for next file.
Need to set YOUR DATABASE string on connection to your database name.
I stumbled through this so can't say it is the best method but it does work. Feel free to contact me directly if you have questions (Frank.Perkins@concerto.com)
'*************************************************************************
' File Move and Split script.
' Used to move files from the downloads directory to
' the workarea directory.
' Server name and directory values are read from DTS global
' variables
' Needs a user id to sign on to db
'***********************************************************************
Option Explicit
Dim oFSO
Dim objCommand
Dim objConnection
Dim objRecordSet
Dim sServerName
Dim sTargetFolder
Dim sErrorFolder
Dim sRootFolder
Dim sSubFolders
Dim sFiles
Dim sExtensionString
Dim sTableName
Dim sSpecName
Dim sFormatId
Dim sSplit
Dim sSplitControl
Dim sErrorFile
Dim bRenameFile
Dim sDay
Dim sMonth
Dim sHour
Dim sMinute
Dim sSecond
Dim sDate
Dim sUserID
Dim sPassword
'*************************************************************************
' Default UserID and Password
'*************************************************************************
sUserId = ""
sPassword = ""
'*************************************************************************
'get today's date
sDate = Now()
' Get Rename parameter from global variable
bRenameFile = DTSGlobalVariables("RenameFile").Value
IF bRenameFile THEN
'Get date and time values to use in rename extension
sDay = Day(Date)
IF Len(sDay) = 1 THEN sDay = "0" & sDay
sMonth = Month(Date)
IF Len(sMonth) = 1 THEN sMonth = "0" & sMonth
sHour = Hour(Time)
IF Len(sHour) = 1 THEN sHour = "0" & sHour
sMinute = Minute(Time)
IF Len(sMinute) = 1 THEN sMinute = "0" & sMinute
sSecond = Second(Time)
IF Len(sSecond) = 1 THEN sSecond = "0" & sSecond
sExtensionString = sMonth & sDay & Year(Date) & sHour & sMinute & sSecond
End If
' current server name
sServerName = DTSGlobalVariables("ServerName").Value
' Create File System Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
' Get the from (download) directory
Set sRootFolder = oFso.GetFolder(DTSGlobalVariables("FromDirectory").Value)
' Then get the list of SubFolders beneath this root folder
Set sSubFolders = sRootFolder.SubFolders
' Get the move to (work) directory
sTargetFolder = DTSGlobalVariables("ToDirectory").Value
If Right(sTargetFolder, 1) <> "\" Then
sTargetFolder = sTargetFolder & "\"
End If
' error directory
sErrorFolder = DTSGlobalVariables("ErrorDirectory").Value
If Right(sErrorFolder, 1) <> "\" Then
sErrorFolder = sErrorFolder & "\"
End If
'connect to the database
Set objConnection = CreateObject("ADODB.Connection")
objConnection.ConnectionString = "Provider=sqloledb;Data Source=" & sServerName & ";Initial Catalog=<YOUR DATABASE>;User Id=" & sUserID & ";Pwd=" & sPassword
objConnection.Open
'build a command object to use for SQL Inserts into task history
Set objCommand = CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = objConnection
Set objRecordSet = CreateObject("ADODB.RecordSet")
'*********************************************************************************************************************************
' Main Function for this script
' Loop through downloads directory, pass each file found in root or any subdirectory of
' the root to a split function which will move the file to the workarea directory and split it if it
' has multiple header rows
'*********************************************************************************************************************************
Function Main()
' ***************************************************************************
' Log process start
' ***************************************************************************
' ******** my requirement included subfolders, yours will probably only have the root **********
' Check for files in root folder -- not supposed to be any at this level, but
' account for error
Set sFiles = sRootFolder.Files
For Each sFiles in sFiles
move sFiles.Path, sFiles.Name
oFSO.DeleteFile(filePath)
Next
' Loop through each subfolder
For Each sSubFolders in sSubFolders
' Get the list of files in this subfolder
Set sFiles = sSubFolders.Files
For Each sFiles in sFiles
move sFiles.Path, sFiles.Name
oFSO.DeleteFile(filePath)
Next
Next
' Clean Up
Set oFSO = Nothing
Main = DTSTaskExecResult_Success
End Function
Function move ( filePath, fileName)
Const ForReading = 1
Const ForWriting = 2
Const Appending = 8
Dim objTextStream
Dim strText
Dim bValidHeader
Dim outFileName
Dim tso
Dim iFileNumber
'***************************************************************************
' FileSystemObject.OpenTextFile(fname,mode,create,format)
'***************************************************************************
'Parameter Description
'fname Required. The name of the file to open
'mode Optional. How to open the file
'1=ForReading - Open a file for reading. You cannot write to this file.
'2=ForWriting - Open a file for writing.
'8=ForAppending - Open a file and write to the end of the file.
'create Optional. Sets whether a new file can be created if the filename does not exist.
'True indicates that a new file can be created, and False indicates that a new file will not be created. False is default
'format Optional. The format of the file
'0=TristateFalse - Open the file as ASCII. This is default.
'-1=TristateTrue - Open the file as Unicode.
'-2=TristateUseDefault - Open the file using the system default.
'***************************************************************************
' Open the passed in file for read
Set objTextStream = oFSO.OpenTextFile(filePath , ForReading)
If not objTextStream.AtEndOfStream Then
'read first line
strText = objTextStream.ReadLine
Else
objTextStream.Close
' ******* I track it as an error if file is empty
sErrorFile = sErrorFolder & fileName & "." & sExtensionString
End If
' ******** you will do this outside the function so it doesn't open another file ***************
'set up the output file name
outFileName = sTargetFolder & fileName & "." & sExtensionString
'and open it for write
Set tso = oFSO.OpenTextFile(outFileName, ForWriting, True)
' ******** you will do this outside the function so it doesn't open another file ***************
'write the first line to the output file
tso.WriteLine(strText)
'read the next line
strText = objTextStream.ReadLine
'if not at end, go into loop and write records until EOF
If NOT objTextStream.AtEndOfStream Then
Do While NOT objTextStream.AtEndOfStream
tso.WriteLine(strText)
strText = objTextStream.ReadLine
Loop
tso.WriteLine(strText)
tso.Close
Else
' this is an error in my case, only one record and it is the header, you may not need
objTextStream.Close
sErrorFile = sErrorFolder & fileName & "." & sExtensionString
oFSO.MoveFile filePath, sErrorFile
tso.Close
Exit Function
End If
objTextStream.Close
Set objTextStream = Nothing
Set tso = Nothing
oFSO.DeleteFile(filePath)
End Function
-
I have a similar situation I resolved by
1) move the files from download directory to work directory. files are renamed with current datetime extension to avoid overwrite. the files I get have multiple "header" records so I have to split it into pieces as well.
2) do whatever needs to be done
3) archive the files (client is anal retentive - and sometimes I just have to CMB) using the same move process (from / to directories and rename boolean are global
For your needs it should be possible to modify the Active X task script below to only write one file (set the outFileName and simply append to it for all received files).
This is a quick cut 'n paste so there may be some mouse droppings for my logging and error checking routines. Tried to clean it up some and add a message here and there, but am trying to meet a Thurs deadline so it's a quickie.
The basic point is to open a file, read each record to end of line, write the record to the output file, loop for next file.
Need to set YOUR DATABASE string on connection to your database name.
I stumbled through this so can't say it is the best method but it does work. Feel free to contact me directly if you have questions (Frank.Perkins@concerto.com)
'*************************************************************************
' File Move and Split script.
' Used to move files from the downloads directory to
' the workarea directory.
' Server name and directory values are read from DTS global
' variables
' Needs a user id to sign on to db
'***********************************************************************
Option Explicit
Dim oFSO
Dim objCommand
Dim objConnection
Dim objRecordSet
Dim sServerName
Dim sTargetFolder
Dim sErrorFolder
Dim sRootFolder
Dim sSubFolders
Dim sFiles
Dim sExtensionString
Dim sTableName
Dim sSpecName
Dim sFormatId
Dim sSplit
Dim sSplitControl
Dim sErrorFile
Dim bRenameFile
Dim sDay
Dim sMonth
Dim sHour
Dim sMinute
Dim sSecond
Dim sDate
Dim sUserID
Dim sPassword
'*************************************************************************
' Default UserID and Password
'*************************************************************************
sUserId = ""
sPassword = ""
'*************************************************************************
'get today's date
sDate = Now()
' Get Rename parameter from global variable
bRenameFile = DTSGlobalVariables("RenameFile").Value
IF bRenameFile THEN
'Get date and time values to use in rename extension
sDay = Day(Date)
IF Len(sDay) = 1 THEN sDay = "0" & sDay
sMonth = Month(Date)
IF Len(sMonth) = 1 THEN sMonth = "0" & sMonth
sHour = Hour(Time)
IF Len(sHour) = 1 THEN sHour = "0" & sHour
sMinute = Minute(Time)
IF Len(sMinute) = 1 THEN sMinute = "0" & sMinute
sSecond = Second(Time)
IF Len(sSecond) = 1 THEN sSecond = "0" & sSecond
sExtensionString = sMonth & sDay & Year(Date) & sHour & sMinute & sSecond
End If
' current server name
sServerName = DTSGlobalVariables("ServerName").Value
' Create File System Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
' Get the from (download) directory
Set sRootFolder = oFso.GetFolder(DTSGlobalVariables("FromDirectory").Value)
' Then get the list of SubFolders beneath this root folder
Set sSubFolders = sRootFolder.SubFolders
' Get the move to (work) directory
sTargetFolder = DTSGlobalVariables("ToDirectory").Value
If Right(sTargetFolder, 1) <> "\" Then
sTargetFolder = sTargetFolder & "\"
End If
' error directory
sErrorFolder = DTSGlobalVariables("ErrorDirectory").Value
If Right(sErrorFolder, 1) <> "\" Then
sErrorFolder = sErrorFolder & "\"
End If
'connect to the database
Set objConnection = CreateObject("ADODB.Connection")
objConnection.ConnectionString = "Provider=sqloledb;Data Source=" & sServerName & ";Initial Catalog=<YOUR DATABASE>;User Id=" & sUserID & ";Pwd=" & sPassword
objConnection.Open
'build a command object to use for SQL Inserts into task history
Set objCommand = CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = objConnection
Set objRecordSet = CreateObject("ADODB.RecordSet")
'*********************************************************************************************************************************
' Main Function for this script
' Loop through downloads directory, pass each file found in root or any subdirectory of
' the root to a split function which will move the file to the workarea directory and split it if it
' has multiple header rows
'*********************************************************************************************************************************
Function Main()
' ***************************************************************************
' Log process start
' ***************************************************************************
' ******** my requirement included subfolders, yours will probably only have the root **********
' Check for files in root folder -- not supposed to be any at this level, but
' account for error
Set sFiles = sRootFolder.Files
For Each sFiles in sFiles
move sFiles.Path, sFiles.Name
oFSO.DeleteFile(filePath)
Next
' Loop through each subfolder
For Each sSubFolders in sSubFolders
' Get the list of files in this subfolder
Set sFiles = sSubFolders.Files
For Each sFiles in sFiles
move sFiles.Path, sFiles.Name
oFSO.DeleteFile(filePath)
Next
Next
' Clean Up
Set oFSO = Nothing
Main = DTSTaskExecResult_Success
End Function
Function move ( filePath, fileName)
Const ForReading = 1
Const ForWriting = 2
Const Appending = 8
Dim objTextStream
Dim strText
Dim bValidHeader
Dim outFileName
Dim tso
Dim iFileNumber
'***************************************************************************
' FileSystemObject.OpenTextFile(fname,mode,create,format)
'***************************************************************************
'Parameter Description
'fname Required. The name of the file to open
'mode Optional. How to open the file
'1=ForReading - Open a file for reading. You cannot write to this file.
'2=ForWriting - Open a file for writing.
'8=ForAppending - Open a file and write to the end of the file.
'create Optional. Sets whether a new file can be created if the filename does not exist.
'True indicates that a new file can be created, and False indicates that a new file will not be created. False is default
'format Optional. The format of the file
'0=TristateFalse - Open the file as ASCII. This is default.
'-1=TristateTrue - Open the file as Unicode.
'-2=TristateUseDefault - Open the file using the system default.
'***************************************************************************
' Open the passed in file for read
Set objTextStream = oFSO.OpenTextFile(filePath , ForReading)
If not objTextStream.AtEndOfStream Then
'read first line
strText = objTextStream.ReadLine
Else
objTextStream.Close
' ******* I track it as an error if file is empty
sErrorFile = sErrorFolder & fileName & "." & sExtensionString
End If
' ******** you will do this outside the function so it doesn't open another file ***************
'set up the output file name
outFileName = sTargetFolder & fileName & "." & sExtensionString
'and open it for write
Set tso = oFSO.OpenTextFile(outFileName, ForWriting, True)
' ******** you will do this outside the function so it doesn't open another file ***************
'write the first line to the output file
tso.WriteLine(strText)
'read the next line
strText = objTextStream.ReadLine
'if not at end, go into loop and write records until EOF
If NOT objTextStream.AtEndOfStream Then
Do While NOT objTextStream.AtEndOfStream
tso.WriteLine(strText)
strText = objTextStream.ReadLine
Loop
tso.WriteLine(strText)
tso.Close
Else
' this is an error in my case, only one record and it is the header, you may not need
objTextStream.Close
sErrorFile = sErrorFolder & fileName & "." & sExtensionString
oFSO.MoveFile filePath, sErrorFile
tso.Close
Exit Function
End If
objTextStream.Close
Set objTextStream = Nothing
Set tso = Nothing
oFSO.DeleteFile(filePath)
End Function
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply