Both SQL Server? Can you use linked servers?

Can you confirm that your Excel file contains only 2 columns? If so, that's an unusual format, but there should be a way ...

Why use SSIS for this? Sounds like a (relatively) straightforward stored proc.

Yuk, that is a horrendous requirement. Is there any way that you can tidy up the source data, rather than having to code around it - I am almost certain...

Why does the column order matter here?

Can't you just grant the necessary folder permissions to the SQL Agent user and use a UNC path to access the files?

Phil

Further to Jack's post, the UNION also forces a sort - so you can remove your sort transformation completely and change your query to:

select lastname, fisrstname, titile, cellphoneno , ...

The fact that you need only column 1 makes this a lot easier, I think.

Just import the whole record into a single column on SQL Server and then run an...

Could it be a permissions issue with the SQL Agent service user?

These look like variable-length records with variable numbers of spaces between the fields ... a bit of a nightmare.

Is it true to say that if two or more consecutive spaces...

No probs - it's an interesting thread.

To get the maximum response, I suggest that you create a new thread in the T-SQL forum specifically with this requirement, eg

Given the following...

🙂 I never said that it was going to be easy.

But there are some damn clever people in this forum and I'll bet that more than one of them could...

If your SQL is really nifty, you may be able to code this up front in your SELECT statement by having a column at the end of every record which...

Sounds like a permissions issue. Check that the SQL Agent service user has rights to access and delete the files.

Phil

Just to clarify:

For each Excel file, you want to

a) Populate one line of an ImportLog table (LogID, Filename, LoadDate)

b) Populate a physical table with the Excel source data, plus...