When you use BULK INSERT or OPENROWSET(BULK...), it is important to understand how [!INCLUDEssNoVersion] version handles impersonation. For more information, see "Security Considerations," later in ...

description: Find out how to use Transact-SQL statements to bulk import data from a file to a SQL Server or Azure SQL Database table, including security considerations.

My plan was to get the text file and do a Bulk insert into a holder table in Sql server 2000 via the bulk insert command inside a stored procedure.

Good old FTP and flat file import will do the trick reliably and never take a day off. Here are the basic steps to automate an FTP download in Windows followed by a CSV insert into MS SQL Server.