Type cast problem in SSIS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Type cast problem in SSIS

Author

Topic

on7june
Starting Member

41 Posts

Posted - 2008-05-30 : 08:33:45

Correct me if i am wrong anywhere.

This is my table structure.

CREATE TABLE [AC].[RUN_PARMS](
[FILE_NAME] [varchar](6) NOT NULL,
[RUN_IND] [bit] NOT NULL,
[GROUP] [varchar](50) NULL,
CONSTRAINT [pk_RUN_PARMS] PRIMARY KEY CLUSTERED
(
[FILE_NAME]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I am using an execute package task in SSIS based on the value of RUN_IND field in RUN_PARMS table. If all the value of RUN_IND field is 0 for all the files in a group, then the execute package task should be enabled else it should be disabled. So i have used the following query.

SELECT SUM(CONVERT(INT,RUN_IND)) AS RUN_IND FROM AC.RUN_PARMS
WHERE [GROUP] = 'FIN'

This will return the value which will be greater than zero. Since i have made the value as 1 for FIN group. Imagine the FIN group has 10 files in it. The output look like,

RUN_IND
10

and

SELECT SUM(CONVERT(INT,RUN_IND)) AS RUN_IND FROM AC.RUN_PARMS
WHERE [GROUP] = 'Ven'

This will return the value which will be equal to zero. Since i have made the value as 0 for Ven group. The output look like,

RUN_IND
0

I have created a variable in the package "user::vardisablepackage".
I have set Resultset as Single for SQL task component
In the package properties (The package which has to be disabled) i have selected property as "Disable" and the expression as @User::vardisablepackage = 0 ?False:True

The problem now is i am not sure what should be the data type for vardisablepackage.
The expression will assign False to Disable property when the RUN_IND = 0.
I am unable to complete this task since i have problem assigning data type for this variable.

Please suggest your views.

Sarvan

on7june
Starting Member

41 Posts

Posted - 2008-06-02 : 00:17:14

I shall re frame my query for better understanding. The records stored in RUN_PARMS looks like below, when we use select query, but when we open the table all 1's are shown as True and 0's as False. Since the RUN_IND is of Bit data type. Due to this we cannot use Aggregate function in our query.

FILENAME RUN_IND GROUP
FAMABT 0 HOLD
FAMADF 1 DOC
FAMALF 1 FIN
FAMAPF 1 FIN
FAMARD 0 DOC
FAMARF 1 DOC
FAMARH 0 DOC
FAMATT 1 FIN
FAMBAF 1 FIN
FAMBTT 0 HOLD
FAMC1T 1 MISC
FAMC2T 0 MISC
FAMCAT 0 HOLD
FAMCCF 1 FIN
FAMCCT 1 HOLD

Here the RUN_IND is 1 for all the file names in FIN group, but other groups has mixed of 0 and 1. So i need a query where the result should be either 0 or 1. The result should be 1 when the RUN_IND is 1 for all the file names in a given group and the result should be 0 when the RUN_IND is 0 at least for one file name in a given group. Is this result is possible to attain?

Note: If needed i can change the RUN_IND field to some other data type

Sarvan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 03:29:01

quote:
Originally posted by on7june

I shall re frame my query for better understanding. The records stored in RUN_PARMS looks like below, when we use select query, but when we open the table all 1's are shown as True and 0's as False. Since the RUN_IND is of Bit data type. Due to this we cannot use Aggregate function in our query.

FILENAME RUN_IND GROUP
FAMABT 0 HOLD
FAMADF 1 DOC
FAMALF 1 FIN
FAMAPF 1 FIN
FAMARD 0 DOC
FAMARF 1 DOC
FAMARH 0 DOC
FAMATT 1 FIN
FAMBAF 1 FIN
FAMBTT 0 HOLD
FAMC1T 1 MISC
FAMC2T 0 MISC
FAMCAT 0 HOLD
FAMCCF 1 FIN
FAMCCT 1 HOLD

Here the RUN_IND is 1 for all the file names in FIN group, but other groups has mixed of 0 and 1. So i need a query where the result should be either 0 or 1. The result should be 1 when the RUN_IND is 1 for all the file names in a given group and the result should be 0 when the RUN_IND is 0 at least for one file name in a given group. Is this result is possible to attain?

Note: If needed i can change the RUN_IND field to some other data type

Sarvan

Cant you try using a Derived Column task to create a new column with value as casting the bit value to int and use it in your calculations?

on7june
Starting Member

41 Posts

Posted - 2008-06-02 : 05:45:40

As i said that my query should result either one or zero, i have used the following query to attain my result.

select min(CONVERT(INT,RUN_IND)) as RUNMIN
FROM AC.RUN_PARMS WHERE [GROUP] = 'FIN'

The RUNMIN WILL RETURN ONE IF ALL THE VALUE OF A PARTICULAR GROUP IS ONE ELSE IT WILL RESULT AS ZERO

Sarvan

shijobaby
Starting Member

44 Posts

Posted - 2009-08-21 : 06:24:05

The ways to avoid this error is simple

just look into my post

http://sqlerrormessages.blogspot.com/2009/08/sql-server-error-message-msg-130-cannot.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources