File System task

Question

File System task

srilu_bannu

Hall of Fame

Points: 3100
More actions
April 21, 2011 at 2:51 pm

#238453

Hi all ,
I have a requirement to save the source files and maintain history of the files.
I have used File System Task to deliver the file to a directory by creating the variable and i have checked the OverWriteDestination to false because i want to maintain the history of the files .
I can deliver the file to the specified location perfectly for the first time but when i run it second time it is giving me error saying that the file already exists .Now is there anyway i can deliver the file with name NAME_20110421 today.dat and the file name changes with date if it is delivered tomorrow like NAME_20110422 and soon..
I want the filename i am going to deliver to be changed every time i run the package with date so that i will not get the error again saying the package already exists.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

srilu_bannu (4/21/2011)
Hi all ,
I have a requirement to save the source files and maintain history of the files.
I have used File System Task to deliver the file to a directory by creating the variable and i have checked the OverWriteDestination to false because i want to maintain the history of the files .
I can deliver the file to the specified location perfectly for the first time but when i run it second time it is giving me error saying that the file already exists .Now is there anyway i can deliver the file with name NAME_20110421 today.dat and the file name changes with date if it is delivered tomorrow like NAME_20110422 and soon..
I want the filename i am going to deliver to be changed every time i run the package with date so that i will not get the error again saying the package already exists.

I think I know what you are trying to do, but am unclear as to what you have tried so far. Can you clarify what you mean by "by creating the variable"? Where are you using the variable and how have you defined it, and is EvaluateAsExpression true on it?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

srilu_bannu Hall of Fame Points: 3100 More actions · Answer 2

I am sorry for not being clear .

This what i did:

1.Right click on the package and clicked variables.

2.to the left of a package you will see variables

3.i have added a variable by clicking add button.

4.I gave :

Name: Archive.

Scope:PackageName.

DataType: String.

Value: c:\Temp\Example.DAT ---I have created a temporary folder in c Drive.

5.I have dragged File System Task followed by my Data Flow Task.

In FTP Task:

IsDestinationPathVariable:True

DestinationVariable: User::Archive

OverWriteDestination:False

Operation:Copy File

IsSourcePathVaraible:False

SourceConnection:Name of the source connection manager i have been using for my package,i have created this in the connection manager section with the source path.

I ran this package which works perfectly for first time but if i want to run it again it is giving me error saying the name already exists which is reasonable .Now what should i do to have history of my files with different names(i think they can be differentiated by dates) in the folder?

Anjan Wahwar SSCommitted Points: 1617 More actions · Answer 3

Use expressions to generate folder name at run time in the format Name_MMDDYYYY instead of giving hard coded value.

__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 4

srilu_bannu (4/22/2011)
I am sorry for not being clear .
This what i did:
1.Right click on the package and clicked variables.
2.to the left of a package you will see variables
3.i have added a variable by clicking add button.
4.I gave :
Name: Archive.
Scope:PackageName.
DataType: String.
Value: c:\Temp\Example.DAT ---I have created a temporary folder in c Drive.
5.I have dragged File System Task followed by my Data Flow Task.
In FTP Task:
IsDestinationPathVariable:True
DestinationVariable: User::Archive
OverWriteDestination:False
Operation:Copy File
IsSourcePathVaraible:False
SourceConnection:Name of the source connection manager i have been using for my package,i have created this in the connection manager section with the source path.
I ran this package which works perfectly for first time but if i want to run it again it is giving me error saying the name already exists which is reasonable .Now what should i do to have history of my files with different names(i think they can be differentiated by dates) in the folder?

Thanks for supplying the additional info.

In the variables pane click on the Archive variable and open the Properties window (Press F4). In the Properties window set EvaluateAsExpression = True for the Archive variable. Then set the Expression property to:

"C:\\Temp\\Example_" + (DT_STR,4,1252)YEAR(GETDATE()) + RIGHT("0" + (DT_STR,2,1252)MONTH(GETDATE()),2) + RIGHT("0" + (DT_STR,2,1252)DAY(GETDATE()),2) + ".DAT"

This will make SSIS evaluate the variable as an expression at runtime so it can be used in your File System Task as the destination file name.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

srilu_bannu Hall of Fame Points: 3100 More actions · Answer 5

Thank You opc.three .

Your expression worked like charm.