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

Conversion errors on date field from Access to SQL server7

Hi
1st time trying to migrate Access 2000 tables to SQLServer7.
The tables transport but I'm getting errors on the data transfer.

The error is based on the date/time field in Access...ex: DOB (DateofBirth) field is formatted as shortdate.

When the error occurs in transport it reads:
Error at Destination for Row number 310...
Insert error, column 16('DOB', DBTYPE_DBTIMESTAMP), status 6. Data overflow. Invalid character value for cast specification.

**What I have found so far is that this error occurs on the rows in the DOB field where the year of birth is before 1900 (ie:1897)...or in some instances if the year is mistakenly in as...example: 9194 (as opposed to 1994) it will not except the transfer.

I have created a mock table with a date/time field of this format (with all the years being in 2002) and it transfers fine!

Any ideas on how I get the SQL Server to accept these records??use datetime rather than smalldatetime.

valid datetime range is 1-Jan-1753 to 31-Dec-9999 23:59:59.9999|||I did convert the SQL field to datetime...but it still gives conversion errors on date fields that are in the 1800's!!??!!

If I change those to 01/01/1900...they will transfer.|||I must be missing the point... if the date is '01-Jan-1897' it would go into a datetime field with out problems. can you provide an example of a trouble maker?|||Here goes...there were some records that had dates like this:
01/01/9194
01/01/1897
01/01/1583 etc...

You had mentioned that if I changed the SQL field to datetime from smalldatetime (which I had already done)...then it would tranfer data from 1-Jan-1753 to 31-Dec-9999

Well...after I changed the field to datetime the records, such as, 01/01/1897 wouldn't transfer...even though they were in the valid range for datetime.

[And if I changed all the records that were before the year 1900 to a date after 1900 it would transfer].

Hope this clears it up.|||thanks!

You may have other problems here, consider the following code:

declare @.dt datetime, @.vc varchar(100)
set @.dt = '01/01/9194'
set @.vc = cast(@.dt as varchar)
select @.dt, @.vc
set @.dt = '01/01/1897'
set @.vc = cast(@.dt as varchar)
select @.dt, @.vc
set @.dt = '01/01/1583'
set @.vc = cast(@.dt as varchar)
select @.dt, @.vc

As one would expect the last date is a problem. Could you import the date data into a varchar field and then selectivly convert the data?

Controlling errors in Stored Procedure

Hi everyone:

I need to use the "SET ROWCOUNT" statement to limit the amount of data returned to the application in a query, I know that if "SET ROWCOUNT = 0" is not specified at the end of this stored proc all the next queries will return only the amount of records specified in the initial "SET ROWCOUNT" call, so I would like to know if a I can have something like theTRY-CATCH-FINALLY statement (inSQL-92 forSQL Server 2000, not in SQL 2005) to make sure the "SET ROWCOUNT = 0" is sent at the end even if an error israised.

Can it be done?

Thanks for any help.

No, I'm afraid in SQL2000 we can not do the error handling like usingTRY-CATCH-FINALLY block. If you only want to limit the rows returned by SELECT statements, you can use TOP key word instead. For example:

select top 1 * from sysobjects

|||

Ok, thanks Lori_jay.

Control if a SQL database exists before its creation or deletion

Hi,
I'm using SQL Server 2005, and I would like to understand how to create and to drop a database without errors:
Infact, if I try to create a database that already exists, SQL Server throws the error "Impossible to create the database because it already exists", and if I try to drop a database that doesn't exist, SQL Server throws the error "Impossible to drop the database because it doesn't esist".
Before creating or dropping a database, I should control if it exists or not...
Is there a method to do that?
I found that such control for a table is the following one (in this case, I drop the table only if it exists):

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table1]

I tried to adapt the statement to the database case, modifying it as follows:
if exists (select * from dbo.sysobjects where id = object_id(N'[Database1]') and OBJECTPROPERTY(id, N'IsDatabase') = 1)
drop database [Database1]

but it didn't function (it was a blind attempt).
Can you suggest me a statement to do that?
Thank you very much

You can try:

if db_id('myDb') is not null print 'DB exists'

Alter NULL, NOT NULL check as appropriate for what you want to check

/Kenneth

|||Ok it functions correctly, thank you KeWin

Consuming Error Output from a Derived column component

Hi,

I have created a program that imports a csv into the sql server. but during that import I need to track all the errors that occured for some malformed rows. I think I need to use the error output collection of the dataflow components to track the errors. I figured out that every dataflow component has a error output collection along with the data output collection. I want to write those error outputs into a separete database. So, I have created a SQL server data destination component and created a path between derived columns error output and it input collection. But it is not working as expected. can any body help on this?

or can anyone give me any example how to use/handle error output collection in SSIS?

I will appreciate all kind of suggestions.

thanks

Why is it not working? Are you receiving any errors? What are those errors?|||

Give more details...

What is the error?

Constraints...

