PMP_BGCMAffiliation

Create PROC [dbo].[InsertInto_BGCMAffiliation]
     @From_BGCM_ID as VARCHAR(100),
           @To_BGCM_ID as VARCHAR(100),
           @Last_Modified_Date as datetime,
           @Create_Date as datetime
          
AS BEGIN
Declare @FBGCM_ID as VARCHAR(100),@TBGCM_ID 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'
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

CURSOR EXAMPLE SQL SERVER

Create 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)  
  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'
   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')
    

    --Update PMP_BGCMAffiliation SET From_BGCM_ID = @BGCM_ID where From_BGCM_ID = @BiometContactId
      
   Fetch next from curContact into @BiometContactId
  End
  close curContact
  DEALLOCATE curContact
 Commit tran insertContact
End

If exist......... else if NOT Exist

Create procedure [dbo].[PMP_CML_AddAttendeeInformationAndAddress]
 @PolarisCustomerID AS VARCHAR(50)='',
 @Customer_Id AS VARCHAR(50)='',
 @FullName AS VARCHAR(100)='',
 @Customer_Type AS VARCHAR(50)='',
 @NPINumber AS INT=0,
 @PolarisAddressID AS VARCHAR(50)='',
 @Address_ID AS VARCHAR(100)='',
 @AddressLine1 AS VARCHAR(100)='',
 @City AS VARCHAR(50)='',
 @State AS VARCHAR(50)='',
 @PostalCode AS NVARCHAR(50)='',
 @createDate AS datetime,
 @SourceSystem AS VARCHAR(100),
 @Country AS varchar(50)
AS
  BEGIN TRAN
--------RecipientType_id,State_Id,Country
DECLARE
@RecipientType_id INT,
@State_Id INT,
@COUNT INT,
@intErrorCode INT,
@AttendeeInfoAttendee_id INT

select @RecipientType_id = RecipientType_id from PMP_RecipientType where RecipientType_Name = @Customer_Type
select @State_Id = State_Id from PMP_StateMaster where State_initials = @State
 
     IF(EXISTS(SELECT A.*,AI.PolarisCustomerID,AI.CustomerID,AI.Fullname_organizationName,AI.RecipientType_id,AI.NPINumber
     FROM PMP_AttendeeInformation AS AI INNER JOIN
     PMP_Address AS A ON AI.Attendee_Id = A.Attendee_Id
     WHERE --AI.PolarisCustomerID = @PolarisCustomerID
         AI.CustomerID = @Customer_Id
     AND AI.Fullname_organizationName = @FullName
     AND AI.RecipientType_id = @RecipientType_id
     --AND AI.NPINumber = @NPINumber
     --AND A.PolarisAddressID = @PolarisAddressID
     --AND A.Attendee_Id = @Customer_Id
     AND A.Location_id =  @Address_ID
     AND A.AddressLine1 = @AddressLine1
     AND A.City = @City
     AND A.State_Id = @State_Id
     AND A.PostalCode = @PostalCode
     AND Convert(VARCHAR(10),AI.CreatedDate,121) = Convert(VARCHAR(10),@createDate,121)
     AND  Convert(VARCHAR(10),A.CreateDate,121) = Convert(VARCHAR(10),@createDate,121)
         ))
     BEGIN
     -------No Changes in Existing records of Core Tables
     SET @COUNT =1
     END
     ELSE IF(NOT EXISTS(SELECT A.*,AI.PolarisCustomerID,AI.CustomerID,AI.Fullname_organizationName,AI.RecipientType_id,AI.NPINumber
     FROM PMP_AttendeeInformation AS AI INNER JOIN
     PMP_Address AS A ON AI.Attendee_Id = A.Attendee_Id
     where    AI.CustomerID = @Customer_Id
     --AND AI.Fullname_organizationName = @FullName
     --AND AI.RecipientType_id = @RecipientType_id
     --AND AI.NPINumber = @NPINumber
     --AND A.PolarisAddressID = @PolarisAddressID
     --AND A.Attendee_Id = @Customer_Id
       AND A.Location_id =  @Address_ID
     --AND A.AddressLine1 = @AddressLine1
     --AND A.City = @City
     --AND A.State_Id = @State_Id
     --AND A.PostalCode = @PostalCode
  AND Convert(VARCHAR(10),AI.CreatedDate,121) = Convert(VARCHAR(10),@createDate,121)
  AND  Convert(VARCHAR(10),A.CreateDate,121) = Convert(VARCHAR(10),@createDate,121)
     ))
    
     BEGIN
    
    
     INSERT INTO PMP_AttendeeInformation(PolarisCustomerID,CustomerID,Fullname_organizationName,RecipientType_id,NPINumber,Isorg,CreatedDate,Creator)
     VALUES(@PolarisCustomerID,@Customer_Id,@FullName,@RecipientType_id,@NPINumber,1,@createDate,@SourceSystem)
     select  @AttendeeInfoAttendee_id = max(Attendee_Id) from PMP_AttendeeInformation --where  CustomerID = @Customer_Id and  Fullname_organizationName = @FullName
     INSERT INTO PMP_Address(PolarisAddressID,Attendee_Id,Location_id,AddressLine1,City,State_Id,PostalCode,Country,CreateDate,Creator)
     VALUES(@PolarisAddressID,@AttendeeInfoAttendee_id,@Address_ID,@AddressLine1,@City,@State_Id,@PostalCode,@Country,@createDate,@SourceSystem)
    
     END
    
     ELSE
    
     BEGIN
   ------------------------------ Update Record ---------------------------------------
    
     select  @AttendeeInfoAttendee_id = max(Attendee_Id) from PMP_AttendeeInformation
    
 UPDATE PMP_AttendeeInformation SET
     PolarisCustomerID = @PolarisCustomerID
     ,Fullname_organizationName = @FullName
     ,RecipientType_id = @RecipientType_id
     ,NPINumber = @NPINumber
  ,Creator = @SourceSystem
     WHERE Attendee_Id  = @AttendeeInfoAttendee_id
   
    UPDATE PMP_Address SET
     PolarisAddressID = @PolarisAddressID
     ,Location_id = @Address_ID
     ,AddressLine1 = @AddressLine1
     ,City = @City
     ,State_Id = @State_Id
     ,PostalCode = @PostalCode
     ,Country = @Country
  ,Creator = @SourceSystem
     WHERE Attendee_Id = @AttendeeInfoAttendee_id
   
     END
    
     SELECT @intErrorCode = @@ERROR
 IF (@intErrorCode <> 0) GOTO PROBLEM
 COMMIT TRAN
    ----------------------------------------
   PROBLEM:
 IF (@intErrorCode <> 0)
 BEGIN
     ---PRINT 'Unexpected error occurred!'
   ROLLBACK TRAN
    END

SSIS Tutorials

truncate table ee;
DBCC CHECKIDENT('ee',reseed,100);


File System task use to create rename folder


FTP Task
Send Or Receive files on Remote Servers
delete local or remote files
create local or remote directories
delete local or remote directories
right click connection manager section ---> new connection --> set properties
Add Ftp task on control flow window --- double click---select ftpConnection
go to second tab fileTransfer---where u set remote path

Send Mail Task:-
go to connection manager section right click ---> select New Connection --- > then select SMTP
set SMTP server name = localhost
now drag send email tak on control flow window -->double click
under mail tab select smtpConnection --- and set other properties
Precedence Constraint

Derived Column
Upper([FirstName]+ ""+[LastName])

DataViewer
right click Prcedence select Data view select Grid OK..

Flate File Source--- take data on file and insert into destination

Union All --- Union two flat to one

Sort ----- sorting column

Audit ---- contain information of the package

SMTP server response: 550 5.7.1 Unable to relay or cannot pick up iis directory

1) Go to: start > settings > control panel > Administrative Tools > Internet Information Services
2) Expand the " (local computer)" node
3) Right click on your SMTP server > go to "Properties"
4) Click "Access" tab
5) Under Relay Restrictions, click the "Relay" button
6) Click "Add"
7) Select "Single Computer" and enter IP address 127.0.0.1
8) Hit OK, OK, OK (until the properties dialog is closed)

and also web.config set smtp server = '127.0.0.1'

mobile application connect

http://www.youtube.com/watch?v=j9XlVUif2G8&NR=1

how to Remove log file on database

http://4rapiddev.com/sql-server/delete-shrink-eliminate-transaction-log-ldf-file/