Move the source excel files to archieve folder

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Move the source excel files to archieve folder

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2008-04-15 : 11:52:31

Using the below script task I am checking for the excel file existence and upon file existence using the data flow task will load the excel data into sql table. After the data is loaded from one file or however number of excel files present, I want to move those into a archieve folder with datetimestamp to the filenames,please let me know how I can move those files with datetimestamp to the filenames, any help is greatly appreciated. Thanks!!

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Public Class ScriptMain
Public Sub Main()
If File.Exists(ReadVariable("FileNameVariable").ToString()) Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub

'From Daniel Read's Blog - http://www.developerdotstar.com/community/node/512/
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
End Class

tm
Posting Yak Master

160 Posts

Posted - 2008-04-15 : 14:10:23

Check out ..

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96032

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2008-04-16 : 10:53:49

How about adding the datetimestamp to multiple files and then moving to archieve folder. Thanks!

tm
Posting Yak Master

160 Posts

Posted - 2008-04-17 : 10:14:39

I know 2 options:

1. Use control Flow ~ script task
2. Use control Flow ~ Foreach Loop Container with File System Task, with two file connections (source and destination) and variables (Source path string, Destination path string, Destination filename)

Below is the Script Task option as it will take me too long to describe the Foreach Loop container as there are various settings you have to make.

Here is the script (Sample script supplied as is. Change as required for your requirement):

Imports System.IO 'required for file access

'script to include in main procedure
' Get current date to append to file
Dim yyyy As String = Date.Today.Year.ToString
Dim mm As String = Date.Today.Month.ToString.PadLeft(2, CChar("0"))
Dim dd As String = Date.Today.Day.ToString.PadLeft(2, CChar("0"))

Dim sourcedir As String, destinationdir As String
Dim sourcepathfile As String, destinationpathfile As String

sourcedir = "f:\temp\temp\" 'pass variable as source directory
destinationdir = "f:\temp\temp\temp3\" 'pass variable as destination directory

Dim di As DirectoryInfo = New DirectoryInfo(sourcedir)

Try
' Get number of files
Dim files As FileSystemInfo() = di.GetFiles()
'MsgBox("Number of files in directory: " + files.Length.ToString)

Dim fiNext As FileInfo

' Loop through all files in directory
For Each fiNext In files
'MsgBox("File : " + fiNext.FullName)
sourcepathfile = fiNext.FullName
destinationpathfile = destinationdir & fiNext.Name.Substring(0, fiNext.Name.Length - 4) _
& yyyy & mm & dd & fiNext.Name.Substring(fiNext.Name.Length - 4, 4)

File.Copy(sourcepathfile, destinationpathfile)
Next
Dts.TaskResult = Dts.Results.Success 'return success
Catch e As Exception
Dts.TaskResult = Dts.Results.Failure 'return failure
End Try

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2008-04-17 : 17:45:27

Here's what I did so far, struggling with this for the past two days to get it work, any help is greatly appreciated:
I have foreach loop container, inside which there's script task and data flow task, script task checks for the source file existence
and then if the file exists it will load the data into the table using the data flow task. Now I added file system task
to the data flow task, i.e Inside foreach loop container added the following:
Script task
|
|
|
|
dataflowtask
|
|
|
|
File system task

so that once the data is loaded using the data flow task, I want
the source files to be moved to the archieve folder and then rename as filename_datetimestamp:
The following are the steps that I followed after adding the File system task:
1. Foreach loop editor:
Enumerator configuration:
Folder: C:\Source
Files: *.xls
Retrieve File Name: Fully qualified

Variable mappings:
New variable:
Name: MyFileValue
Type: string
Value: \File1 9-15.xls

Then added the follow variables:
1. Name: SourcePath
data type: string
Value: C:\Source

2. Name: ArchivePath
data type: string
Value: C:\Source\Archieve

3. FullSourcePathFileName
For this variable: In the properties:
EvalAsExpress: True
Expression: @[User::SourcePath] + @[User::MyFileValue]

