Tuesday, October 6, 2009

Delete bunch of dynamic files

Question: How to delete all files from a directory?

Answer: Please follow the following steps:

Step1: Add a For each loop container. & Inside it add a File system task.

Step2: Add 2 variables (Scope: Package level)
  1. Var_FilePath
  2. Var_InputFolder

Step3; Double click on Foreach loop container and in expression map the "Directory" property to Var-InputFolder. (Click on the image for larger view)

Please select the "Reterive file name" property to fully qualified.

Step4; Map the Var_FilePath in variable mapping tab.

Step5: Double click on File system task and change the operation to "Delete file" and Set IsSourcePathVariable property to TRUE. Then map your Var_FilePath to it. (Click Image)

Step6: Now select the File System Task and press F4 for property window. Set the "Delay validation" to True.

Step7: Execute your package.

Hope this will help you.


Allocating a 'Set ID' & Extracting data in groups

Allocate a Set ID for a Batch of data and make a group.
Retain Values from Previous Rows.

Following is the input data and desired output for that


Drag a DFT in the control flow and add the following components.

For achieving this you need to use Script Component in DFT. and the following below steps: Before that you need to select Header as input column.

Create an output column in script component

Add the following code in the Script component.
Imports System
Imports System.Data
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Inherits UserComponent
Dim int_Sno as integer ' // this variable should be global for the script (class level)
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Row.RecType = "A" Then
int_Sno = int_Sno + 1
End If
Row.Sno = int_Sno

Catch ex As Exception
End Try
End Sub
End Class

Now execute your package and you will get the desired output (Given below)

Hope this will help you.