none
OLEDB Source to return nil data

    Question

  • Hi,

    I have a data flow task that contains OLEDB Source and OLEDB Destination.

    The OLEDB Source has this SQL command:

    DECLARE @cnt AS TINYINT
    SELECT @cnt = COUNT(*) FROM person.[Age]
    
    IF @cnt = 0 
    SELECT 'Unknown' as Value

    The OLEDB Destination has person.Age table tied to it.

    Here's the table schema for person.Age:

    CREATE TABLE [person].[Age](
    	[ValueID] [tinyint] IDENTITY(0,1) NOT NULL,
    	[Value] [varchar](10) NOT NULL,
    ) ON [PRIMARY]

    I want the "Unknown" value to be inserted into person.Age if the table doesn't have any data but do nothing if person.Age already have a data. it works well for the 1st scenario but stuck running if person.Age already have a data.

    Is there an efficient way to do this?


    cherriesh

    Monday, March 03, 2014 1:31 AM

Answers

All replies

  • Are you getting any error?
     How many records are there in the table?
    Can you try changing the datatype and see if that works for you?

    DECLARE @cnt AS BIGINT SELECT @cnt = COUNT(ValueID) FROM person.[Age] IF @cnt = 0 SELECT 'Unknown' as Value

    Else....

    Monday, March 03, 2014 1:39 AM
  • Try this.

    SELECT [Value] FROM (
    	SELECT CASE WHEN COUNT(*) = 0 THEN 'Unknown' ELSE NULL END [Value] FROM [person].[Age]
    ) A
    WHERE [Value] IS NOT NULL
    

    I get an error using your SQL Command in OLEDB Source. Basically it's expecting a resultset and a resultset is not return if there are rows in [person].[Age]. The query above will return a resultset with no record.

    Hope this helps.
    ~ J.

    Wednesday, March 05, 2014 5:29 AM
  • just this is enough i guess

    INSERT Person.Age(Value)
    SELECT 'Unknown'
    WHERE NOT EXISTS (SELECT 1
    FROM Person.Age)


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, March 05, 2014 7:01 AM
  • Hi Cherriesh,

    Besides Jonathan’s answer, Visakh’s query should also work if we delete the INSERT satement:

    SELECT 'Unknown' as Val
     WHERE NOT EXISTS (SELECT 1 FROM Person.Age)

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support


    Thursday, March 13, 2014 7:18 AM
    Moderator