4. FullArchivePathFileName
For this variable: In the properties:
EvalAsExpress: True
Expression: @[User::ArchivePath] + SUBSTRING( @[User::MyFileValue] , 1 , FINDSTRING( @[User::MyFileValue],".",1) - 1 ) + "-" + (DT_STR, 2, 1252) Month( @[System::StartTime] )+ (DT_STR, 4, 1252) Year( @[System::StartTime] )+ SUBSTRING( @[User::MyFileValue] , FINDSTRING( @[User::MyFileValue],".",1) , LEN( @[User::MyFileValue] ) )

Then in the Filesystem task editor:
Isdestinationpathvar: True
Destvar: user::FullArchivePathFileName
Overwritedest: False

Oper: Rename File

SourceConn:
Issourcepathvar: True
Sourcevar: User::FullSourcePathFileName

The error I am getting:
[File System Task] Error: An error occurred with the following error message:
"Could not find a part of the path '\\folder1\Source\\folder1\Source\File4 12-10.xls'.".

Sorry for posting such a lenghty one, I wanted to try my best to resolve this issue, before I give up.
Thanks in advance for all your help on this!!!

tm
Posting Yak Master

160 Posts

Posted - 2008-04-18 : 09:36:47

Rather you give more info than less. Great info BTW.

I believe this is what is causing your problem:

1. Foreach loop editor:
Enumerator configuration:
Folder: C:\Source
Files: *.xls
Retrieve File Name: Fully qualified <<<<< [Should be "Name and Extension" radio selected. Fully qualified means it will return C:\source\filename.ext instead of filename.ext]

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2008-04-18 : 11:46:25

In \\server\Source folder the follow files are present
File1 9-15.xls
File2 9-16.xls
File3 9-17.xls
File4 12-9.xls
File5 12-10.xls
File6 12-11.xls
File7 12-12.xls
File8 12-13.xls
After the change to selecting NameandExtension radiobutton option, the follow is the error:
[File System Task] Error: An error occurred with the following error message:
"Could not find file '\\server\sourceFile5 12-10.xls'.".
I am trying to find where's it's resolving the above path, ofcourse
there's no file with the name of "sourceFile5 12-10.xls" somehow the "\" is missing between source and File5 12-10.xls, please let me know any ideas, Thanks a bunch!!!

tm
Posting Yak Master

160 Posts

Posted - 2008-04-18 : 13:03:07

Change path to include "\" at the end ..

1. Name: SourcePath
data type: string
Value: C:\Source <<<<< [change to C:\Source\]

2. Name: ArchivePath
data type: string
Value: C:\Source\Archieve <<<<< [change to C:\Source\Archieve\]

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2008-04-21 : 17:01:20

You rock!!!It worked!! Thanks much!!!

FYI, I did some changes to FullArchivePathFileName to add the date(MMDDYYYY format) and Timestamp, and also added another file system task to remove the files from the Source folder after moving the files to archieve. Below is the expression of FullArchivePathFileName:

Expression of FullArchivePathFileName:
@[User::ArchivePath] + SUBSTRING( @[User::MyFileValue] , 1 , FINDSTRING( @[User::MyFileValue],".",1) - 1 ) + "-" + RIGHT("0" + (DT_WSTR, 2) DATEPART("month", @[System::StartTime]), 2) +RIGHT("0" + (DT_WSTR, 2) DATEPART("day", @[System::StartTime]), 2)+ (DT_STR, 4, 1252) Year( @[System::StartTime]) + "-" +substring((DT_WSTR,30)GETDATE(), 12,2)+ "-" +substring((DT_WSTR,30)GETDATE(), 15,2)+ "-" +substring((DT_WSTR,30)GETDATE(), 18,2)+ SUBSTRING( @[User::MyFileValue] , FINDSTRING( @[User::MyFileValue],".",1) , LEN( @[User::MyFileValue] ) )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources