May 16, 2012 at 6:22 am
In order to provide a 1 to 1 tracking mechanism from the flat-file source to the extract layer, the name of the source file and the row-number should make it into the destination table.
I have figured out the sourcefile-name (C# script).
Is there a way to do this for the source row number?
TIA
May 16, 2012 at 7:24 am
A row number would be a useful addition to the flat file source component.
I think your best approach is another script component.
May 16, 2012 at 11:47 am
You could pre-process the file to append a line number to each line prior to processing the file with SSIS.
You could use a Transformation Script Component as mentioned to add the line number to a column in the pipeline as it passes through the Component.
A third option is to load the data into a table with an IDENTITY column while having the Max Commit Size on an OLE DB Destination set to 1, i.e. to load the file one row at a time. Committing each row one at a time can be extremely slow relative to committing thousands of rows at a time. This approach requires no custom coding in SSIS, only a change to the schema of the staging table, however performance can be a barrier to using this approach if the source files are very large or the relative delay cannot be tolerated.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 16, 2012 at 4:22 pm
Good ideas, thank´s.
I´m a bit uncertain about the "transformation script component".
What do you have in mind?
May 16, 2012 at 4:33 pm
When you drop a Script Component onto a Data Flow design surface you are asked what type of Script Component to implement. You would want to choose Transformation. This Script Component will sit somewhere in between your Flat File Source and your Database Destination. As you process rows through the Script Component you will populate a column meant to hold the line number and that column will be fed into your Database Destination and eventually into your table.
I found this on Google. I have not run it through to see if the demo holds up but it looks like what you want and will hopefully get you there:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2012 at 5:07 am
I believe by Row number you mean total number of rows in the source file.
There's a pretty easy way of doing this ... below is a script to help you out (You can add this code to your existing Script Task )
(This will open the file in read mode )
Dim fullfilename As String
fullfilename = location of your source file
Dim fileReader As System.IO.StreamReader
fileReader = _
My.Computer.FileSystem.OpenTextFileReader(fullfilename)
(This will store the number of rows in countRows)
Dim countRows As Long = 0
Do While fileReader.Peek() >= 0
fileReader.ReadLine()
countRows = countRows + 1
Loop
fileReader.Close()
At the end you can assign the value of countRows to a package variable & use it wherever you like.
Hope this is of some good...:-)
June 7, 2012 at 3:20 am
This is what I found:
Drop a script component onto your data-flow right after the flat-file component.
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public override void PreExecute()
{
base.PreExecute();
/*
Add your code here for preprocessing or remove if not needed
*/
}
public override void PostExecute()
{
base.PostExecute();
/*
Add your code here for postprocessing or remove if not needed
You can set read/write variables here, for example:
Variables.MyIntVar = 100
*/
}
int rowNumber = 1;
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
Row.RowNumber = rowNumber;
rowNumber += 1;
}
}
Now add a derived column to pick up the RowNumber, and Bob's your uncle!
Thank's for all your feedback.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply