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 message for (updategram to insert data into sales_dump table)
1 send message 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.
thanks