Using sql server management studio I want to do 2 things.

- Import all txt files from a directory as tables into my DB.
- Add an additional field to each new table which contains the table name.

Can anyone help me?

• Moved by Kalman Toth 10 hours 43 minutes ago Not T-SQL

You can import data from files using BULK INSERT, but without some heavy duty use of SQLCMD you're not going to be able to loop through them.

Why would you want the table name as a column in the table? Do you work the Department of Redundancy Department?

There are basically couple of ways to load data into SQL Server from text file

• SQL Server import export wizard 
• OPENROWSET
• bcp Utility
• bulk insert 
• SSIS (My fav)
• .NET application (may be web form or windows) - Check this answer from StackOverflow

Like Patrick Hurst has mentioned that you can utilize BULK insert but its name indicate it won't go record by record . I load in batches. So, you won't be able to apply business logic in between.

Your requirement can be done with any one of the above specified list (may be more, which I can not recall right now :) )

SSIS, SQL Server import export wizard and .NET can provide you very well error handling mechanism.  

Let us know if you need more help on that.

Thanks, I'm working my way through the SSIS now.

And any idea how I could an additional field to each table which contains the table name?

An odd request on the face of it, I know.

Each table is the exact same in structure but from a different system.

The table name contains the System ID.

Adding the table name as a field will be a reference for me when I join all the tables.

There are couple of ways to do it:

1. File naming conventions : YourFileName_SystemId.txt

You can create one extra column in table or may be name a table with system id. Then after loading the data you can update that column with the system id.

   components required:

•  ForEachLoopContainer - To get file and store file name in a variable
•  Expression Task (if SSIS 2012 or above) or variable expression or script task to seperate file name from systemId
•  Data Flow Task - to load data
•  Execute SQL Task - to update last column or specific table based on precedence constraints  

Note: You can also add extra column in data flow via derived column transformation.

Ex.:

Load file via foreach loop

Split string via seprator

2: Loading different system file in different folder

you can have predefined folder structure ex: SystemId1, SyetemId2 etc and can download respective txt file in their system folder. Then you can easily identity which file belong to which system and then load it to the respective table.

3: Use first row of text file to hold the system value

Use first row to have the system id and then skip row while fetching data. 

Note: Then you have to read first row through script task to get the system id.

hope this will help

Back to SSIS.

I'm running windows 8.1 with SQL.Server.2012

I've admin permissions on the PC but can't access SSIS because Access is denied like below.
However the solution proposed below is not appropriate for me as I can't find DCOM User Group Membership on my Windows 8.1 machine.
Any suggestions how I can get around this?

http://www.mssqltips.com/sqlservertip/3086/how-to-resolve-ssis-access-denied-error-in-sql-server-management-studio/

"Adding the table name as a field will be a reference for me when I join all the tables."

Don't. Hints for you do not belong in your db. They belong in your code:

SELECT a.*, 'Table one' AS hint, b.*, 'Table two' AS anotherHint
  FROM table1 a
    INNER JOIN table2 b
      ON a.id = b.id

I've admin permissions on the PC but can't access SSIS because Access is denied like below.
However the solution proposed below is not appropriate for me as I can't find DCOM User Group Membership on my Windows 8.1 machine.
Any suggestions how I can get around this?

http://www.mssqltips.com/sqlservertip/3086/how-to-resolve-ssis-access-denied-error-in-sql-server-management-studio/

You should open SQL Server Data Tools and create a new SSIS Project and new SSIS package inside it.

Could you explain why are you trying to connect to Intergration Services from SSMS? It is the place where you can see already existing Stored/Running packages