I am creating the following constraints on a table and keep getting some
errors... what is the problem'
ALTER TABLE [dbo].[CMStb] ADD
CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[Eligibilitytb] ADD
CONSTRAINT [FK_Eligibilitytb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
Error Message
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'FK_CMStb_RecipDemotb'. The conflict occurred in database 'EDITPS', table
'RecipDemotb', column 'OriginalRecipid'.
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'FK_Eligibilitytb_RecipDemotb'. The conflict occurred in database 'EDITPS',
table 'RecipDemotb', column 'OriginalRecipid'.Sounds like the tables already have data that violate the foreign key
constraint. Try this:
SELECT * FROM dbo.CMStb
WHERE OriginalRecipid NOT IN
(
SELECT OriginalRecipid FROM dbo.RecipDemotb GROUP BY OriginalRecipid
)
If you get rows back, this is why the constraint fails.
(Is the "tb" at the end of the object name meant to stand for "table"? If
so, and Celko spots it, heaven help you.)
--
http://www.aspfaq.com/
(Reverse address to reply.)
"stoko" <stoko@.discussions.microsoft.com> wrote in message
news:9C5527C1-A010-4BC6-B78C-0755FF93BCD5@.microsoft.com...
> I am creating the following constraints on a table and keep getting some
> errors... what is the problem'
> ALTER TABLE [dbo].[CMStb] ADD
> CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
> (
> [OriginalRecipid]
> ) REFERENCES [dbo].[RecipDemotb] (
> [OriginalRecipid]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
> ALTER TABLE [dbo].[Eligibilitytb] ADD
> CONSTRAINT [FK_Eligibilitytb_RecipDemotb] FOREIGN KEY
> (
> [OriginalRecipid]
> ) REFERENCES [dbo].[RecipDemotb] (
> [OriginalRecipid]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
>
> Error Message
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK_CMStb_RecipDemotb'. The conflict occurred in database 'EDITPS', table
> 'RecipDemotb', column 'OriginalRecipid'.
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK_Eligibilitytb_RecipDemotb'. The conflict occurred in database
'EDITPS',
> table 'RecipDemotb', column 'OriginalRecipid'.
>

Constant Errors on Simple Sums... Why?

There seems to be a concept I'm not grasping. I don't understand why
I can't get a simple sum...
I have a singe table report based on a single dataset and all I want
to do is summarize some financials at 4 group levels.
I get both of these errors for every Sum expression in a report:
"A value expression used for the report parameter
'=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
function. Aggregate functions cannot be used in report parameter
expressions."
"The field expression for the data set ?DatasetName' has a scope
parameter that is not valid for an aggregate function. The scope
parameter must be set to a string constant that is equal to either the
name of a containing group, the name of a containing data region, or
the name of a data set."
I have only 2 parameters in the report, fiscalyear(int) and
fiscalperiod(int).
Half of my Sums aggregate YTD figures that do not reference either of
the parameters.
The other half aggregate MTD figures that are dependent on the
fiscalperiod parameter.
I've tried putting the expression directly into the appropriate text
field, and I have tried making the Sum expressions their own fields
and dropping those fields into the table. Nothing works.
Can anyone explain?
Thanks,
JodyHave you added groups to the table on the form? You need groups. Go to the
footer for the group. Use the expression builder to put the appropriate
values. If you are approaching it correctly it should be very straight
forward.
HTH,
Bruce L-C
"JodyT" <datagal@.msn.com> wrote in message
news:f9d864c3.0408171120.48b2b30b@.posting.google.com...
> There seems to be a concept I'm not grasping. I don't understand why
> I can't get a simple sum...
> I have a singe table report based on a single dataset and all I want
> to do is summarize some financials at 4 group levels.
> I get both of these errors for every Sum expression in a report:
> "A value expression used for the report parameter
> '=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
> function. Aggregate functions cannot be used in report parameter
> expressions."
> "The field expression for the data set 'DatasetName' has a scope
> parameter that is not valid for an aggregate function. The scope
> parameter must be set to a string constant that is equal to either the
> name of a containing group, the name of a containing data region, or
> the name of a data set."
> I have only 2 parameters in the report, fiscalyear(int) and
> fiscalperiod(int).
> Half of my Sums aggregate YTD figures that do not reference either of
> the parameters.
> The other half aggregate MTD figures that are dependent on the
> fiscalperiod parameter.
> I've tried putting the expression directly into the appropriate text
> field, and I have tried making the Sum expressions their own fields
> and dropping those fields into the table. Nothing works.
> Can anyone explain?
> Thanks,
> Jody|||All of the groups are there..
After some experimentation, I found that you don't actually have to
specify the scope in an aggregate in a table, and that helped.
Another part of the problem is that I'm getting inconsistent results
from my Preview pane and from the Debug preview window. The Debug is
generally right and the Preview if often wrong, even after I do a
rebuild.
Things are going better, but still far from what I had hoped for.
Thanks,
Jody
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<#nIqvFJhEHA.644@.tk2msftngp13.phx.gbl>...
> Have you added groups to the table on the form? You need groups. Go to the
> footer for the group. Use the expression builder to put the appropriate
> values. If you are approaching it correctly it should be very straight
> forward.
> HTH,
> Bruce L-C
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408171120.48b2b30b@.posting.google.com...
> > There seems to be a concept I'm not grasping. I don't understand why
> > I can't get a simple sum...
> >
> > I have a singe table report based on a single dataset and all I want
> > to do is summarize some financials at 4 group levels.
> > I get both of these errors for every Sum expression in a report:
> >
> > "A value expression used for the report parameter
> > '=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
> > function. Aggregate functions cannot be used in report parameter
> > expressions."
> >
> > "The field expression for the data set 'DatasetName' has a scope
> > parameter that is not valid for an aggregate function. The scope
> > parameter must be set to a string constant that is equal to either the
> > name of a containing group, the name of a containing data region, or
> > the name of a data set."
> >
> > I have only 2 parameters in the report, fiscalyear(int) and
> > fiscalperiod(int).
> > Half of my Sums aggregate YTD figures that do not reference either of
> > the parameters.
> > The other half aggregate MTD figures that are dependent on the
> > fiscalperiod parameter.
> >
> > I've tried putting the expression directly into the appropriate text
> > field, and I have tried making the Sum expressions their own fields
> > and dropping those fields into the table. Nothing works.
> >
> > Can anyone explain?
> >
> > Thanks,
> > Jody|||The groups are all there.
After some monkeying around I discovered that you shouldn't specify
the scope for an aggregate in a table. Tha helped.
Another problem is that I get different results on the VS Preview Pane
and the Debug preview window. The window is right, but the pane if
often wrong, even with a rebuild
Thanks,
Jody
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<#nIqvFJhEHA.644@.tk2msftngp13.phx.gbl>...
> Have you added groups to the table on the form? You need groups. Go to the
> footer for the group. Use the expression builder to put the appropriate
> values. If you are approaching it correctly it should be very straight
> forward.
> HTH,
> Bruce L-C
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408171120.48b2b30b@.posting.google.com...
> > There seems to be a concept I'm not grasping. I don't understand why
> > I can't get a simple sum...
> >
> > I have a singe table report based on a single dataset and all I want
> > to do is summarize some financials at 4 group levels.
> > I get both of these errors for every Sum expression in a report:
> >
> > "A value expression used for the report parameter
> > '=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
> > function. Aggregate functions cannot be used in report parameter
> > expressions."
> >
> > "The field expression for the data set 'DatasetName' has a scope
> > parameter that is not valid for an aggregate function. The scope
> > parameter must be set to a string constant that is equal to either the
> > name of a containing group, the name of a containing data region, or
> > the name of a data set."
> >
> > I have only 2 parameters in the report, fiscalyear(int) and
> > fiscalperiod(int).
> > Half of my Sums aggregate YTD figures that do not reference either of
> > the parameters.
> > The other half aggregate MTD figures that are dependent on the
> > fiscalperiod parameter.
> >
> > I've tried putting the expression directly into the appropriate text
> > field, and I have tried making the Sum expressions their own fields
> > and dropping those fields into the table. Nothing works.
> >
> > Can anyone explain?
> >
> > Thanks,
> > Jody|||The groups are all there.
After some monkeying around I discovered that you shouldn't specify
the scope for an aggregate in a table. That helped.
Another problem is that I get different results on the VS Preview Pane
and the Debug preview window. The window is right, but the pane if
often wrong, even with a rebuild
Thanks,
Jody
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<#nIqvFJhEHA.644@.tk2msftngp13.phx.gbl>...
> Have you added groups to the table on the form? You need groups. Go to the
> footer for the group. Use the expression builder to put the appropriate
> values. If you are approaching it correctly it should be very straight
> forward.
> HTH,
> Bruce L-C
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408171120.48b2b30b@.posting.google.com...
> > There seems to be a concept I'm not grasping. I don't understand why
> > I can't get a simple sum...
> >
> > I have a singe table report based on a single dataset and all I want
> > to do is summarize some financials at 4 group levels.
> > I get both of these errors for every Sum expression in a report:
> >
> > "A value expression used for the report parameter
> > '=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
> > function. Aggregate functions cannot be used in report parameter
> > expressions."
> >
> > "The field expression for the data set 'DatasetName' has a scope
> > parameter that is not valid for an aggregate function. The scope
> > parameter must be set to a string constant that is equal to either the
> > name of a containing group, the name of a containing data region, or
> > the name of a data set."
> >
> > I have only 2 parameters in the report, fiscalyear(int) and
> > fiscalperiod(int).
> > Half of my Sums aggregate YTD figures that do not reference either of
> > the parameters.
> > The other half aggregate MTD figures that are dependent on the
> > fiscalperiod parameter.
> >
> > I've tried putting the expression directly into the appropriate text
> > field, and I have tried making the Sum expressions their own fields
> > and dropping those fields into the table. Nothing works.
> >
> > Can anyone explain?
> >
> > Thanks,
> > Jody