I am transferring 1000 rows from a SQL Data Source to SQL Destination using OLEDB Command control. I am using OLEDB Command because i need to execute the stored procedure for each row.
I have found that 974 rows were transferred. I figured out which rows were skipped and then executed stored procedure manually to see if any errors but manually stored procedure ran successfully and it inserted those records.
I have also created a flow task to include only those records which missed during the first load
SELECT TOP ( 1000 ) AddressStringId , HASHBYTES('SHA1', CONVERT(VARCHAR(12), AddressStringId)) AS Guid , HASHBYTES('SHA1', SearchString) AS UniqueHashString , OrganisationName AS LocationName , OrganisationName AS AddressLine1 , SubBuildingName AS AddressLine2 , '' AS AddressLine3 , BuildingNumber , BuildingName , StreetFirstPart + ' ' + StreetSecondPart AS Street , '' AS Locality , 'England' AS AdministrativeArea , '' AS SubAdministrativeArea , PostTown AS Municipality , PostCode , 'GB' AS Iso3166MinusOneCountryAlpha2Code , STR(Longitude, 25, 5) AS Longitude , STR(Latitude, 25, 5) Latitude , SearchString , 3.0 AS PointBufferDistanceInMeters , --below are the output parameters in the stored procedure however SSIS doesn't seem to handle this. so passing 0 or null values for these parameters ISNULL(ERROR_NUMBER(), 0) AS ErrorNumber , ISNULL(ERROR_SEVERITY(), 0) AS ErrorSeverity , ISNULL(ERROR_STATE(), 0) AS ErrorState , ISNULL(ERROR_PROCEDURE(), 0) AS ErrorProcedure , ISNULL(ERROR_LINE(), 0) AS ErrorLine , ISNULL(ERROR_MESSAGE(), '') AS ErrorMessage , 0 AS InsertedRecordsFROM AddressStringSearchWHERE AddressStringId IN (16855935,16855951,16856022,16856056,16856155,16856162,16856207,16856243,16856257,16856301,16856362,16856406,16856430,16856444,16856453,16856453,16856491,16856585,16856602,16856623,16856650,16856680,16856687,16856701,16856703,16856758,16856809)
I manually passed the values for two Ids 16855935 and 16855951. The stored procedure ran fine.
Now i am totally confused as why is it happening can you please help?
SSIS
Last Comment
Ali Shah
8/22/2022 - Mon
Pawan Kumar
In clause doesn't work if you have NULL in the table.
I think below is the issue.
WHERE AddressStringId IN (16855935,16855951,16856022,16856056,16856155,16856162,16856207,16856243,16856257,16856301,16856362,16856406,16856430,16856444,16856453,16856453,16856491,16856585
,16856602,16856623,16856650,16856680,16856687,16856701,16856703,16856758,16856809)
Ali Shah
ASKER
I don't have null values in my table. all of these id's do exist. Also these ids were skipped from the initial load and i can add them by running stored procedure manually as explained in my original question
SELECT TOP ( 1000 ) AddressStringId , HASHBYTES('SHA1', CONVERT(VARCHAR(12), AddressStringId)) AS Guid , HASHBYTES('SHA1', SearchString) AS UniqueHashString , OrganisationName AS LocationName , OrganisationName AS AddressLine1 , SubBuildingName AS AddressLine2 , '' AS AddressLine3 , BuildingNumber , BuildingName , StreetFirstPart + ' ' + StreetSecondPart AS Street , '' AS Locality , 'England' AS AdministrativeArea , '' AS SubAdministrativeArea , PostTown AS Municipality , PostCode , 'GB' AS Iso3166MinusOneCountryAlpha2Code , STR(Longitude, 25, 5) AS Longitude , STR(Latitude, 25, 5) Latitude , SearchString , 3.0 AS PointBufferDistanceInMeters , --below are the output parameters in the stored procedure however SSIS doesn't seem to handle this. so passing 0 or null values for these parameters ISNULL(ERROR_NUMBER(), 0) AS ErrorNumber , ISNULL(ERROR_SEVERITY(), 0) AS ErrorSeverity , ISNULL(ERROR_STATE(), 0) AS ErrorState , ISNULL(ERROR_PROCEDURE(), 0) AS ErrorProcedure , ISNULL(ERROR_LINE(), 0) AS ErrorLine , ISNULL(ERROR_MESSAGE(), '') AS ErrorMessage , 0 AS InsertedRecordsFROM AddressStringSearch
Have you defined batch size or something like that ... Query seems to be fine.
Ali Shah
ASKER
no i have not defined anything perhaps using default settings for Data Source, and OLEDB Command component. The only thing i am using is Data Conversion component to convert some fields to unicode string.
The AMAZING thing is that i could not find any difference between inserted and skipped records. Also the strange is that i can manually execute the stored procedure which successfully operates. There must be something with OLEDB command control
I think below is the issue.
WHERE AddressStringId IN (16855935,16855951,1685602
,16856602,16856623,1685665