Help on Script Component assignment of output variable

Hi all,

Actually I′m working with the beta 2 of the Sql Server 2005 with SSIS. And it′s great fun! But I′m now experience a problem:

I′m working with a script component. In that script component I would like to assign a value to a specific column ("Row.Formula"). The type of "Row.Formula" is Unicode text stream [DT_NTEXT]. Whenever I try to assign a byte array or a string to that field I am getting a compliation error. I have to assign a variable with the type "blobcolumn". But I cannot initialize this variable because "blobcolumn" has no public constructor.

Any thoughts on that?

The code sample:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Avanade.AMCS.DataProcessorReplacement.Utility

Public Class ScriptMain Inherits UserComponent

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Dim wholeRowBuffer As Byte()
Dim columns() As String

wholeRowBuffer = Row.WholeLine.GetBlobData(0, CInt(Row.WholeLine.Length))

columns = ScriptComponentHelper.RetrieveStringArrayOutOfBuffer(wholeRowBuffer)

Row.Name = columns(1)
Row.MenuName = columns(2)
Row.CascadeName = columns(3)

Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(columns(4))

'Conversion compliation error - cannot convert from byte array to 'Microsoft.SqlServer.Dts.Pipeline.BlobColumn'

Row.Formula = buffer

End Sub

End Class

To access BLOB data in Script component, please use AddBlobData and GetBlobData.

For example:

Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(columns(4))

Row.Formula.AddBlobData(buffer)

|||

---

Imports System
Imports System.Xml
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim i As Integer
Dim CoverNote As String()

CoverNote = Split(Row.CoverNote.ToString, "^")

For i = 0 To UBound(CoverNote)
With Output0Buffer
.AddRow()
.Key = Row.Key
.AgentCode = Row.AgentCode
.TransactionDate = Row.TransactionDate
.Branchcode = Row.BranchCode
.BatchNo = Row.BatchNo
.NoOfCoverNotes = Row.NoOfCoverNotes
.TotalGrossPremium = Row.TotalGrossPremium
.MedicalGrsPrem = Row.MedicalGrsPrem
Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(CoverNote(i))
.CoverNote = Row.CoverNote.AddBlobData(buffer)
End With
Next
End Sub
End Class

The above scripting which highlightted wtih red color was encounter error.
The error msg : Expression doed not produce value.
Note: sources column for CoverNote is text data type which contain a lot multivalue.
example 001^002^003^004^005

Anyonce know how to write the correct scripting for handle such column with text data type.I dont have any idea for this case with text data type.

Thanks in advance.

|||

The line should just read

