I am able to read the csv file and map it to an updategram and succesfully inserting the received filename and the bulk data into the database, but now the issue i have is , I have to also execute a stored procedure immediately after the updategram send port.the stored procedure does not accept any parameter not returns any values. (what it does is as follows:
create PROCEDURE [dbo].[Usp_Insert_CustomerInfo]
retreive filename from sales_dump table and get the dealerid based on the dealercode from dbo.dealer table
DECLARE @filename varchar(255)
DECLARE @DealerCode varchar(50)
DECLARE @DealerID int
Select @filename = Reverse(left(Reverse(ReceivedFileName),Charindex('\',Reverse(ReceivedFileName))-1)) From dbo.Sales_DataDump
SET @DealerCode = (select * from dbo.fnParseStringTSQL(@filename,'_'))
SET @DealerID = (select dealerid from dbo.Dealer where dealercode = @Dealercode)
--FROM SALES_DATADUMP INTO --1) INSERT DATA INTO Customer_Contact_Info_Detail
INSERT dbo.Customer_Contact_Info_Detail(CustomerID,DealerID,Address,Address2 ,City,State,ZipCode,HomePhone,OfficePhone,CellPhone,Email) SELECT CustomerID,@DealerID,Address,Address2 ,City,State,ZipCode,HomePhone,OfficePhone,CellPhone,Email FROM dbo.Sales_DataDump
----------------------------------------------------------------------------------------THIS IS THE STORED PROCEDURE THAT I NEED TO EXECUTE IN THE SEND PORT OF ORCHESTRATION AFTER THE SEND PORT OF UPDATEGRAM IS EXECUTED (AND I AM NOT ABLE TO EXECUTE ANY CODE AFTER THE SEND PORT FOR UPDATEGRAM IS IMPLEMENTED.SO BASICALLY - I NEED 1 RECEIVE PORT (for flat file), 1 send port for (updategram to insert data into sales_dump table)1 send port to (execute Stored procedure call wihtout any parameters or no return values) just trigger execute.is this possible (i have been breaking my head on this issue since 1 week. (its very urgent) any help is gr8tly appreciated.