Hi I am experiencing a truncation of characters following the incidence of CHR(0) in my encrypted data colulm. I am attempting to iterate through the rows of the resultset in an SSIS Script Component. The OLE DB Source produces a column with the value: a=b8+0>o The ascii values are: a-97 -164 -136 -192 -181 =-61 b-98 8-56 -196 +-43 -164 _0 -142 -162 >-62 The SSIS Script component sees this as a=b8+ - truncating the trailing characters after the character. I am unsure if the OLE DB Source column is truncated when exposed in the InputBuffer to the SSIS Script Component or if the truncation is occurring as a behavior of the OLE DB Source itself. I have tried changing the code page and switching over to Unicode with same results. I am unable to apply any Replace methods because the CHR(0) is part of the encoded data that I need to decode. Do I need to send the data as a varbinary and do something else? Advance thanks to any efforts in responding.

Hi Marcus, Can you please put some more details on the issue? 1) what is the source of your data - Oracle,SQL,Excel,TextFile or something else? 2) What is the data type for the column where the encoded value is stored? Ideally your OLEDB source should use the similar data type as stored in the source.Also there is an alternate way to query the source data through the script component and loop through data in the same script component. That way you'll have more control on data.

Thank you for your response Manish. The data source is SQL 2K5. The data type of the column is varchar(16) in the database table and DT_STR Length=16 in the SSIS OLEDBSOurce Column property. I am open to using the script component to perform the query. Can you point me in that direction with an example? Would I use System.SqlClient classes such as command and reader objects ?

 Public Overrides Sub PreExecute() Conn = New OleDbConnection(Me.Variables.sMyConnString) Conn.Open() 'MsgBox("Connection successfully established") Dim cmd As New OleDbCommand cmd = Conn.CreateCommand() cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = " dbo.GetReportExtract" cmd.Parameters.Add("@iDaysToAdd", OleDbType.Integer) cmd.Parameters("@iDaysToAdd").Value = -2 sqlReader = cmd.ExecuteReader() End Sub Do While sqlReader.Read With Me.<YourBufferName> .AddRow() .H1 = sqlReader("H1").ToString() .H2 = sqlReader("H2").ToString() .H3 = sqlReader("H3").ToString() .H4 = sqlReader("H4").ToString() .H5 = sqlReader("H5").ToString() '.H6 = sqlReader("H6").ToString() '.H7 = sqlReader("H7").ToString() '.H8 = sqlReader("H8").ToString() 'etc, etc, etc for all columns. End With Loop Public Overrides Sub PostExecute() If Not sqlReader.IsClosed Then sqlReader.Close() End If If Not Conn.State = ConnectionState.Open Then Conn.Close() End If End Sub Above is the code i used to do the same. In the first step open a connection in PreExecute() method using the package's connection object. CreateOutPutRows() is the method where you can read your resultset one by one and generate required output. Same will be available as output columns in the next component. In the end i've just ensured tthat connection is closed. Hope this will help. Please mark as answer is it helps you.

 Public Overrides Sub PreExecute() Conn = New OleDbConnection(Me.Variables.sMyConnString) Conn.Open() 'MsgBox("Connection successfully established") Dim cmd As New OleDbCommand cmd = Conn.CreateCommand() cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = " dbo.GetReportExtract" cmd.Parameters.Add("@iDaysToAdd", OleDbType.Integer) cmd.Parameters("@iDaysToAdd").Value = -2 sqlReader = cmd.ExecuteReader() End Sub Do While sqlReader.Read With Me.<YourBufferName> .AddRow() .H1 = sqlReader("H1").ToString() .H2 = sqlReader("H2").ToString() .H3 = sqlReader("H3").ToString() .H4 = sqlReader("H4").ToString() .H5 = sqlReader("H5").ToString() '.H6 = sqlReader("H6").ToString() '.H7 = sqlReader("H7").ToString() '.H8 = sqlReader("H8").ToString() 'etc, etc, etc for all columns. End With Loop Public Overrides Sub PostExecute() If Not sqlReader.IsClosed Then sqlReader.Close() End If If Not Conn.State = ConnectionState.Open Then Conn.Close() End If End Sub Above is the code i used to do the same. In the first step open a connection in PreExecute() method using the package's connection object. CreateOutPutRows() is the method where you can read your resultset one by one and generate required output. Same will be available as output columns in the next component. In the end i've just ensured tthat connection is closed. Hope this will help. Please mark as answer is it helps you.