Importing CSV data into a user-defined SQL table (otherwise known as how to specify the table name)

I’m a total newbie to SQL Server but I’m starting to require skills in this area specifically for the purpose of manipulating and “pre-processing” data consumed by FIM. I have the “Inside SQL Server 2008 T-SQL Programming” and “SQL Server 2008 T-TSQL Fundamentals” books within reach, as well as an Internet connection, so I’m getting what I need done as quickly as possible. However I stumbled across something today that I thought I’d post, again for my own purposes as well as anyone really new to SQL, as I couldn’t find a direct answer to my question within a couple of minutes of searching the Internet.

Background

I need to take a CSV file and pull the data into a SQL table. This is very trivial and easily accomplished with the “Import and Export Data” wizard located within the SQL Server start menu folder or indeed from context-sensitive menus within SQL Server Management Studio (SSMS). My problem was the name of the CSV file was defined by a business process that I have no control over and I didn’t want to name the resultant table in my database with the name of the file, e.g. [dbo].[hrcsv] doesn’t work for me.

Before we get started a minor piece of background. On the “Select Source Tables and Views” page of the Import and Export Data wizard, within the Column Mappings you can click “Edit Mappings…”. This exposes the raw T-SQL via the “Edit SQL…” button. However if you click this button you’re faced with the following text:

You can customize the default CREATE TABLE statement. However, after you have customized the statement, you must manually maintain any subsequent changes to the column mappings by editing the statement.

Manually maintaining that statement means complexities in my design documents and more specifically the operations guide(s) I leave with the customer, so I’m not going down that route just yet. Furthermore if you choose “Drop and re-create destination table” that isn’t conveyed in the raw T-SQL, so is the wizard going to be trying to drop the name of the CSV yet execute T-SQL to create something different? It sure looks like as it tries to drop [dbo].[hrsvc] on first run which kills the job as I don’t have a table with that name but I do want my package to perform a drop and create on each run.

BDIS to the rescue

So I decided to use SQL Server Business Intelligence¬†Development Studio (BIDS). My starting point for this little adventure was the online article “Import Data From Comma Seperated Values .csv Flat File Into SQL Server Database Table using SQL Server Integration Services SSIS“. I followed the instructions as described by Eralper Yilmaz but deviated a little by choosing to use the ADO.NET provider over OLEDB as I’m under the impression that the .NET stuff is faster when dealing with SQL (could be wrong).

Note. The very first thing I did was drag the ADO NET Source from the “Data Flow Sources” toolbox section, which resulted in the error:

Cannot create connector. 
The destination component does not have any available inputs for use in creating a path. 

This is because this is an ADO NET *SOURCE* not an ADO NET *Destination*. Deleting the erroneous control, scrolling to the bottom and dragging ADO NET Destination from the “Data Flow Destinations” toolbox section fixed this problem. J

Deployment

I used this tip as my inspiration for deployment. Again I deviated from the instructions by opting to deploy the package into SQL Server.

Execution

Once the package has been deployed you obviously want to run it right? In my case I want it to run every morning -early. So I created a SQL Server Agent job that executes the SSIS package at 0530 GMT daily.

Wrap-up

If you want to perform some basic CSV to SQL data transformation but you want to use a nicely named table as opposed to the name of the CSV file then it is very easy to create an SSIS package using BDIS and then deploy that to SQL Server. BDIS gives you all the control you could ever want and then some (or at least it does to a novice like me) so don’t be afraid. It’s loads better than the wizard and what I’ve just written took me about twenty minutes, which included searching for and reading the info. available on the Internet.

I know this is a very simplistic example but this is impressively powerful and easy to use stuff. Armed with this info. I’m more than just a concern to SQL server databases. I’m a clear and present danger! J

Advertisements

About Paul Williams

IT consultant working for Microsoft specialising in Identity Management and Directory Services.
This entry was posted in SQL Server and tagged , , , , , , , , , . Bookmark the permalink.

4 Responses to Importing CSV data into a user-defined SQL table (otherwise known as how to specify the table name)

  1. Pingback: Dropping and recreating the destination table in an SSIS package | Yet another identity management blog

  2. Pingback: Invoking (running) SQL Server Agent Jobs using PowerShell | Yet another identity management blog

  3. Bel says:

    Hello.
    Can you please tell main difference of SSIS vs FIM?
    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s