ìn case anyone wishes to look at it.

Its not a complex proc - its just a rather complex query 

alter procedure [Schema]. @DateFrom date
           ,...

Possible options

 - define the file as not having headers on it, load into a staging table with all columns defined similar to what you did, and then parse...

can you put code for that proc here? if it is the one I saw before it won't deal with dates datatypes.

Really you should not set it to off and should code accordingly.

But in either case it is doing what the manual says.
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql?view=sql-server-2017

set...

You could setup a msa/gmsa just to perform that task - give it the required permissions to the folders on both servers, and have on a windows schedule on one...

As this is a error that occurs only at times, mainly at particular periods it leads me to believe the most likely cause is something on the network temporarily blocking...

I don't quite understand your question.

Is it maybe that you are asking what is the fastest way to extract data out of a sql table onto a file?...

have a look at
https://www.brentozar.com/archive/2013/01/best-practices-table-partitioning-merging-boundary-points/
and http://www.dbdelta.com/table-partitioning-best-practices/
for some examples and explanations.

this may also be of help on the partition aspect of it.

32/64 bit has nothing to do with network speed - if speed is lower the issue is with the package and what it is doing and the configuration of the...

Its always hard to help with third party tools - the maker should be your first point of contact to see if performance can be improved with settings or workarounds...

If you run the package through a sql server agent job, within a step defined as Integration Services Package there is a tick box on the advanced properties to elect...

if  I understand correctly what you need I think the following covers it

select *
from items
where (len(@items) > 0
   and item_no IN (