How to Read Multiple File in SSIS
Most of the SSIS developers want to get the list of the files from a folder (and maybe subfolders) and save them in to an object variable or loop though them one by one so that they can use it in an object within SSIS. (e.g. *.xml, *.bmp, *.txt, most of all Excel files *.xls and etc...)
I have followed some steps it follows….
Step 1. Selected For Each Loop Container and Script task.
Most of the SSIS developers want to get the list of the files from a folder (and maybe subfolders) and save them in to an object variable or loop though them one by one so that they can use it in an object within SSIS. (e.g. *.xml, *.bmp, *.txt, most of all Excel files *.xls and etc...)
I have followed some steps it follows….
Step 1. Selected For Each Loop Container and Script task.
Step 2. Creating variables
Step 3. Double click for each loop container task
Step 4. Then Double click Script Task
Step 5.Next Click Design Scipt and enter this code.
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
'
' Add your code here
'
MsgBox(Dts.Variables("var_ConnectionStr").Value.ToString)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
