This may work better in SSIS.  You can read the file one line at a time and direct each line to the proper table in a script task.

A...

For Visual Studio, you can set the .Net framework version to different levels to get different versions of the SMO libraries.  Assuming you are trying to use the SMO object...

You can open a New Query window on just about any version of SQL Server from the latest Management Studio.  I say "just about" because I can't test it on...

This works with all the values hardcoded.  In practice, the Detail subquery would probably be a correlated subquery based on something in the header row.
SELECT HdrAttrib1,

A clustered index must be unique, but not necessarily in the columns you use to define it.  For a non-unique clustered index, SQL Server will automatically add a hidden four-byte...

it is just too darn difficult to choose a good clustering key. As a matter of fact, choosing a good—the “right”—clustering key is almost impossible if there are more than...

I think there's an important distinction between sparse attributes known at design time, and a design that has to accommodate unknown attributes added by users over time.  It would be...

I should point out that the query had the original split name parts because the test data I used came from an Employee table with names that were already split. ...

To begin with, trying to parse names is incredibly difficult and is something you should buy as a service from companies that have invested a lot of effort into it. ...

I think you need to pass the parameter some other way, like creating a parameter table in a database.  The calling VBA could add a row to the table with...

They may look the same, but there are more differences than just leaving out the word DECLARE.

Parameter declarations can have the attributes OUTPUT, READONLY, and VARYING; local variables...

I prefer using arithmetic over string expressions that require allocating and garbage collecting extra strings for every intermediate step:

DECLARE @dec FLOAT;
SET @dec = 94.3;
PRINT...

A log file backup doesn't remove transactions from the log file, but it releases them.  The sections of the log file that have been fully committed and backed up are...

Task Factory is another package of SSIS components that includes an SFTP task.  It was originally sold by Pragmatic Works, but is now available from Sentry One.  
https://www.sentryone.com/products/task-factory/high-performance-ssis-components

A better answer is that, in general, curly braces in a query are used for ODBC escape sequences.  Meaning the enclosed text is not standard SQL or T-SQL, it is...