Pages

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

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



Following is the input data and desired output for that




Answer:

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)
Try
If Row.RecType = "A" Then
int_Sno = int_Sno + 1
End If
Row.Sno = int_Sno

Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class
******************************************************************

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



Hope this will help you.