customized error output?

I am using SSIS to load a lot of Excel, csv files. Some of the files will fail for various formating/validation reason. Is it a good way to capture the error and generate a nice error report so the provider can read it easily and correct the data files?

The error log of the package is difficult to read.

I have one suggestion:

1. instead of failing the component whenever there is an error during DataConversion on Lookup etc, redirect that row and then do a multicast.

2. Now you have two identical sets.

3.As you are aware each column has a lineage ID now generate a collection of column names and Lineage ID.

4. Perform an Inner join with the other replica on Lineage ID.

5. Retrieve the Erroneous Column name, Value and Row number.

6. Finally once you are done with all transformations, join the input obtained as mentioned above, on Rownumber and write out records as Erroneous and Non-Erroneous Data.

Custom transform component, change type or add output column

Would anyone happen to have any pointers or know of any good code examples to either programmatically change the type of an input column when it is passed through the component, or add a new column to the output? I am extracting data from an Oracle database which is in Julian date format (represented within SSIS as a DT_NUMERIC column) and I need to to either transform the input column holding it into a date column, or to dynamically add a new output column holding the transformed data.

Many thanks

You cannot change the type of a buffer column. You will have to add a new column. If you only expect users to select valid columns, and for each one selected you could handle this by overriding SetUSageType on the component class.

Whenever people select a column (UsageType read-only, deny read-write as not required), you could then add a new output column. This would work and would be quite clean.

I would also use a custom property on the output column to store the lineage ID of the "source" inpurt column.

The better way would be to handle adding the column outside of SetUsageType. So with the Advanced UI you would need to select the input column (SetUsageType), then add the output column (InsertOutputColumnAt) and add the custom property (SetOutputColumnProperty) all in three steps. Method names used by the UI are shown in braclets so you know what to do if it was your own UI. With a proper custom UI this of course would become one step for the end user, but more importantly could all be done through the managed wrapper interface, CManagedComponentWrapperClass.SetUsageType, InsertOutputColumnAt, SetOutputColumnProperty methods. This would be good for the user experience it will give around real-time validation and OK/Cancel behaviour of the UI.

custom sp_browsereplcmds

Hi NG
I want to customise sp_browsereplcomdands.
I use an vb-Datagrid for output. The Grid can't show varbinary.
Thats why I need an additional column: cast (xact_seqno as bigint)
--CODE:
declare @.query nvarchar(4000),
@.dbname nvarchar(128)
set @.query='select xact_seqno,
originator_id,publisher_database_id,article_id,typ e,convert(int,partial_command),command
from MSrepl_commands order by originator_id, publisher_database_id,
xact_seqno,article_id,command_id'
set @.dbname='distribution'
exec master..xp_printstatements @.query, @.dbname
--WORKS FINE
--BUT
declare @.query nvarchar(4000), @.dbname nvarchar(128)
set @.query=
'select xact_seqno,
originator_id,publisher_database_id,article_id,typ e,convert(int,partial_command),command
from
MSrepl_commands order by originator_id, publisher_database_id,
xact_seqno,article_id,command_id,
convert(bigint, xact_seqno)' --< --
set @.dbname='distribution' exec master..xp_printstatements @.query,
@.dbname
--DOWN'T Show the additional Column, an alias is not possible
It seems, the parameter of xp_printstatements are not flexible to
parse an additonal column.
Any ideas?
Thanks Thomas
push the commands off into a separate table like this
sp_browsereplcmds @.results_table='test'
The schema looks like this
CREATE TABLE [dbo].[test] (
[xact_seqno] [varbinary] (16) NULL ,
[originator_id] [int] NULL ,
[publisher_database_id] [int] NULL ,
[article_id] [int] NULL ,
[type] [int] NULL ,
[command] [nvarchar] (1024) NULL
) ON [PRIMARY]
GO
alter table test
add transaction_sequenceno bigint
go
update test set transaction_sequenceno =convert(bigint, xact_seqno)
GO
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Thomas Hase" <tohas@.freenet.de> wrote in message
news:4213a36c.181072656@.news.t-online.de...
> Hi NG
> I want to customise sp_browsereplcomdands.
> I use an vb-Datagrid for output. The Grid can't show varbinary.
> Thats why I need an additional column: cast (xact_seqno as bigint)
> --CODE:
> declare @.query nvarchar(4000),
> @.dbname nvarchar(128)
> set @.query='select xact_seqno,
>
originator_id,publisher_database_id,article_id,typ e,convert(int,partial_comm
and),command
> from MSrepl_commands order by originator_id, publisher_database_id,
> xact_seqno,article_id,command_id'
> set @.dbname='distribution'
> exec master..xp_printstatements @.query, @.dbname
> --WORKS FINE
> --BUT
> declare @.query nvarchar(4000), @.dbname nvarchar(128)
> set @.query=
> 'select xact_seqno,
>
originator_id,publisher_database_id,article_id,typ e,convert(int,partial_comm
and),command
> from
> MSrepl_commands order by originator_id, publisher_database_id,
> xact_seqno,article_id,command_id,
> convert(bigint, xact_seqno)' --< --
> set @.dbname='distribution' exec master..xp_printstatements @.query,
> @.dbname
>
> --DOWN'T Show the additional Column, an alias is not possible
>
> It seems, the parameter of xp_printstatements are not flexible to
> parse an additonal column.
>
> Any ideas?
> Thanks Thomas
|||thanks
I've seen this code inside sp_browsereplcmds.
But I hoped, there was another way.
Thomas
On Wed, 16 Feb 2005 15:38:13 -0500, "Hilary Cotter"
<hilary.cotter@.gmail.com> wrote:

>push the commands off into a separate table like this
>sp_browsereplcmds @.results_table='test'
>The schema looks like this
>CREATE TABLE [dbo].[test] (
> [xact_seqno] [varbinary] (16) NULL ,
> [originator_id] [int] NULL ,
> [publisher_database_id] [int] NULL ,
> [article_id] [int] NULL ,
> [type] [int] NULL ,
> [command] [nvarchar] (1024) NULL
>) ON [PRIMARY]
>GO
>alter table test
>add transaction_sequenceno bigint
>go
>update test set transaction_sequenceno =convert(bigint, xact_seqno)
>GO
sql

Custom Render

I wonder if someone may be able to help. I have had a request to output a report from Reporting Services 2005 as a Pipe delimited file. I know it is possible to create custom renderers but I'm not sure how this is done. I've read http://msdn.microsoft.com/msdnmag/issues/05/02/CustomRenderers/ but I don't have much experience of VB.Net.

Any help would be much appreciated

Thanks

Adam

Hello, you can use the existing CSV renderer to export a pipe delimited file. You can use the FieldDelimiter DeviceInfo parameter to change the delimiter character:

http://msdn2.microsoft.com/en-us/library/ms155365.aspx

-Chris

Custom Delivery Extension

Hi,
I'm writing a custom delivery extension that saves the rendered report
to the disk.
My problem is that the output file only contains spaces (the file size
is excatly what it should be but no text only black spaces).
Does anyone know why spaces appear instead of chars ?
this is the code for excel but the same happens with mhtml:
Dim deviceInfo, format As String
format = "EXCEL"
deviceInfo = String.Format("<DeviceInfo><OutputFormat>{0}</OutputFormat></DeviceInfo>",
format)
' Render report
m_files = notification.Report.Render(format, deviceInfo)
Dim fileName As String
fileName = "C:\" & m_files(0).FileName & ".xls"
Dim results(CInt(m_files(0).Data.Length)) As Byte
m_files(0).Data.Read(results, 0, CInt(m_files(0).Data.Length))
Dim _filestream As System.IO.FileStream = System.IO.File.OpenWrite(fileName)
_filestream.Write(results, 0, CInt(m_files(0).Data.Length))
_filestream.Flush()
_filestream.Close()You need to take the encoding of the stream into account. Use a stream
reader to read the contents passing in the encoding of the stream, and then
use a stream writer to write to the filestream, also passing in the
encoding.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"kman" <kamara1@.yahoo.com> wrote in message
news:1106326857.837797.301490@.z14g2000cwz.googlegroups.com...
> Hi,
> I'm writing a custom delivery extension that saves the rendered report
> to the disk.
> My problem is that the output file only contains spaces (the file size
> is excatly what it should be but no text only black spaces).
> Does anyone know why spaces appear instead of chars ?
> this is the code for excel but the same happens with mhtml:
> Dim deviceInfo, format As String
> format = "EXCEL"
> deviceInfo => String.Format("<DeviceInfo><OutputFormat>{0}</OutputFormat></DeviceInfo>",
> format)
> ' Render report
> m_files = notification.Report.Render(format, deviceInfo)
> Dim fileName As String
> fileName = "C:\" & m_files(0).FileName & ".xls"
> Dim results(CInt(m_files(0).Data.Length)) As Byte
> m_files(0).Data.Read(results, 0, CInt(m_files(0).Data.Length))
> Dim _filestream As System.IO.FileStream => System.IO.File.OpenWrite(fileName)
> _filestream.Write(results, 0, CInt(m_files(0).Data.Length))
> _filestream.Flush()
> _filestream.Close()
>|||Thanks,
I tried what you've suggested but i think i'm doing something wrong
because now i'm getting a zero byte file.
can you provide the code for that?
here's the code i added:
Dim encode As Encoding = m_files(0).Encoding
Dim _readStream As New StreamReader(m_files(0).Data, encode)
Dim _filestream As New System.IO.FileStream(fileName,
FileMode.OpenOrCreate)
Dim _writeStream As New StreamWriter(_filestream, encode)
_writeStream.Write(_readStream.ReadToEnd)
_writeStream.Close()
_readStream.Close()
_FileStream.Close()|||Daniel, somebody,
Please help ...|||You're correct - for excel there's no encoding. I tried the mhtml
format which has ASCIIEncoding.
I'm only sending the format in the device info:
String.Format("<DeviceInfo><OutputFormat>{0}</OutputFormat></DeviceInfo>",format)
I'm not sure what you mean to pass via url, i've tried to export the
report from the report manager and it works fine.
Can you post how to read it like you do ?|||Try not passing in this device info and instead pass the format in on the
render call only.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"kman" <kamara1@.yahoo.com> wrote in message
news:1106853884.827838.300370@.z14g2000cwz.googlegroups.com...
> You're correct - for excel there's no encoding. I tried the mhtml
> format which has ASCIIEncoding.
> I'm only sending the format in the device info:
> String.Format("<DeviceInfo><OutputFormat>{0}</OutputFormat></DeviceInfo>",format)
> I'm not sure what you mean to pass via url, i've tried to export the
> report from the report manager and it works fine.
> Can you post how to read it like you do ?
>|||i tried - notification.Report.Render(format, Nothing), but still
there's a black file.
i checked the findrendersave sample and it works fine there but i can't
use the render method directly from the web service because i need the
subscription info (parameters ...)
any ideas ?|||I'm sorry to say, I don't see what the problem is. I can't see anything
wrong with what you are doing. It should work.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"kman" <kamara1@.yahoo.com> wrote in message
news:1106909217.659089.277880@.z14g2000cwz.googlegroups.com...
>i tried - notification.Report.Render(format, Nothing), but still
> there's a black file.
> i checked the findrendersave sample and it works fine there but i can't
> use the render method directly from the web service because i need the
> subscription info (parameters ...)
> any ideas ?
>|||Hi,
Solved it. It wasn't the encoding after all but the position on the
data stream.
I added this line and it solved it:
_renderedOutputFile.Data.Seek(CType(0, Long),
System.IO.SeekOrigin.Begin)
Thanks for your help.
This is the complete code for saving the file (works for excel, mhtml
and pdf):
m_files = notification.Report.Render(format, Nothing)
Dim _fileStream As System.IO.FileStream = Nothing
Dim _renderedOutputFile As RenderedOutputFile
_renderedOutputFile = m_files(0)
Dim fileName As String = "C:\" & _renderedOutputFile.FileName.Trim &
"." & _renderedOutputFile.Extension.Trim
_fileStream = New System.IO.FileStream(fileName, FileMode.OpenOrCreate,
FileAccess.Write)
_renderedOutputFile.Data.Seek(CType(0, Long),
System.IO.SeekOrigin.Begin)
Dim arr(CInt(_renderedOutputFile.Data.Length)) As Byte
_renderedOutputFile.Data.Read(arr, 0,
CInt(_renderedOutputFile.Data.Length))
_fileStream.Write(arr, 0, CInt(_renderedOutputFile.Data.Length))
_fileStream.Close()

Cursors and Remote Stored Procedures

given it would appear i can use a cusor as an OUTPUT parameter to a remote
stored procedure, what is the best way to retrieve/return a batch of data
from a stored procedure.On Tue, 8 Nov 2005 07:26:06 -0800, "Martin Samm" <Martin
Samm@.discussions.microsoft.com> wrote:

>given it would appear i can use a cusor as an OUTPUT parameter to a remote
>stored procedure, what is the best way to retrieve/return a batch of data
>from a stored procedure.
Hi Martin,
http://www.sommarskog.se/share_data.html
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)