Note: The Result Name is set to 0.
Once the SQL Task will be executed this
object variable will containing the entire result set returned by the query.
In the collections tab do the below
settings:
Use the object variable in the For each
loop.
Set the enumerator to Foreach ADO Enumerator.
Next goto the Variable mapping and do
the below mentioned settings:
Select the 2 string variable to which
the file name and file path will be saved for each iteration of the object variable.
Note: Set the index starting from 0 and the order should be same as
the order of the columns fetched in the SELECT query above.
Once this is done we put a Data Flow Task(DFT) in the above For Each Loop.
In the DFT use:
1.Flat File Source to fetch the data from the input file.
2.Derived
Column transformation to change the string date field to DB_DBDATE
3.OLEDB Destination to send the valid records to database table.
4.Flat File destination to log the error
records.
Let’s have a look at the layout
of the DFT.
In the Flat file source we make a new connection manager where we set browse and
set the file path of an existing file.
In the Advanced tab set the Column Names
as per the need and set the data type to which you can convert the data without
the loss of information like the numeric columns. Leave the other to default or
set the max width of the string columns.
Once the Flat File connection manager
is set up select the columns you need in the flow and your done.
Next connect the Flat File Source to
the Derived column to change the DOB column to DT_DBDATE format.
To do this in the derived column add
a new column give it a name and set the expression to :
(DT_DBDATE)(SUBSTRING(DOB,4,2) + "-" + SUBSTRING(DOB,1,2) + "-" + SUBSTRING(DOB,7,4))
Once the expression is set click the
Configure Error Output button at the bottom.
In case of error in the Derived Column
transform (e.g. the date field is No Value, “00/00/000” or any invalid
date) set it to redirect row so that it can be logged and analyzed.
For the correct records send the data
to Table
In the OLEDB Destination:
Select the OLEDB Connection needed to connect to the destination
Data base.
Select the table where the columns need to be inserted.
Goto the mapping
Go the mapping and do the mapping as required and click
OK.
For the error records(red arrow) after
the derived Column set a flat file destination to log the error records.
The DFT is now all set.
Now the DFT Flat File source needs to
be made dynamic.
To achieve this follow the steps below.
Goto the connection manager right click->
Properties
In the properties pane for the above connection manager:
Set the
DelayValidation
to True
Expand the
Expressions
by clicking the ellipse.
On the Property Expressions Editor window:
In the Property column select the
ConnectionString
property from the drop down list.
Now expand the Expression column by clicking the ellipse.
An Expression Builder window will pop up.
On the Expression Builder window:
Set the
Expression
as:
@[User::FilePath]+ “\\” + @[User::FileName]
Click the Evaluate expression button at the left hand bottom
to see if the expression is creating the desired output.
Click OK.
Click OK on the Property Expression Editor as well.
Save and build the package.
Now when you execute the package the package will take the filenames from the DB
table and dump the data in order in the final table.
You can download this package from here.
Let me know if you need any further clarification.