March 24, 2010 at 1:38 pm
I have an OLE DB Command transformation with the following SqlCommand:
DELETE HF
FROM HouseFacility HF
JOIN Facility F
ON F.Id = HF.FacilityId
JOIN HouseFacilityGroup G
ON HF.HouseFacilityGroupId = G.Id
WHERE G.HouseId = ?
AND F.FacilityGroupId = ?
In T-SQL, the command executes as expected: the rows from HouseFacility are deleted for the correct rows. But when I click on Column Mappings in the OLE DB Command transformation, I get this error: ".....Invalid object name 'HF' "
Can anyone shed some light on what is going on here?
March 24, 2010 at 2:58 pm
What component are you trying to do this in?
Also a delete statement isn't going to have any columns to map to.
Perhaps you should tell us what you are trying to accomplish and we will guide you.
CEWII
March 24, 2010 at 3:06 pm
This is an OLE DB Command transformation within a Data Flow task.
March 24, 2010 at 3:50 pm
Did you try using the actual Table Name instead of alias... That might just do it.. If it does not work try using [TablName].[ColumnName] instead.. This might sound silly but i remember having a similar issue and changing it as mentioned worked for me
DELETE houseFacility
FROM HouseFacility
JOIN Facility
ON Facility.Id = HouseFacility.FacilityId
JOIN HouseFacilityGroup
ON HouseFacilityGroup.HouseFacilityGroupId = HouseFacilityGroup.Id
WHERE HouseFacilityGroup.HouseId = ?
AND Facility.FacilityGroupId = ?
March 24, 2010 at 4:12 pm
Hi, thanks for the suggestion. I tried it, but it also given an error, a different one. This one is:
The multi-part identifier "HouseFacilityGroup.HouseId" could not be bound.
Again, the revised DELETE statement works fine in T-SQL. Also, when I replace the ? parameters with just numbers in the OLE DB Command SqlCommand, it accepts it just fine - it's the parameters that are making it generate the error.
March 24, 2010 at 4:26 pm
If the Column Names being used in the Where clause are not in any other tables, then you might want to remove the tableName and just use column Names instead..like ColumnName = ?
March 25, 2010 at 2:37 am
I cannot try that because each table has an ID field, and I need to refer to the ID field in the DELETE query.
This is very frustrating - it is a simple DELETE statement, and I am having to consider writing a stored procedure to handle this deficiency in SSIS. If I create one stored procedure, then why don't I simply implement the entire package as stored procedures?
March 25, 2010 at 3:55 am
There is no need for joins with deletes.
Try the standard ANSI syntax:
DELETE HouseFacility
WHERE EXISTS
(
SELECT *
FROM Facility F
WHERE F.[Id] = HouseFacility.FacilityId
AND F.FacilityGroupId = ?
)
AND EXISTS
(
SELECT *
FROM HouseFacilityGroup G
WHERE G.[Id] = HouseFacility.HouseFacilityGroupId
AND G.HouseId = ?
)
March 25, 2010 at 4:47 am
Thanks for the suggestion. I tried it, but SSIS gives me the error:
Parameter Information cannot be derived from SQL statements with sub-select queries. Set parameter information before preparing command.
March 25, 2010 at 8:20 am
xnl28-574517 (3/24/2010)
Hi, thanks for the suggestion. I tried it, but it also given an error, a different one. This one is:The multi-part identifier "HouseFacilityGroup.HouseId" could not be bound.
Again, the revised DELETE statement works fine in T-SQL. Also, when I replace the ? parameters with just numbers in the OLE DB Command SqlCommand, it accepts it just fine - it's the parameters that are making it generate the error.
I'm wondering what you changed to get that error.. It should have looked more like:
DELETE dbo.HouseFacility
FROM dbo.HouseFacility HF
INNER JOIN Facility F
ON F.Id = HF.FacilityId
INNER JOIN dbo.HouseFacilityGroup G
ON HF.HouseFacilityGroupId = G.Id
WHERE G.HouseId = ?
AND F.FacilityGroupId = ?
CEWII
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply