case expression plus outer join in ole db source

I'm trying to generate the data for a 2-column table, where both columns are defined as NOT NULL and the second column is a uniqueidentifier.

In SQL Server Management Studio, this works fine:

insert into table_3(column_a, column_b)

select table_1.column_a, (case when table_2.column_b is NULL then newid() else table_2.column_b end) as column_b

from table_1 left outer join table_2 on table_1.column_c = table_2.column_c

That is, column_b of the SELECT result has no NULL values, and all 35,986 rows are successfully inserted into a previously empty table_3. (If I comment out the INSERT INTO clause and project table_2.column_b instead of "(case ... end) as column_b", the SELECT result includes 380 rows with a NULL in column_b, so I know the case expression plus the outer join are working as expected.)

But when I use the SELECT query as the SQL command in an OLE DB Source component that is connected directly to the OLE DB Destination for the result table, I get this error:

There was an error with input column "column_b" (445) on input "OLE DB Destination Input" (420

The column status returned was: "The value violated the integrity constraints for the column.".

And sure enough, when I modify the result table to allow NULL in column_b, truncate it, and re-run the data flow, it inserts the exact same 380 rows with a NULL in column_b among the 35,986 rows.

So what is SSIS doing to screw up the results of the SELECT command?

Kevin,

Can you see the values of column_b when you use the 'preview' of the OLE DB source?

Did you check twice the mapping tab in your OLE DB destination to make sure nothing is missing?

Have you used a data view right before the OLE DB Destination to check the values of column_b are shown correctly?

If the answer to those 3 questions is yes, I am affraid I could not help you.

Rafael Salas

|||Could there be an issue in executing the newid() function?|||

Argh, the problem was due to operator error: somehow the source component SQL statement had gotten out of sync with the corresponding variable. How embarrassing!

But many thanks to Rafael and Phil, who got me to examine the data flow task closely enough to discover my error.

cars attributes should be >= specs attributes (was "Help with Query")

Hello all-

I have a specification table that has some attributes defined.
SpecId - Id of the specification
Attribute - Attribute of the spec. (Like Color, HP etc)
Value - Is the value of the attribute
Then I have a car table that actually has information about the cars. Intention is to take each specification and match the cars that match the specification. If the car has more attributes than the spec, we ignore the extra attributes for the match. But if the car has less attributes, we don't even consider the car as a match (even if the attributes present, match). To summarize, the car's attributes should be >= spec's attributes.

The code I have below is bad because I am joining the same tables twice. In addition, it fails in the condition "the car's attributes should be >= spec's attributes"

Any help is greatly appreciated.

DECLARE @.Specification TABLE
(SpecId VARCHAR(10),
Attribute VARCHAR(100),
Value VARCHAR(100))

DECLARE @.Car TABLE
(CarName VARCHAR(10),
Attribute VARCHAR(100),
Value VARCHAR(100))

INSERT INTO @.Specification VALUES ('S1', 'Type', 'Sedan')
INSERT INTO @.Specification VALUES ('S1', 'Transmission', 'Auto')
INSERT INTO @.Specification VALUES ('S1', 'HP', '220')

INSERT INTO @.Specification VALUES ('S2', 'Type', 'SUV')
INSERT INTO @.Specification VALUES ('S2', 'Transmission', 'Manual')
INSERT INTO @.Specification VALUES ('S2', 'HP', '300')

INSERT INTO @.Car VALUES ('Accord', 'Type', 'Sedan')
INSERT INTO @.Car VALUES ('Accord', 'Transmission', 'Auto')
INSERT INTO @.Car VALUES ('Accord', 'HP', '220')
INSERT INTO @.Car VALUES ('Accord', 'Color', 'Black')

INSERT INTO @.Car VALUES ('Escape', 'Type', 'SUV')
INSERT INTO @.Car VALUES ('Escape', 'Transmission', 'Manual')
INSERT INTO @.Car VALUES ('Escape', 'HP', '300')

INSERT INTO @.Car VALUES ('Explorer', 'Type', 'SUV')
INSERT INTO @.Car VALUES ('Explorer', 'Transmission', 'Manual')

SELECT DISTINCT Spec.SpecId, Car.CarName
FROM @.Specification Spec
INNER JOIN @.Car Car
ON Spec.Attribute = Car.Attribute
AND Spec.Value = Car.Value
WHERE Spec.SpecId NOT IN (SELECT Spec.SpecId
FROM @.Specification Spec
LEFT OUTER JOIN @.Car Car
ON Spec.Attribute = Car.Attribute
AND Spec.Value = Car.Value
WHERE Car.CarName IS NULL)First, there is nothing wrong with joining a table more than once in a query. It is pretty common, actually.

Second, rephrase your requirement like this: "The car's matching attributes should be = spec's total attributes", and you'll get the same results but the query is easier to derive:

select CarSpecMatches.CarName, CarSpecMatches.SpecId
from
(select Car.CarName, Specification.SpecId, count(*) Attributes
from @.Car Car
inner join @.Specification Specification
on Car.Attribute = Specification.Attribute and Car.Value = Specification.Value
group by Car.CarName, Specification.SpecId) CarSpecMatches
inner join
(select SpecID, count(*) Attributes
from @.Specification Specification
group by SpecID) SpecAttributes
on CarSpecMatches.SpecID = SpecAttributes.SpecID
and CarSpecMatches.Attributes = SpecAttributes.Attributes|||Blindman-

Thanks for your help with the query. Your approach should be a lot better than mine. The main reason why I was concerned about joining the tables twice was that, as such these tables are very large and then my query was doing a left join versus inner.

I will plug this into my code and see the results.

I greatly appreciate your help with the query.

Thanks

Capture Grouping Values

I have two groupings defined in my table. I group by Owner and then by Priority. I'm struggling with the expression to capture the string values at the appropriate scope. Using the table below, I want to capture "Critical, High" for "Jack Daniels" and "Medium, Low, Informational" for "Jim Beam". The table has "grpOwner" and "grpPriority" defined on Fields!Owner.Value and Fields!Priority.Value respectively. Any help would be greatly appreciated!

Owner

Priority

Jack Daniels

Critical

High

Jim Beam

Medium

Low

Informational

You should be able to concatenate the values in code as explained here.|||I opened a ticket with MSFT and they recommended creating a custom assembly with a function that retrieves the values based on the grouping level.

Can''t update smalldatetime field

I have a table with the field end_date which is defined as a smalldatetime.

I have been unable to update this field using the SqlCommand object.

In my windows form I have a DateTime Picker and I am trying to get it's value into the db with no success.

I have tried

command.Parameters.Add ("@.end_date",SqlDbType.DateTime);

command.Parameters["@.end_date"].Value = dtpEndDate.Value;

I have also tried variations of

command.Parameters.AddWithValue ("@.end_date",dtpEndDate.Value);

Please assist if you can.

although I don't believe this is the best way, but it's working.

command.Parameters.AddWithValue("@.end_date", dtpEndDate.Value.ToShortDateString());