April 15, 2008 at 9:49 am
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 date×tamp to the filenames, Please let me know how I can move those excel files with date×tamp 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
April 15, 2008 at 2:48 pm
Just another method>> Why don't you use File System Task to move the file. It is easier that way. Also, what Are you trying to do with the file? Read it, clean it, anything? OR just want to archive it if it is there?
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
April 16, 2008 at 8:51 am
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!!!
April 17, 2008 at 3:48 pm
Any help will be great!!
April 17, 2008 at 4:09 pm
Mh (4/17/2008)
Any help will be great!!
Have you evaluated your expressions so that you can verify the exact path it is trying to hit.
Lucky
April 18, 2008 at 7:19 am
lucky (4/17/2008)
Mh (4/17/2008)
Any help will be great!!Have you evaluated your expressions so that you can verify the exact path it is trying to hit.
Yes, I did that and it shows the correct path.
April 21, 2008 at 8:45 am
(Sorry for the late reply)
Okay, as far as I am seeing it now, your codes are doing what you are suppose to do right? Now, can you please tell where is your problem? Are you trying to archive your files that is done going into your destination to somewhere else? Please make me clear and we will go from there.
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply