May 3, 2013

Setting the File System Task properties dynamically in SSIS

We can set all the File System Task properties or values in variables, configuration file, database etc. For this it is necessary to set default values. 

Storing the File System Task properties in variables.

Step 1: Declare the variables for each property. In this example we will set the Source, Destination, Operation, and OverWriteDestinationFile properties.

Operation: This property is integer type which has following meaning:

Operation
Meaning
1
Move file
2
Delete File
3
Rename file
4
Set Attributes
5
Create directory
6
Copy directory
7
Move directory
8
Delete directory
9
Delete directory content
10
Copy file

Source: It is string type which the name of the file connection of source folder.

Destination: It is string type which the name of the file connection of destination folder.

OverWriteDestinationFile: It is Boolean type. If it is true then it allows the content in the destination folder. To create a Source and Destination file connection right click on Connection Manager and select New File Connection.

We will get File Connection Manager Editor. Select the Usage Type and Folder.


Now click on OK button. In the same way create the destination file connection.

Step 2:To declare variable go the SSIS -> Variables 


Click on Add Variable icon. Write down the variable name, scope, Data type and value for each property.



Step 3: Set the default values of all the properties of File System Task as shown in following figure:


Note: It is necessary to assign the default values it will be used only if we have not set the values of properties in the expression.

Step 3: Click on expression Tab. Click on the expression button. We will get the Property Expression Editor.


Step 4: Select Source property from Property combo box.  Click on expression button. We will get Expression Builder. Expand the Variable, drag and drop the SourceConn variable in the Expression text field and click on OK button.


Step 5: In the same way assign a variable to properties Destination, Operation, and OverwriteDestinationFile.


Click on Ok button.

Step 6: Right click on SSIS file connection in Connection manages and select the property. Click on the Expression button and set the Connection String property. In the same way assign DestinationPath variable to Destination Connection String property.



Step 7: Now run the package. If everything is fine we will get following screen:


Storing the File System Task variables in configuration file.

Step 1: Go to the SSIS -> Package Configuration

Step 2: Checked the Enable Package configuration and click on Add button.


Step 3: Click on Next button in the Welcome to the package configuration wizard. In the next window

Configuration type:  Choose XML configuration file

Configuration file name: Specify the configuration file name with path.

Click on Next button.


Step 4: Select the variables and properties which you want to keep in the configuration file. In this example we are selecting only value property of all variables.


Click on Next then Finish button.

Configuration file will look like this:



<?xml version="1.0"?>
<DTSConfiguration>
  <DTSConfigurationHeading>
    <DTSConfigurationFileInfo
      GeneratedBy="xxxx-PC\xxxx"
      GeneratedFromPackageName="Package"
      GeneratedFromPackageID="{A02ACEDC-9A5B-42C4-A207-C9ABA387853C}"
      GeneratedDate="11/13/2011 3:28:06 AM"/>
  </DTSConfigurationHeading>
  <Configuration
    ConfiguredType="Property"
    Path="\Package.Variables[User::DestinationConn].Properties[Value]"
    ValueType="String">
    <ConfiguredValue>Destination</ConfiguredValue>
  </Configuration>
  <Configuration
    ConfiguredType="Property"
    Path="\Package.Variables[User::DestinationPath].Properties[Value]"
    ValueType="String">
    <ConfiguredValue>C:\Destination</ConfiguredValue>
  </Configuration><Configuration
    ConfiguredType="Property"
    Path="\Package.Variables[User::Operation].Properties[Value]"
    ValueType="Int32">
    <ConfiguredValue>6</ConfiguredValue>
  </Configuration><Configuration
    ConfiguredType="Property"
    Path="\Package.Variables[User::SourceConn].Properties[Value]"
    ValueType="String">
    <ConfiguredValue>SSIS</ConfiguredValue>
  </Configuration>
  <Configuration
    ConfiguredType="Property"
    Path="\Package.Variables[User::SourcePath].Properties[Value]"
    ValueType="String">
    <ConfiguredValue>C:\SSIS</ConfiguredValue>
  </Configuration>
</DTSConfiguration>

1 comment:

  1. Anonymous6/20/2013

    Copy file Operation = 0 and not 10

    ReplyDelete