SSIS Package stored procedure

ALTER PROC [dbo].[InsertInto_BGCMAddress]   
            @BCE_Address_ID varchar(100)   
           ,@Address_Line_1 varchar(100)   
           ,@City varchar(50)   
           ,@State_Province varchar(100)   
           ,@Postal_Area varchar(100)   
           ,@Country varchar(100)   
           ,@Phone_Number varchar(100)
   
AS BEGIN   
   
DECLARE @BGCM_ID INT ,@COUNT INT
 SELECT @BGCM_ID = isnull(BGCM_ID,0) FROM PMP_BGCMCustomer WHERE BCE_ID=@BCE_Address_ID  and Customer_Type <> 'Physician'
 IF(@BGCM_ID <> 0)
  BEGIN
       INSERT INTO PMP_BGCMAddress   
       ([BGCM_ID]   
       ,[BCE_Address_ID]   
       ,[Last_Modified_Date]   
       ,[Last_Modified_Source]   
       ,[Address_Line_1]   
       ,[City]   
       ,[State_Province]   
       ,[Postal_Area]   
       ,[Country]   
       ,[Phone_Number]   
       ,[Create_Date]   
       ,[Address_Status_Code])   
    VALUES   
       (@BGCM_ID   
       ,@BCE_Address_ID   
       ,getdate()   
       ,'CE'   
       ,@Address_Line_1   
       ,@City   
       ,@State_Province   
       ,@Postal_Area   
       ,@Country   
       ,@Phone_Number   
       ,getdate()   
       ,'Active')
      
 END
END

--------------------------------------------------------------------------------------------------------------------

ALTER PROC [dbo].[InsertInto_BGCMAffiliation]
     @From_BGCM_ID as VARCHAR(100),
           @To_BGCM_ID as VARCHAR(100),
           @Last_Modified_Date as datetime,
           --@Last_Modified_Source as VARCHAR(100),
           @Create_Date as datetime
           --@Affiliation_Status_Code varchar(100)
AS BEGIN
Declare @FBGCM_ID as VARCHAR(100),@TBGCM_ID as VARCHAR(100),@From_BGCM_ID_Exist as VARCHAR(100),@To_BGCM_ID_Exist as VARCHAR(100)

SELECT @FBGCM_ID = BGCM_ID FROM PMP_BGCMCustomer
WHERE BCE_ID = @From_BGCM_ID AND Customer_Type = 'Physician'

SELECT  @TBGCM_ID = BGCM_ID FROM PMP_BGCMCustomer
WHERE BCE_ID  = @To_BGCM_ID AND Customer_Type <> 'Physician'
IF(@FBGCM_ID <> null and @TBGCM_ID <> null)
BEGIN
Select @From_BGCM_ID_Exist = From_BGCM_ID from PMP_BGCMAffiliation where From_BGCM_ID = @FBGCM_ID
Select @To_BGCM_ID_Exist = To_BGCM_ID from PMP_BGCMAffiliation where To_BGCM_ID = @TBGCM_ID
IF (@From_BGCM_ID_Exist <> null AND  @To_BGCM_ID_Exist <> null)
BEGIN
UPDATE PMP_BGCMAffiliation SET
 Last_Modified_Date = @Last_Modified_Date,
 Last_Modified_Source = 'CE',
 Create_Date = @Create_Date,
 Affiliation_Status_Code = 'Active' where From_BGCM_ID = @FBGCM_ID AND To_BGCM_ID  = @TBGCM_ID
END
ELSE
BEGIN
INSERT INTO PMP_BGCMAffiliation
(From_BGCM_ID,To_BGCM_ID,Last_Modified_Date,Last_Modified_Source,Create_Date,Affiliation_Status_Code)
    VALUES
(@FBGCM_ID,@TBGCM_ID,@Last_Modified_Date,'CE',@Create_Date,'Active')
END
END
END
----------------------------------------------------------------------------------
ALTER PROC [dbo].[InsertInto_BGCMStateLicense]     
     
 @BCE_State_License_ID varchar(100),     
 @State_License_State varchar(100),     
 @State_License_Number varchar(100),     
 @State_License_Expiration_Date varchar(100) , 
 @MA_Recipient_ID varchar(100)    
     
AS BEGIN     
 DECLARE @BGCM_ID INT
      
 SELECT @BGCM_ID = BGCM_ID FROM PMP_BGCMCustomer WHERE BCE_ID=@BCE_State_License_ID   
   
 IF(@BGCM_ID <>'' )
 BEGIN
 Declare @BCE_State_License_ID_exist as varchar(100)

 select @BCE_State_License_ID_exist = BCE_State_License_ID from PMP_BGCMStateLicense where BGCM_State_License_ID = @BCE_State_License_ID_exist

  IF(@BCE_State_License_ID_exist > 0)
  BEGIN
  UPDATE PMP_BGCMStateLicense SET [BGCM_ID] =@BGCM_ID,
       [Last_Modified_Date] =GETDATE(),
       [Last_Modified_Source]= 'CE',
       [State_License_Status]= 'Active',
       [State_License_State]=@State_License_State,
       [State_License_Number]=@State_License_Number,
       [State_License_Expiration_Date]=@State_License_Expiration_Date,
       MA_Recipient_ID = @MA_Recipient_ID
       WHERE [BCE_State_License_ID] =@BCE_State_License_ID
  END
  ELSE
  BEGIN 
 
     INSERT INTO PMP_BGCMStateLicense     
       ([BGCM_ID]     
       ,[BCE_State_License_ID]     
       ,[Last_Modified_Date]     
       ,[Last_Modified_Source]     
       ,[State_License_Status]     
       ,[State_License_State]     
       ,[State_License_Number]     
       ,[State_License_Expiration_Date],MA_Recipient_ID)     
     VALUES     
       (@BGCM_ID     
       ,@BCE_State_License_ID     
       ,GETDATE()     
       ,'CE'     
       ,'Active'     
       ,@State_License_State     
       ,@State_License_Number     
       ,@State_License_Expiration_Date,@MA_Recipient_ID)
  END     
   END      
END 

----------------------------------------------------------------------------------------

ALTER PROC [dbo].[InsertInto_ContactBGCMAddress]     
   @BCE_Address_ID varchar(100)     
     ,@Address_Line_1 varchar(100)     
     ,@City varchar(50)     
     ,@State_Province varchar(100)     
     ,@Postal_Area varchar(100)     
     ,@Country varchar(100)     
     ,@Phone_Number varchar(100)  
AS
BEGIN   
 Begin tran insertContact
  DECLARE @BGCM_ID INT, @BiometContactId VARCHAR(100),@COUNT INT  
  Declare curContact Cursor
  For 
  select From_BGCM_ID from PMP_BGCMAffiliation where To_BGCM_ID =  @BCE_Address_ID               
  open  curContact
  Fetch next from curContact into @BiometContactId
  While @@FETCH_STATUS = 0
  Begin

   SELECT @BGCM_ID = BGCM_ID FROM PMP_BGCMCustomer WHERE BCE_ID= @BiometContactId  and Customer_Type = 'Physician'
   IF (EXISTS(select BGCM_ID from PMP_BGCMAddress where BGCM_ID = @BGCM_ID))
BEGIN
 
   --update PMP_BGCMAddress set
   --BCE_Address_ID = @BCE_Address_ID,
   --Last_Modified_Date = getdate(),   
   --Last_Modified_Source = 'CE',
   --Address_Line_1 = @Address_Line_1,
   --City = @City,
   --State_Province = @State_Province,
   --Postal_Area = @Postal_Area,
   --Country = @Country,
   --Phone_Number = @Phone_Number,
   --Create_Date = getdate(),
   --Address_Status_Code = 'Active'
   --where BGCM_ID = @BGCM_ID
    SET @COUNT = 1
 
END
   ELSE
BEGIN
   INSERT INTO PMP_BGCMAddress     
     ([BGCM_ID]     
     ,[BCE_Address_ID]     
     ,[Last_Modified_Date]     
     ,[Last_Modified_Source]     
     ,[Address_Line_1]     
     ,[City]     
     ,[State_Province]     
     ,[Postal_Area]     
     ,[Country]     
     ,[Phone_Number]     
     ,[Create_Date]     
     ,[Address_Status_Code])     
   VALUES     
     (@BGCM_ID     
     ,@BCE_Address_ID     
     ,getdate()     
     ,'CE'     
     ,@Address_Line_1     
     ,@City     
     ,@State_Province     
     ,@Postal_Area     
     ,@Country     
     ,@Phone_Number     
     ,getdate()     
     ,'Active')
   
END
 
   Fetch next from curContact into @BiometContactId
End
  close curContact
  DEALLOCATE curContact
 Commit tran insertContact
End

-----------------------------------------------------------------------------------------------------------