January 23, 2015 at 10:18 am
Hi All,
I am running into an issue where I have created a data flow, source to target, with mainly just look-up transformations. When I execute the dataflow, it completes successfully and I am expecting roughly 200,000 records in the target. When I query the target, I am getting roughly 100,000. I updated the error_output in the target table to redirect row and I have this going to another table. I checked the error_code (it's the same for all records) and it is = -1071607685.
I researched this code and got the following:
Symbolic Name = DTS_E_OLEDBDESTINATIONADAPTERSTATIC_UNAVAILABLE
Description = No status is available.
My target table does have a constraint based on a Key. The first thing I checked was the Key value in the fallout table and they all have the correct Key that should be loaded into my target.
Without an error reason, I am lost. Any suggestions of how to figure out why these records are not loading into my target table?
Thanks
January 23, 2015 at 10:23 am
skaggs.andrew (1/23/2015)
Hi All,I am running into an issue where I have created a data flow, source to target, with mainly just look-up transformations. When I execute the dataflow, it completes successfully and I am expecting roughly 200,000 records in the target. When I query the target, I am getting roughly 100,000. I updated the error_output in the target table to redirect row and I have this going to another table. I checked the error_code (it's the same for all records) and it is = -1071607685.
I researched this code and got the following:
Symbolic Name = DTS_E_OLEDBDESTINATIONADAPTERSTATIC_UNAVAILABLE
Description = No status is available.
My target table does have a constraint based on a Key. The first thing I checked was the Key value in the fallout table and they all have the correct Key that should be loaded into my target.
Without an error reason, I am lost. Any suggestions of how to figure out why these records are not loading into my target table?
Thanks
Could it be that the lookup transformations are excluding rows if no match is found? Also, are there any triggers on your destination table that may change the insert behavior?
January 23, 2015 at 10:29 am
I checked all of the look-ups and what I am doing is redirecting records where there is no match to a derived column, giving it a value -9 and then doing a union all between the look-up and derived column. Those records then continue through. (The look-up is to get a key from a dim table)
I do not have any triggers on the target table.
January 23, 2015 at 10:50 am
skaggs.andrew (1/23/2015)
I checked all of the look-ups and what I am doing is redirecting records where there is no match to a derived column, giving it a value -9 and then doing a union all between the look-up and derived column. Those records then continue through. (The look-up is to get a key from a dim table)I do not have any triggers on the target table.
In that case, providing more information would be helpful. Please provide the package, table scripts and sample data if you can. Also indicate what version of SSIS you are using please.
January 23, 2015 at 11:06 am
Here is the constraint on my table:
ALTER TABLE [dbo].[APSB_FACT_SYK10] WITH NOCHECK ADD CONSTRAINT [CK_SCHOOL_YEAR_KEY_10] CHECK (([SCHOOL_YEAR_KEY]=(10)))
GO
ALTER TABLE [dbo].[APSB_FACT_SYK10] CHECK CONSTRAINT [CK_SCHOOL_YEAR_KEY_10]
GO
student_assessment_idassessment_idversion_idstudent_iddate_takenperformance_band_idpointspoints_possibleansweredpercent_correctnumber_of_questionsstudent_id_numSTUDENT_KEYSTUDENT_CURR_SCHOOL_IDSCHOOL_KEYSCHOOL_YEAR_KEYILL_ASSESSMENTS_KEYuser_idlocal_user_idlocal_user_id_charTEACHER_KEYILL_PERFORMANCE_BANDS_KEYILL_VERSIONS_KEYATT_CODE_KEYTERM_KEYTIME_KEYDISCIPLINE_KEYSTATE_TEST_KEYSTUDENT_ACTIVITY_KEYDIBELS_TEST_KEYDIBELS_CLASS_KEYHZ_EK_KEYHZ_STANDARD_KEYHZ_SUBJECT_COURSE_KEYHZ_TEST_KEYILL_REPORTING_GROUPS_KEYPERF_BAND_KEYPERIOD_KEYSECTION_COURSE_KEYErrorCodeErrorColumn
361040594058267162014-08-20 00:00:00.000450621.00000032.0000003265.6300003226716372733005271036864921782717827342880236974-9-9-9-9-9-9-9-9-9-9-9-9-9-9-9-9-10716076850
I already have several data flows that successfully load into this target table, that is what confuses me. I'm not sure why half the records load and half do not.
I am using Visual Studio 2008
January 23, 2015 at 11:30 am
skaggs.andrew (1/23/2015)
Here is the constraint on my table:ALTER TABLE [dbo].[APSB_FACT_SYK10] WITH NOCHECK ADD CONSTRAINT [CK_SCHOOL_YEAR_KEY_10] CHECK (([SCHOOL_YEAR_KEY]=(10)))
GO
ALTER TABLE [dbo].[APSB_FACT_SYK10] CHECK CONSTRAINT [CK_SCHOOL_YEAR_KEY_10]
GO
student_assessment_idassessment_idversion_idstudent_iddate_takenperformance_band_idpointspoints_possibleansweredpercent_correctnumber_of_questionsstudent_id_numSTUDENT_KEYSTUDENT_CURR_SCHOOL_IDSCHOOL_KEYSCHOOL_YEAR_KEYILL_ASSESSMENTS_KEYuser_idlocal_user_idlocal_user_id_charTEACHER_KEYILL_PERFORMANCE_BANDS_KEYILL_VERSIONS_KEYATT_CODE_KEYTERM_KEYTIME_KEYDISCIPLINE_KEYSTATE_TEST_KEYSTUDENT_ACTIVITY_KEYDIBELS_TEST_KEYDIBELS_CLASS_KEYHZ_EK_KEYHZ_STANDARD_KEYHZ_SUBJECT_COURSE_KEYHZ_TEST_KEYILL_REPORTING_GROUPS_KEYPERF_BAND_KEYPERIOD_KEYSECTION_COURSE_KEYErrorCodeErrorColumn
361040594058267162014-08-20 00:00:00.000450621.00000032.0000003265.6300003226716372733005271036864921782717827342880236974-9-9-9-9-9-9-9-9-9-9-9-9-9-9-9-9-10716076850
I already have several data flows that successfully load into this target table, that is what confuses me. I'm not sure why half the records load and half do not.
I am using Visual Studio 2008
Unless I am missing something, it looks like the "SCHOOL_YEAR_KEY" is 27 from what you've posted here...the check constraint is failing and the record therefore excluded. You may have missed something in the mapping of your fields.
January 23, 2015 at 11:44 am
You are correct. That is weird. It's as if the records that are falling out are using the school_key as the school_year_key like the columns shifted or something. Any thoughts if that could be an issue with my mapping? Why would some records have the correct school_year_key and others the values shifted?
January 23, 2015 at 11:48 am
skaggs.andrew (1/23/2015)
You are correct. That is weird. It's as if the records that are falling out are using the school_key as the school_year_key like the columns shifted or something. Any thoughts if that could be an issue with my mapping? Why would some records have the correct school_year_key and others the values shifted?
Very difficult to say unless you are prepared to share your SSIS Package 😛
January 23, 2015 at 11:50 am
Actually, I just recounted and the 16th column is School_Year_Key and the 16th value is 10
January 23, 2015 at 12:12 pm
Build a staging table without the constraint, change that to the destination, and then try to copy over with an insert select and see what error message you get from SSMS.
January 23, 2015 at 12:54 pm
I created a copy of the target table without the constraint, replaced my target table in my SSIS data flow with this new table and everything loaded in successfully with no fallout records. Is that what you meant Nevyn?
January 23, 2015 at 1:47 pm
Well, yes that is half of it. Now you should be able to query the records that violate the would be constraint as this seems to confirm the constraint is the issue
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply