Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Transfer Objects in SSIS

    I am trying to perform a simple object transfer. All I wantr to do is transfer every table. If the table already exists on the destination, drop it and recreate it.

    I have "Drop Objects" set to TRUE. In my mind, this means if the table is there, drop it first and then recreate it. This is not happening at all.

    I get a warning on every single table stating that it already exists.

    Also, the package is complaining about a foreign key that is just fine. Here is the error:


    There are no primary or candidate keys in the referenced table 'VisitPatient' that match the referencing column list in the foreign key 'FK_ChartReviewChecklistQuestionData_VisitPatient' .
    I'm assuming this means that there is no matching column in the parent table to the column in the foreign key definition. The funny thing is that the column DOES exist in the parent table. Here's the DDL for the parent table:

    Code:
    CREATE TABLE [dbo].[VisitPatient](
     [VSPAT_ID] [int] IDENTITY(1,1) NOT NULL,
     [VSPAT_VS_ID] [int] NOT NULL,
     [VSPAT_PAT_ID] [int] NOT NULL,
     [VSPAT_LinkToPrevious_VSPAT_ID] [int] NULL,
     [VSPAT_EmployerID] [int] NULL CONSTRAINT [DF_VisitPatient_VSPAT_EmployerID]  DEFAULT (0),
     [VSPAT_FirstTimeVisit] [bit] NULL CONSTRAINT [DF_VisitPatient_VSPAT_FirstTimeVisit]  DEFAULT (1),
     [VSPAT_SummaryText] [varchar](1024) NULL,
     [VSPAT_ReviewedConsentForm] [bit] NULL CONSTRAINT [DF__VisitPati__VSPAT__04E4BC85]  DEFAULT (0),
     [VSPAT_ReviewedPatientDemographics] [bit] NULL,
     [VSPAT_ReviewedInsurance] [bit] NULL,
     [VSPAT_ReviewedVisitHistory] [bit] NULL,
     [VSPAT_ReviewedMedicalHistory] [bit] NULL,
     [VSPAT_ReviewedCurrentMedications] [bit] NULL,
     [VSPAT_ReviewedMedicationAllergies] [bit] NULL,
     [VSPAT_ReviewedPrimCareProv] [bit] NULL,
     [VSPAT_ReviewedNoPCPAdvice] [bit] NULL,
     [VSPAT_InternalComment] [varchar](4096) NULL,
     [VSPAT_PrimCareProv_PRIMCP_ID] [int] NULL,
     [VSPAT_AbortReasonCategory] [varchar](30) NULL,
     [VSPAT_AbortReasonDescription] [varchar](255) NULL,
     [VSPAT_GalvanonActivityID] [varchar](50) NULL,
     [VSPAT_REVREQAUTOSTAT_ID] [int] NULL,
     [VSPAT_REVSTAT_ID] [int] NULL,
     [VSPAT_ReviewRequiredByDateTime] [datetime] NULL,
     [VSPAT_Followup_EMAILQ_ID] [int] NULL,
     [VSPAT_RequiresReview] [int] NULL,
     [VSPAT_RequiresFollowup] [int] NULL,
     [VSPAT_PaymentType] [varchar](30) NULL,
     [VSPAT_PaymentAmount] [money] NULL,
     [VSPAT_VSPATBAL_CategoryID] [int] NULL,
     [VSPAT_BalanceComment] [varchar](255) NULL,
     [VSPAT_BillBatch_ID] [int] NULL,
     [VSPAT_FluFastTrackVisit] [bit] NULL CONSTRAINT [DF_VisitPatient_VSPAT_FluFastTrackVisit]  DEFAULT (0),
     [VSPAT_LastUpdateDateTime] [datetime] NULL,
     [VSPAT_LearningNeedsAssessmentComplete] [bit] NULL,
     [VSPAT_Management_REVREQAUTOSTAT_ID] [int] NULL,
     [VSPAT_Management_REVSTAT_ID] [int] NULL,
     [VSPAT_ManagementFollowup_EMAILQ_ID] [int] NULL,
     [VSPAT_JointCommissionPreVisitVerification] [bit] NULL,
     [VSPAT_Follow_Up_Pharmacy_ID] [int] NULL,
     [VSPAT_GalvanonMemberID] [varchar](50) NULL,
     [VSPAT_ConsentRelationship] [varchar](50) NULL,
     [VSPAT_SecondaryEMPLOYER_ID] [int] NULL,
     [VSPAT_OtherEmployerName] [varchar](255) NULL,
     [VSPAT_SecondaryEmployerOtherName] [varchar](255) NULL,
     [VSPAT_CR_MDCheckOff] [bit] NULL,
     [VSPAT_CR_RMDCheckOff] [bit] NULL,
     [VSPAT_CR_MOCheckOff] [bit] NULL,
     [VSPAT_SIGNIN_ID] [int] NULL,
     CONSTRAINT [VisitPatient_PK] PRIMARY KEY NONCLUSTERED
    Here's the DDL for the child table:

    Code:
    CREATE TABLE [dbo].[ChartReviewChecklistQuestionData](
     [CHRTREVCHKLSTDATA_ID] [int] IDENTITY(1,1) NOT NULL,
     [CHRTREVCHKLSTDATA_VSPAT_ID] [int] NOT NULL, [CHRTREVCHKLSTDATA_CHRTREVCHKLSTQUES_ID] [int] NULL,
     [CHRTREVCHKLSTDATA_REVTYPE_ID] [int] NOT NULL,
     [CHRTREVCHKLSTDATA_DateTime] [datetime] NOT NULL,
     [CHRTREVCHKLSTDATA_Reviewer_USER_ID] [int] NOT NULL,
     [CHRTREVCHKLSTDATA_EnteredBy_USER_ID] [int] NOT NULL,
     [CHRTREVCHKLSTDATA_Checked] [bit] NOT NULL,
     CONSTRAINT [PK_ChartReviewChecklistQuestionData] PRIMARY KEY CLUSTERED 
    (
     [CHRTREVCHKLSTDATA_ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    ALTER TABLE [dbo].[ChartReviewChecklistQuestionData]  WITH CHECK ADD  CONSTRAINT [FK_ChartReviewChecklistQuestionData_VisitPatient] FOREIGN KEY([CHRTREVCHKLSTDATA_VSPAT_ID])
    REFERENCES [dbo].[VisitPatient] ([VSPAT_ID])
    GO
    ALTER TABLE [dbo].[ChartReviewChecklistQuestionData] CHECK CONSTRAINT [FK_ChartReviewChecklistQuestionData_VisitPatient] REFERENCES [dbo].[VisitPatient] ([VSPAT_ID])
    
    As you can see, the foreign key references the VSPAT_ID in the VisitPatient table. I have no idea what it is complaining about.

    Thanks in advance...!!


    --------------------------------------------------------------------------------
    A. M. Robinson
    Last edited by ansonee; 03-13-09 at 16:31.
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Agreed that you have the column , but is it dedfined as the primary key for the table ?
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    Yes...the column is defined as the Primary Key...
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •