Conversion issues on Output Columns with Script Task

I am not sure which type to use for my Script Transformation Editor output fields. I'm getting errors based on the Data Type I'm specifying for my fields.

Print Screens:

http://www.webfound.net/script_task.jpg

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Import Maintenance (mnt) File [Split HeaderRows into Columns [5176]]: Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'UInteger'.
Line 21 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 22 Column 35 through 69
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 23 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 25 Column 27 through 61

Error at Import Maintenance (mnt) File [Split HeaderRows into Columns [5176]]: Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'UInteger'.
Line 21 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 22 Column 35 through 69
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 23 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 25 Column 27 through 61

Error at Import Maintenance (mnt) File [DTS.Pipeline]: "component "Split HeaderRows into Columns" (5176)" failed validation and returned validation status "VS_ISBROKEN".

Error at Import Maintenance (mnt) File [DTS.Pipeline]: One or more component failed validation.

Error at Import Maintenance (mnt) File: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

I'm not sure if this is needed but here's the script I coded in my script task also:

Imports System

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 strWholeRow As String = Row.OutputHeaderRows

Row.BatchDate = CStr(strWholeRow.Substring(0, 8))

Row.NotUsed = CStr(strWholeRow.Substring(9, 32))

Row.TransactionCode = CStr(strWholeRow.Substring(33, 34))

Row.GrossBatchTotalAmount = CDbl(strWholeRow.Substring(35, 44))

Row.NetBatchTotalAmount = CDbl(strWholeRow.Substring(45, 54))

Row.BatchTransactionCount = CDbl(strWholeRow.Substring(55, 59))

Row.PNETID = CStr(strWholeRow.Substring(60, 63))

Row.PartnerCode = CDbl(strWholeRow.Substring(64, 67))

Row.Filler = strWholeRow.Substring(68, 100)

End Sub

End Class

Looking at the screenshot and the code it looks like you're trying to put a decimal number into an integer column and you simply can't do that. You'll have to change either the type of the output column (try using DT_DECIMAL) or change CDbl to CInt.

-Jamie

Control Flow Task Error shouldn't fail package

Hi all,

I have a Send Mail Task in my control flow to notify users that the processing is done. I want to avoid the package to fall in error if the Send Mail task failed.

What is the best practice to do that ?

Should I raise the MaximumErrorCount of theSend Mail Task ? Should I play with ErrorHandler ?

Try setting the Send Mail Task's "ForceExecutionResult" to "Success"

Control flow drawing bug

Minor issue here. But I just wanted to point it out.

I think this is how to reproduce it. If I disable a dataflow task and run the package, after it is complete, but yet still in the debugger, reenable the dataflow task. When I close the debugger, the dataflow task is still shaded as if it were disabled, but it will execute on the next run.

Close the package and re-open it and the work flow task is drawn correctly.

Thanks for the report, Jim. What build is this with? We've fixed one of these occurrences recently.

regards,
ash|||I am still on the April CTP.|||OK. I believe this has been fixed in the next CTP (CTP16). Please review when you get that, thanks!

Continue package execution on task failure

Hi,

I am developing an SSIS package and need the execution of the package to continue even if one of the tasks within the package fails. I have an OnError event handler for this task which fires when it fails but want the rest of the package to continue.

Any suggestions greatly appreciated.

Thanks

Hi PK2000

Can you tell me on what kind of package it fails on and what the next package is that you want to execute?

Kind Regards,

Joos Nieuwoudt

|||

Hi Joos,

I probably didn't explain the problem clearly......

I have one SSIS package with multiple tasks. I want the the package to continue even if the first task within the package fails.

Thanks

|||Hi

by default the arrows between your tasks on the control flow (precedence constraints) are green. It means that you require the task to be successful in order for the next task to be launched.

You can change this behaviour by double-clicking on the green arrow to change the constraint value from 'Success' (green) to 'Completion' (blue) - the next task will then be executed on both failure or success.

cheers

Thibaut|||

Hi PK2000,

Sorry, I understood your question wrong but Thibaut's answer is correct. Try it.

Kind Regards,

Joos Nieuwoudt.

Continuation of emailing an excel file

Hi Rafael,

I need to create a new excel file daily and then need to mail it.

The problem iam facing is that ,when i schedule a job the task that dumps data into excel(Dataflow task ) gives a validation error.Everytime i need to manually link the sheets of the excel to the corresponding tables.

Any idea why this is happening?So iam unable to schedule as a job...

Thanks,

Vani.

This is more of an integration services forum question than data access forum question. Moving this thread to SQL IS forums.|||

You might try setting the DelayValidation property true on the data flow task and the connection manager. You might take a look at this post for more information on dynamically creating Excel files in SSIS.

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

Consuming a Recordset in a Script Transformation

I have a situation where I have created a recordset in a previous data flow task where the output was a recordset.

Now I want to write that recordset to a flat file.

There is no direct recordset input data flow source. So I thought I would use a source script transformation to read the recordset. Then I searched on this and found no information on how I might do this.

Anyone have any ideas on how to do this in a script transformation?

There are reasons that I would like to do it this way, but if I have to choose another way, well that's life.

Try this -

SSIS Junkie : SSIS: Recordsets instead of raw files
(http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx)

By the way I would consider raw files over this, especially if you have a lot of data.

|||

DarrenSQLIS wrote:

Try this -

SSIS Junkie : SSIS: Recordsets instead of raw files
(http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx)

By the way I would consider raw files over this, especially if you have a lot of data.

I echo what Darren has said. And if you need proof, go here:

Comparing performance of a raw file against a recordset destination
http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4159.aspx

-Jamie