Ok I have access to a stored procedure (i didn't write it) which is used in our ERP to import sales orders using pre-defined fields .at least i believe it does . I want to reuse it to process a flat file or excel . would this be done best in SQL or SSIS as part of a package ?
and most importantly how would i do it is SQL ?
I would use SSIS myself, must you could look into using the OPENROWSET command in SQL.
https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-2017
I'm just not sure how to pass the data through the SP . The SP is pretty big but I'm imaginign this sort of scenario in my un-educated brain 
but its never that simple
CREATE TABLE #TEXTFILE_1( FIELD1 varchar(100) , FIELD2 varchar(100) , FIELD3 varchar(100) , FIELD4 varchar(100)); BULK INSERT #TEXTFILE_1 FROM 'C:\STUFF.TXT' WITH (FIELDTERMINATOR =' | ',ROWTERMINATOR =' \n') / insert into wsp_SalesOrdersImport_JL_EDI_JJC from #TEXTFILE_1 txt drop table #TEXTFILE_1
What am i doing wrong , i cannot see the issue with my flat file
CREATE TABLE #TEXTFILE_14
(CustomerId nvarchar(200),
DueDate datetime,
DelAddress nvarchar(200),
DelCity nvarchar(200),
DelRegion nvarchar(200),
DelPostalCode nvarchar(200),
DelCountryId nvarchar(200),
CustomerOrderNumber nvarchar(200),
ItemId nvarchar(200),
Quantity decimal(17,5));
-- select * from #TEXTFILE_12
BULK INSERT #TEXTFILE_14
FROM '\\ART-WM01\Import Services\TEST DUMP\Test delivery input 15.TXT'
WITH ( FIELDTERMINATOR =', ',ROWTERMINATOR ='\n'
--,ERRORFILE = '\\ART-WM01\Import Services\TEST DUMP\\myRubbishData.log' )
drop table #TEXTFILE_14
JOHNSTE4,03/04/2019 00:00,19 BOB ROAD,WEMBLEY,LONDON,HA8 7JH,GREAT BRITAIN,23,UAG3FKO40025,1
keep getting the below error
Msg 4832, Level 16, State 1, Line 23
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 23
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 23
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
You need to validate the row terminator - it is probably only a line feed (LF) character in the file. Open the file in a text editor like Notepad++ and view all symbols/characters. The standard row terminator for Windows is a CRLF and the \n in Windows is treated as \r\n.
There is also the option of:
BULK INSERT ...
FROM ...
WITH (FORMAT = 'CSV');
There are additional parameters that can be included for CSV format if needed.
Now - to your original question. This will get the data into a table in SQL Server but will not utilize the stored procedure to actually load that data into the application. To accomplish that, once you have the data in a table you will need a cursor to loop over each row - and call the stored procedure with the appropriate parameters.
That can all be done in SSIS also. In SSIS - you start with a data flow to load the file using a flat file source and a recordset destination. You setup the recordset destination with the fields you need from the file.
You then need to create variables for each field returned from the recordset.
The next item (in the control flow) would be a for each loop - where you loop over the recordset you created in the data flow. In the for each loop - you map the fields from the recordset to the variables defined.
Inside the for each - you use an Execute SQL Task with an ADO.NET connection type to your database. You specify the stored procedure to be executed - and map the parameters to the the variables.
This will then read the file - loop over each record in the file - and call the stored procedure with the variables from the file.
It sounds like a lot...but once you see how it is done it isn't anything more than creating a cursor in T-SQL.
all working thanks for the assistance