Row.CoverNote.AddBlobData(...

So remove the assignment part of the highlighted statement; AddBlobData() returns nothing.

|||Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim i As Integer
Dim CoverNote As String()

CoverNote = Split(Row.CoverNote.ToString, "^")

For i = 0 To UBound(CoverNote)
With Output0Buffer
.AddRow()
.Key = Row.Key
.AgentCode = Row.AgentCode
.TransactionDate = Row.TransactionDate
.Branchcode = Row.BranchCode
.BatchNo = Row.BatchNo
.NoOfCoverNotes = Row.NoOfCoverNotes
.TotalGrossPremium = Row.TotalGrossPremium
.MedicalGrsPrem = Row.MedicalGrsPrem
Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(CoverNote(i))
Row.CoverNote.AddBlobData(buffer)
End With
Next
End Sub
End Class

Thanks jaegd.

I had try to modify the code as per suggested.

Other columns output were fine and ok, just CoverNote result output was NULL value.

Any other script I miss out and need to add in or modified to able support this CoverNOte with text data type?

Appreciate for any help.

|||

What is the pipeline data type of the input column Row.CoverNote -- DT_TEXT,DT_NEXT, or something else?

|||The pipeline data type of the input column Row.CoverNote is DT_TEXT|||

Since the source pipeline type is DT_TEXT , before you can meaningfully called Split() on the character data contained in the blob, convert the pipeline data type to a .NET String first. Retrieval of DT_TEXT data is performed by calling GetBlobData() on the named/typed accessor and then decoding the array of bytes returned.

In other words, replace the line

CoverNote = Split(Row.CoverNote.ToString, "^")

with

Dim blobLength As Int32 = Convert.ToInt32(Row.CoverNote.Length)

Dim blobData() As Byte = Row.CoverNote.GetBlobData(0, blobLength)

Dim blobCodePage As Int32 = Row.CoverNote.ColumnInfo.CodePage

Dim joinedCoverNote As String = Text.Encoding.GetEncoding(blobCodePage).GetString(blobData)

CoverNote = Split(joinedCoverNote, "^")

|||

I had tried the sripting as per suggested.

The problems had been solved.

Thanks for your helping.

Help on Script Component assignment of output variable

Hi all,

Actually I′m working with the beta 2 of the Sql Server 2005 with SSIS. And it′s great fun! But I′m now experience a problem:

I′m working with a script component. In that script component I would like to assign a value to a specific column ("Row.Formula"). The type of "Row.Formula" is Unicode text stream [DT_NTEXT]. Whenever I try to assign a byte array or a string to that field I am getting a compliation error. I have to assign a variable with the type "blobcolumn". But I cannot initialize this variable because "blobcolumn" has no public constructor.

Any thoughts on that?

The code sample:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Avanade.AMCS.DataProcessorReplacement.Utility

Public Class ScriptMain Inherits UserComponent

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Dim wholeRowBuffer As Byte()
Dim columns() As String

wholeRowBuffer = Row.WholeLine.GetBlobData(0, CInt(Row.WholeLine.Length))

columns = ScriptComponentHelper.RetrieveStringArrayOutOfBuffer(wholeRowBuffer)

Row.Name = columns(1)
Row.MenuName = columns(2)
Row.CascadeName = columns(3)

Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(columns(4))

'Conversion compliation error - cannot convert from byte array to 'Microsoft.SqlServer.Dts.Pipeline.BlobColumn'

Row.Formula = buffer

End Sub

End Class

To access BLOB data in Script component, please use AddBlobData and GetBlobData.

For example:

Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(columns(4))

Row.Formula.AddBlobData(buffer)

|||

---

Imports System
Imports System.Xml
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim i As Integer
Dim CoverNote As String()

CoverNote = Split(Row.CoverNote.ToString, "^")

For i = 0 To UBound(CoverNote)
With Output0Buffer
.AddRow()
.Key = Row.Key
.AgentCode = Row.AgentCode
.TransactionDate = Row.TransactionDate
.Branchcode = Row.BranchCode
.BatchNo = Row.BatchNo
.NoOfCoverNotes = Row.NoOfCoverNotes
.TotalGrossPremium = Row.TotalGrossPremium
.MedicalGrsPrem = Row.MedicalGrsPrem
Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(CoverNote(i))
.CoverNote = Row.CoverNote.AddBlobData(buffer)
End With
Next
End Sub
End Class

The above scripting which highlightted wtih red color was encounter error.
The error msg : Expression doed not produce value.
Note: sources column for CoverNote is text data type which contain a lot multivalue.
example 001^002^003^004^005

Anyonce know how to write the correct scripting for handle such column with text data type.I dont have any idea for this case with text data type.

Thanks in advance.

|||

The line should just read

Row.CoverNote.AddBlobData(...

So remove the assignment part of the highlighted statement; AddBlobData() returns nothing.

|||Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim i As Integer
Dim CoverNote As String()

CoverNote = Split(Row.CoverNote.ToString, "^")

For i = 0 To UBound(CoverNote)
With Output0Buffer
.AddRow()
.Key = Row.Key
.AgentCode = Row.AgentCode
.TransactionDate = Row.TransactionDate
.Branchcode = Row.BranchCode
.BatchNo = Row.BatchNo
.NoOfCoverNotes = Row.NoOfCoverNotes
.TotalGrossPremium = Row.TotalGrossPremium
.MedicalGrsPrem = Row.MedicalGrsPrem
Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(CoverNote(i))
Row.CoverNote.AddBlobData(buffer)
End With
Next
End Sub
End Class

Thanks jaegd.

I had try to modify the code as per suggested.

Other columns output were fine and ok, just CoverNote result output was NULL value.

Any other script I miss out and need to add in or modified to able support this CoverNOte with text data type?

Appreciate for any help.

|||

What is the pipeline data type of the input column Row.CoverNote -- DT_TEXT,DT_NEXT, or something else?

|||The pipeline data type of the input column Row.CoverNote is DT_TEXT|||

Since the source pipeline type is DT_TEXT , before you can meaningfully called Split() on the character data contained in the blob, convert the pipeline data type to a .NET String first. Retrieval of DT_TEXT data is performed by calling GetBlobData() on the named/typed accessor and then decoding the array of bytes returned.

In other words, replace the line

CoverNote = Split(Row.CoverNote.ToString, "^")

with

Dim blobLength As Int32 = Convert.ToInt32(Row.CoverNote.Length)

Dim blobData() As Byte = Row.CoverNote.GetBlobData(0, blobLength)

Dim blobCodePage As Int32 = Row.CoverNote.ColumnInfo.CodePage

Dim joinedCoverNote As String = Text.Encoding.GetEncoding(blobCodePage).GetString(blobData)

CoverNote = Split(joinedCoverNote, "^")

|||

I had tried the sripting as per suggested.

The problems had been solved.

Thanks for your helping.

Help on Script Component assignment of output variable

Hi all,

Actually I′m working with the beta 2 of the Sql Server 2005 with SSIS. And it′s great fun! But I′m now experience a problem:

I′m working with a script component. In that script component I would like to assign a value to a specific column ("Row.Formula"). The type of "Row.Formula" is Unicode text stream [DT_NTEXT]. Whenever I try to assign a byte array or a string to that field I am getting a compliation error. I have to assign a variable with the type "blobcolumn". But I cannot initialize this variable because "blobcolumn" has no public constructor.

Any thoughts on that?

The code sample:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Avanade.AMCS.DataProcessorReplacement.Utility

Public Class ScriptMain Inherits UserComponent

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Dim wholeRowBuffer As Byte()
Dim columns() As String

wholeRowBuffer = Row.WholeLine.GetBlobData(0, CInt(Row.WholeLine.Length))

columns = ScriptComponentHelper.RetrieveStringArrayOutOfBuffer(wholeRowBuffer)

Row.Name = columns(1)
Row.MenuName = columns(2)
Row.CascadeName = columns(3)

Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(columns(4))

'Conversion compliation error - cannot convert from byte array to 'Microsoft.SqlServer.Dts.Pipeline.BlobColumn'

Row.Formula = buffer

End Sub

End Class

To access BLOB data in Script component, please use AddBlobData and GetBlobData.

For example:

Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(columns(4))

Row.Formula.AddBlobData(buffer)

|||

---

Imports System
Imports System.Xml
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim i As Integer
Dim CoverNote As String()

CoverNote = Split(Row.CoverNote.ToString, "^")

For i = 0 To UBound(CoverNote)
With Output0Buffer
.AddRow()
.Key = Row.Key
.AgentCode = Row.AgentCode
.TransactionDate = Row.TransactionDate
.Branchcode = Row.BranchCode
.BatchNo = Row.BatchNo
.NoOfCoverNotes = Row.NoOfCoverNotes
.TotalGrossPremium = Row.TotalGrossPremium
.MedicalGrsPrem = Row.MedicalGrsPrem
Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(CoverNote(i))
.CoverNote = Row.CoverNote.AddBlobData(buffer)
End With
Next
End Sub
End Class

The above scripting which highlightted wtih red color was encounter error.
The error msg : Expression doed not produce value.
Note: sources column for CoverNote is text data type which contain a lot multivalue.
example 001^002^003^004^005

Anyonce know how to write the correct scripting for handle such column with text data type.I dont have any idea for this case with text data type.

Thanks in advance.

|||

The line should just read

Row.CoverNote.AddBlobData(...

So remove the assignment part of the highlighted statement; AddBlobData() returns nothing.

|||Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim i As Integer
Dim CoverNote As String()

CoverNote = Split(Row.CoverNote.ToString, "^")

For i = 0 To UBound(CoverNote)
With Output0Buffer
.AddRow()
.Key = Row.Key
.AgentCode = Row.AgentCode
.TransactionDate = Row.TransactionDate
.Branchcode = Row.BranchCode
.BatchNo = Row.BatchNo
.NoOfCoverNotes = Row.NoOfCoverNotes
.TotalGrossPremium = Row.TotalGrossPremium
.MedicalGrsPrem = Row.MedicalGrsPrem
Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(CoverNote(i))
Row.CoverNote.AddBlobData(buffer)
End With
Next
End Sub
End Class

Thanks jaegd.

I had try to modify the code as per suggested.

Other columns output were fine and ok, just CoverNote result output was NULL value.

Any other script I miss out and need to add in or modified to able support this CoverNOte with text data type?

Appreciate for any help.

|||

What is the pipeline data type of the input column Row.CoverNote -- DT_TEXT,DT_NEXT, or something else?

|||The pipeline data type of the input column Row.CoverNote is DT_TEXT|||

Since the source pipeline type is DT_TEXT , before you can meaningfully called Split() on the character data contained in the blob, convert the pipeline data type to a .NET String first. Retrieval of DT_TEXT data is performed by calling GetBlobData() on the named/typed accessor and then decoding the array of bytes returned.

In other words, replace the line

CoverNote = Split(Row.CoverNote.ToString, "^")

with

Dim blobLength As Int32 = Convert.ToInt32(Row.CoverNote.Length)

Dim blobData() As Byte = Row.CoverNote.GetBlobData(0, blobLength)

Dim blobCodePage As Int32 = Row.CoverNote.ColumnInfo.CodePage

Dim joinedCoverNote As String = Text.Encoding.GetEncoding(blobCodePage).GetString(blobData)

CoverNote = Split(joinedCoverNote, "^")

|||

I had tried the sripting as per suggested.

The problems had been solved.

Thanks for your helping.

sql

Help on a query

Hi,
Iam trying to figure out the query to achieve the output depicted below

create table master (
iss_dtl_seq_nbr int
)

create table child1(
iss_dtl_seq_nbr int,
line_no int
)

create table child2(
iss_dtl_seq_nbr int,
line_no int
)

insert into master
select 1

insert into child1
select 1, 1
insert into child1
select 1, 2

insert into child2
select 1, 1

insert into child2
select 1, 2

insert into child2
select 1, 3

SELECT MASTER.ISS_DTL_SEQ_NBR,CHILD1.LINE_NO, CHILD2.LINE_NO
FROM CHILD1, CHILD2, MASTER
WHERE MASTER.ISS_DTL_SEQ_NBR = CHILD1.ISS_DTL_SEQ_NBR
AND MASTER.ISS_DTL_SEQ_NBR = CHILD2.ISS_DTL_SEQ_NBR
AND CHILD1.LINE_NO = CHILD2.LINE_NO
ORDER BY CHILD1.LINE_NO, CHILD2.LINE_NO

Expected Output:

ISS_DTL_SEQ_NBR LINE_NO LINE_NO
----- ---- ----
1 1 1
1 2 2
1 NULL 3

Can anybody help with the query to achieve this?

Thanks.On 9 Sep 2004 09:33:21 -0700, Sudhir wrote:

>Hi,
> Iam trying to figure out the query to achieve the output depicted below
>create table master (
>iss_dtl_seq_nbr int
>)
>create table child1(
>iss_dtl_seq_nbr int,
>line_no int
>)
>create table child2(
>iss_dtl_seq_nbr int,
>line_no int
>)
>insert into master
>select 1
>insert into child1
>select 1, 1
>insert into child1
>select 1, 2
>insert into child2
>select 1, 1
>insert into child2
>select 1, 2
>insert into child2
>select 1, 3
>
>SELECT MASTER.ISS_DTL_SEQ_NBR,CHILD1.LINE_NO, CHILD2.LINE_NO
>FROM CHILD1, CHILD2, MASTER
>WHERE MASTER.ISS_DTL_SEQ_NBR = CHILD1.ISS_DTL_SEQ_NBR
>AND MASTER.ISS_DTL_SEQ_NBR = CHILD2.ISS_DTL_SEQ_NBR
>AND CHILD1.LINE_NO = CHILD2.LINE_NO
>ORDER BY CHILD1.LINE_NO, CHILD2.LINE_NO
>Expected Output:
>ISS_DTL_SEQ_NBR LINE_NO LINE_NO
>----- ---- ----
>1 1 1
>1 2 2
>1 NULL 3
>Can anybody help with the query to achieve this?
>Thanks.

Hi Sudhir,

Thanks for providing the statements to recreate your table structure and
data. The following query will produce the expected output:

select coalesce(master.iss_dtl_seq_nbr, child2.iss_dtl_seq_nbr) AS
iss_dtl_seq_nbr,
child1.line_no, child2.line_no
from master
inner join child1
on master.iss_dtl_seq_nbr = child1.iss_dtl_seq_nbr
right outer join child2
on child1.iss_dtl_seq_nbr = child2.iss_dtl_seq_nbr
and child1.line_no = child2.line_no
order by child2.line_no, child1.line_no

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try this:

select master.iss_dtl_seq_nbr, child1.line_no, child2.line_no
from child1 full outer join child2 on child1.iss_dtl_seq_nbr =
child2.iss_dtl_seq_nbr and child1.line_no = child2.line_no
join master on master.iss_dtl_seq_nbr =
isnull(child1.iss_dtl_seq_nbr, child2.iss_dtl_seq_nbr)

Shervin

s.sudhir@.gmail.com (Sudhir) wrote in message news:<80a5355b.0409090833.4ccd6d2b@.posting.google.com>...
> Hi,
> Iam trying to figure out the query to achieve the output depicted below
> create table master (
> iss_dtl_seq_nbr int
> )
> create table child1(
> iss_dtl_seq_nbr int,
> line_no int
> )
> create table child2(
> iss_dtl_seq_nbr int,
> line_no int
> )
> insert into master
> select 1
> insert into child1
> select 1, 1
> insert into child1
> select 1, 2
> insert into child2
> select 1, 1
> insert into child2
> select 1, 2
> insert into child2
> select 1, 3
>
> SELECT MASTER.ISS_DTL_SEQ_NBR,CHILD1.LINE_NO, CHILD2.LINE_NO
> FROM CHILD1, CHILD2, MASTER
> WHERE MASTER.ISS_DTL_SEQ_NBR = CHILD1.ISS_DTL_SEQ_NBR
> AND MASTER.ISS_DTL_SEQ_NBR = CHILD2.ISS_DTL_SEQ_NBR
> AND CHILD1.LINE_NO = CHILD2.LINE_NO
> ORDER BY CHILD1.LINE_NO, CHILD2.LINE_NO
> Expected Output:
> ISS_DTL_SEQ_NBR LINE_NO LINE_NO
> ----- ---- ----
> 1 1 1
> 1 2 2
> 1 NULL 3
> Can anybody help with the query to achieve this?
> Thanks.

Help Needed in Approach for Error Output In SSIS

Hi i have a issue i am loading data from a flat file into a relational Database and i am loading the data without dropping the Primary and Foreign Key constraints and i am sending the error rows into a error table for each table. This is becoming large over head is there a way to Load the entire error Row as a single Column in one Error Table.

This is occuring coz the input is being parsed and its dividing into columns,

One approach would be to used Derived Column Transformation and Substring all columns but it makes to writing long substring statement as some tables have 80 columns ,

is there a better way to handle these errors so that they can be looked at and Changes can be made to the Data ,

Also one more question is is there a way to Load in case of Duplicates load only the Recent Row By Date Column.

and send the old row according to Date into Error output.

Please suggest me what approach should i go for

Not really

You are best to create individual error tables. It means its easier for the user to see what the data is because it will be correctly split up.

|||

For your first question - why not use a error table with "row number" and "table name" columns and then rather than dumping the whole row into the error table -- log the row number and table name so that you can go back to the exact row and find the data you are having problem with. As the error table now has a standard structure (2 columns), you can use the same error table for logging errors for all the tables you are loading.

For you second question - You are trying to clean up the data (De-Duplication) so its better to stage the tables you are having duplicates in and then load it into the database after deleting the duplicate rows. This is hard to be done in the data-flow task because the order of the data coming in might not be based on date.

|||Another approach to de-duping rows is to use the Rank function - You can find an example using T-SQL here: http://rafael-salas.blogspot.com/2007/04/remove-duplicates-using-t-sql-rank.html and one with a custom component here: http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Rank-Transform.aspx

On the single column to capture an error, I have seen this implemented by using a text or xml column and generically creating the string to insert based on the row data. However, as Simon noted, this isn't typically very readable, and may require a seperate application to support reviewing the errors.

|||

is there way to log the record number in the Flatfile where the error is occuring and is there a way to log the Error Code and Error Description . Can u please guide me regarding this.

|||I need to capture the Database, Table, Input File Name, Input File Record Number; Key Data (does not need to be entire record), Error Code, Error Desc, Date and Time, SSIS Package/Program|||Basically you need to generate a rowNumber column in the dataflow. There are some samples:

http://support.microsoft.com/kb/908460

http://www.sqlis.com/93.aspx|||Thanks for the information it was very helpful i was thinking to capture the sorce filename and Destination table name too into the error table is there a way to capture this information.|||If you store those values in variables, you can use a derived column transform to bring them into the error flow.|||I am aware of this approach but is there way to store these values into variables , my connection managers are Dynamic i.e from a configurations table in sql server. is there way to capture the src and output names atleast in variable s to write it to error table|||

Dev2624 wrote:

Thanks for the information it was very helpful i was thinking to capture the sorce filename and Destination table name too into the error table is there a way to capture this information.

Why are you creating new threads on this topic? http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2195648&SiteID=1|||Phil's suggestion in the other thread is the same thing I was going to recommend.

Help Needed in Approach for Error Output In SSIS

Hi i have a issue i am loading data from a flat file into a relational Database and i am loading the data without dropping the Primary and Foreign Key constraints and i am sending the error rows into a error table for each table. This is becoming large over head is there a way to Load the entire error Row as a single Column in one Error Table.

This is occuring coz the input is being parsed and its dividing into columns,

One approach would be to used Derived Column Transformation and Substring all columns but it makes to writing long substring statement as some tables have 80 columns ,

is there a better way to handle these errors so that they can be looked at and Changes can be made to the Data ,

Also one more question is is there a way to Load in case of Duplicates load only the Recent Row By Date Column.

and send the old row according to Date into Error output.

Please suggest me what approach should i go for

Not really

You are best to create individual error tables. It means its easier for the user to see what the data is because it will be correctly split up.

|||

For your first question - why not use a error table with "row number" and "table name" columns and then rather than dumping the whole row into the error table -- log the row number and table name so that you can go back to the exact row and find the data you are having problem with. As the error table now has a standard structure (2 columns), you can use the same error table for logging errors for all the tables you are loading.

For you second question - You are trying to clean up the data (De-Duplication) so its better to stage the tables you are having duplicates in and then load it into the database after deleting the duplicate rows. This is hard to be done in the data-flow task because the order of the data coming in might not be based on date.

|||Another approach to de-duping rows is to use the Rank function - You can find an example using T-SQL here: http://rafael-salas.blogspot.com/2007/04/remove-duplicates-using-t-sql-rank.html and one with a custom component here: http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Rank-Transform.aspx

On the single column to capture an error, I have seen this implemented by using a text or xml column and generically creating the string to insert based on the row data. However, as Simon noted, this isn't typically very readable, and may require a seperate application to support reviewing the errors.

|||

is there way to log the record number in the Flatfile where the error is occuring and is there a way to log the Error Code and Error Description . Can u please guide me regarding this.

|||I need to capture the Database, Table, Input File Name, Input File Record Number; Key Data (does not need to be entire record), Error Code, Error Desc, Date and Time, SSIS Package/Program|||Basically you need to generate a rowNumber column in the dataflow. There are some samples:

http://support.microsoft.com/kb/908460

http://www.sqlis.com/93.aspx|||Thanks for the information it was very helpful i was thinking to capture the sorce filename and Destination table name too into the error table is there a way to capture this information.|||If you store those values in variables, you can use a derived column transform to bring them into the error flow.|||I am aware of this approach but is there way to store these values into variables , my connection managers are Dynamic i.e from a configurations table in sql server. is there way to capture the src and output names atleast in variable s to write it to error table|||

Dev2624 wrote:

Thanks for the information it was very helpful i was thinking to capture the sorce filename and Destination table name too into the error table is there a way to capture this information.

Why are you creating new threads on this topic? http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2195648&SiteID=1|||Phil's suggestion in the other thread is the same thing I was going to recommend.