Wednesday, February 12, 2014

Copy a file in File System Task in SSIS by examples


In SSIS (Sql server integration services) we can copy any types of file from one location to another location by using File System Task.

Suppose we have a text file at C:\Source\ssis.txt which we want to copy at any other location say C:\Destination\

Step 1:  Create a new integration services project.

Step 2: In control flow tab, drag and drop the File System Task.


Step 3:  Double click on File System Task. We will get the File System Task Editor.

Name:  It can be any name of task. In this example task name is Copy file.

Description: It is description of the task. In this example description is Copy at c:\Destination\

Operation:  There are ten different operations performed by File System task. They are:

Task Name
Use
Create directory
To create a new folder at given path.
Delete directory
To delete an existing folder.
Delete directory content
To delete the all content of a folder.
Move directory
To move a folder from one location to other.
Copy directory
To copy a folder from one location to other.
Copy file
To copy a file from one location to other.
Move file
To move a file from one location to other.
Rename file
To rename a given file.
Set Attributes 
To set the four attributes of a specific a file or folder. Attributes are:
  1. Hidden
  2. Read only
  3. Archive
  4. System

In this example we have to copy a file from one location to other so operation name will be Copy file


IsSorcePathVariable: It can be either true or false. If it is true then path of the source folder will stored in a variable. If it is false then path of the source folder will be static.  

In this example we will go for both the options.

Option 1: Set the IsSorcePathVariable to true.

Source variable: To specify path of the source folder which we have to copy. Select <New Variable…> from combo box. We will get the Add variable window.

Container: To specify the scope of the variable. In this example we will use Package (write name of your package). It means scope of variable is whole the package.

Name: Any name of the variable. In this example name of the variable is SourceFolderPath.

NameSpace: It can be either User of System. User means user defined variable. We can write any name of namespace of user defined variable. In this example we will keep User.
  
Value Type: Data type of variable. Since we have to store path of a folder so, we will choose String.

Value: It keeps the any value of a variable. In this example it will path of the source file i.e. C:\Source\ssis.txt


Now click on OK button.

Option 2: Set the IsSorcePathVariable to false.

SourceConnection: To specify the path of the source folder. Choose the <New connection…> from combo box. We will get File Connection Manager Editor.

Usage type: In this example choose the Existing file.

File: Click browse button to choose the source file which you want to copy.


Now click on OK button.

IsDestinationPathVariable: It can be either true or false. If it is true then path of the destination folder will be stored in a variable. If it is false then path of the source folder will be static.  Same way as we have done in case of source file. In this example we will set to False.

DestinationConnection: Choose the destination folder path where we have to copy the folder. In this example it is c:\Destination

OverwriteDestination: It can be either true or false. If it is true then while copying it will overwrite if the files and folders of source folder are already present in the destination folder if it is false then it will be not been overwritten. In this example we will keep True.

  
Now click on OK button.

Step 4: Now we will run the package. For this press F5 button from keyboard. If everything is fine we will get following screen: 

No comments:

Post a Comment