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.
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
I used this tip as my inspiration for deployment. Again I deviated from the instructions by opting to deploy the package into SQL Server.
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.
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