Whole numbers from sql column using SSIS

Question

Below is the sample SQL.

From Source1 tb, I need only whole numbers of Value column into ValueNEW of Destination1 tb as shown in the output pic.

--Source1
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Source1]') AND type in (N'U'))
DROP TABLE [dbo].[Source1]
GO
CREATE TABLE [dbo].[Source1](
    [ID] [int] NOT NULL,
    [Value] [varchar](255) NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[Source1] VALUES(1, '45.212')
INSERT INTO [dbo].[Source1] VALUES(2, '12/45')
INSERT INTO [dbo].[Source1] VALUES(3, '6')
INSERT INTO [dbo].[Source1] VALUES(4, '100.56')
INSERT INTO [dbo].[Source1] VALUES(5, 'ADFJJ 12/34 F=10.4')
INSERT INTO [dbo].[Source1] VALUES(6, '+. 0.5')
INSERT INTO [dbo].[Source1] VALUES(7, '300')
INSERT INTO [dbo].[Source1] VALUES(8, '90')
INSERT INTO [dbo].[Source1] VALUES(9, '80.5')
INSERT INTO [dbo].[Source1] VALUES(10, '13): @FD E 19.50 32')

--Destination
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Destination1]') AND type in (N'U'))
DROP TABLE [dbo].[Destination1]
GO
CREATE TABLE [dbo].[Destination1](
    [ID] [int] NOT NULL,
    [Value] [varchar](255) NULL,
    [ValueNEW] [varchar](255) NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[Destination1] VALUES(1, '45.212', NULL)
INSERT INTO [dbo].[Destination1] VALUES(2, '12/45', NULL)
INSERT INTO [dbo].[Destination1] VALUES(3, '6', '6')
INSERT INTO [dbo].[Destination1] VALUES(4, '100.56', NULL)
INSERT INTO [dbo].[Destination1] VALUES(5, 'ADFJJ 12/34 F=10.4', NULL)
INSERT INTO [dbo].[Destination1] VALUES(6, '+. 0.5', NULL)
INSERT INTO [dbo].[Destination1] VALUES(7, '300', 300)
INSERT INTO [dbo].[Destination1] VALUES(8, '90', 90)
INSERT INTO [dbo].[Destination1] VALUES(9, '80.5', NULL)
INSERT INTO [dbo].[Destination1] VALUES(10, '13): @FD E 19.50 32', NULL)


 SELECT * FROM [dbo].[Source1]
 SELECT * FROM [dbo].[Destination1]

ValueNEW needed as output.

Answer 1

I think you're asking about using IsNumeric and CharIndex in your source query and then throwing away the things that are not whole numbers. I would change my source query to be

SELECT 
    S.* 
,   CASE
        WHEN
        -- This filters for only the numeric type of data
        ISNUMERIC(S.Value) > 0
        -- this filters the non whole numbers
        AND CHARINDEX('.', S.Value) = 0
            THEN S.Value
        ELSE NULL
    END AS NewValue
FROM 
    dbo.Source1 AS S;

Otherwise, you're looking at either using a series of Derived Column Components or a Script Component to do the same actions.

References

• CHARINDEX
• ISNUMERIC