You don't need any of that DTS or SSIS stuff... it's just gonna slow things down. Here's you monster RBAR query converted to set based. The SELECT includes the "Rosetta Stone" that identifies the column mappings.

Stored Procedure for Transfering Inqyiry Header table

ALTER PROCEDURE [T5_REPORTING_FSUSER].[SP_TransferIH]

-- Add the parameters for the stored procedure here

AS

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

--===== Gather and preprocess all of the information that the cursor used to do

SELECT ess.Inquiry_Number AS Inquirey_Number,

ess.ESS_CreationDate AS Date,

ess.Probability_Rating AS Probability,

SUBSTRING(ess.Originator_Name,1,CHARINDEX(' ', ess.Originator_Name)-1) AS From_F,

SUBSTRING(ess.Originator_Name,CHARINDEX(' ', ess.Originator_Name)+1,LEN(ess.Originator_Name)) AS From_L,

sif.Cust_ID AS Customer,

NULLIF(ess.Quote_Type,' ') AS Budgetary_Firm,

ess.ESS_Site_ID AS Site_Location,

ess.Proposal_NeedDate AS Need_Prop_Date,

ess.Estimated_BookDate AS Booking_Date,

ess.Cust_ExWorksDeliveryDt AS Prop_Deliv_Date,

ess.Previously_Quoted AS Prev_Quoted,

CAST(NULL AS INT) AS Old_Inquiry,

LEN(ess.Table_PkgNumber)-LEN(REPLACE(ess.Table_PkgNumber,',','')) AS Number_Units,

sif.Cust_Loc_Type AS [Application],

sif.Cust_Elev_Value AS Elevation,

sif.Cust_Elev_Unit AS Elevation_Units,

sif.Cust_Tem_Des AS Amb_Temperature,

sif.Cust_Tem_Unit AS TempUnits,

sif.Cust_Tem_Min AS Min_Temp,

sif.Cust_Tem_Max AS Max_Temp,

CAST(NULL TINYINT) AS Relative_Humidity,

sif.Cust_Comments AS Site_Notes,

ess.Driver_Change AS Initiated_By,

ess.Competion AS Competition,

ess.Sales_Approach AS Sales_Approach,

ess.Scope_Proposed_Modifications AS Proposed_Mods,

CAST(NULL AS VARCHAR) AS Preferred_Options,

ess.Special_Requirements AS Special_Requirements,

CAST(NULL AS VARCHAR) AS Expectations,

ess.Misc_Comments AS Notes,

ess.ControlSystem_Electr AS PS_Control,

ess.Pneumatic_System AS PS_Pneumatic,

ess.Fuel_System AS PS_Fuel,ess.Start_System AS PS_Start,

ess.Engine_Assemblies AS PS_EngineAssemblies,

ess.Lube_System AS PS_Lube,

ess.Vibration_System AS PS_Vibration,

ess.Seal_System AS PS_Seal,

ess.Enclosure_System AS PS_Enclosure,

ess.Driven_Equipment AS PS_Driven,

ess.Gear_Drives AS PS_GearDrives,

ess.Accessories AS PS_Accessories,

ess.Other AS PS_Other,

ess.Engine_UpRate AS Engine_Uprate,

ess.ReStage AS Restage,

ess.Solonox AS Solonox,

ess.Controls_Retrofit AS Controls_Retrofit,

ess.Systems_Upgrade AS System_Upgrade,

ess.Factory_Refurb AS Refurbishment,

ess.Onsite_Refurb AS OnSiteRefurb,

'FALSE' AS fFastTrack,

'FALSE' AS bPostBookedFTSolution,

ess.Total_Order_Value AS Total_Order_Value,

ess.Comments AS Comments,

ess.ESS_History AS ESS_History,

ess.ESS_Country AS ESS_Country,

ess.ESS_District AS ESS_District,

ess.ESS_Rev AS ESS_Rev,

ess.ESS_Status AS ESS_Status,

ess.Proposal_Urgency AS Proposal_Urgency,

ess.Related_Inquiries AS Related_Inquiries,

ess.Scope AS Scope

INTO #MyHead

FROM T5_Reporting_FSUser.Test_ESS ess

LEFT JOIN dbo.SiteInfoForm sif ON ess.ESS_Site_ID = sif.Cust_ID

WHERE ess.ESS_LNDtTimestamp >= Dateadd(dd,-10,GetDate())

--===== Delete any rows that match the new information

DELETE CSInquiry_Test.dbo.InquiryHeader

FROM CSInquiry_Test.dbo.InquiryHeader ih

INNER JOIN #MyHead t

ON ih.Inquiry_Number = t.Inquiry_Number

--===== Add all the new information. Some will replace the deleted rows,

-- some will be new rows

INSERT INTO CSInquiry_Test.dbo.Inquiryheader(

Inquiry_Number, Date, Probability, From_F, From_L,

Customer, Budgetary_Firm, Site_Location, Need_Prop_Date, Booking_Date,

Prop_Deliv_Date, Prev_Quoted, Old_Inquiry, Number_Units, [Application], Elevation,

Elevation_Units, Amb_Temperature, Temp_Units, Min_Temp, Max_Temp, Relative_Humidity,

Site_Notes, Initiated_By, Competition, Sales_Approach, Proposed_Mods, Preferred_Options,

Special_Requirements, Expectations, Notes, PS_Control, PS_Pneumatic, PS_Fuel,

PS_Start, PS_EngineAssemblies, PS_Lube, PS_Vibration, PS_Seal, PS_Enclosure,

PS_Driven, PS_GearDrives, PS_Accessories, PS_Other, Engine_Uprate, Restage,

Solonox, Controls_Retrofit, System_Upgrade, Refurbishment, OnSiteRefurb,

fFastTrack, bPostBookedFTSolution, Total_Order_Value, Comments, ESS_History,

ESS_Country, ESS_District, ESS_Rev, ESS_Status, Proposal_Urgency,

Related_Inquiries, Scope)

SELECT Inquiry_Number, Date, Probability, From_F, From_L,

Customer, Budgetary_Firm, Site_Location, Need_Prop_Date, Booking_Date,

Prop_Deliv_Date, Prev_Quoted, Old_Inquiry, Number_Units, [Application], Elevation,

Elevation_Units, Amb_Temperature, Temp_Units, Min_Temp, Max_Temp, Relative_Humidity,

Site_Notes, Initiated_By, Competition, Sales_Approach, Proposed_Mods, Preferred_Options,

Special_Requirements, Expectations, Notes, PS_Control, PS_Pneumatic, PS_Fuel,

PS_Start, PS_EngineAssemblies, PS_Lube, PS_Vibration, PS_Seal, PS_Enclosure,

PS_Driven, PS_GearDrives, PS_Accessories, PS_Other, Engine_Uprate, Restage,

Solonox, Controls_Retrofit, System_Upgrade, Refurbishment, OnSiteRefurb,

fFastTrack, bPostBookedFTSolution, Total_Order_Value, Comments, ESS_History,

ESS_Country, ESS_District, ESS_Rev, ESS_Status, Proposal_Urgency,

Related_Inquiries, Scope

FROM #MyHead