It is a very frequent request to upload CSV file to database or Import CSV file into a database. I have previously written article how one can do this using T-SQL over here SQL SERVER – Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server.
One of the request which I quite often encounter is how I can do the same using SSIS package and automate it. Today we will understand the same using images.
First of all, let us create the table where we want to insert the CSV file.
Here is the sample table which I am creating where I will import my CSV.
CREATE TABLE [EmployeeImported]( [EmployeeID] [int] IDENTITY(1,1) NOT NULL, [ContactID] [int] NOT NULL, [ManagerID] [int] NULL, [Title] [varchar](100) NOT NULL, [MaritalStatus] [char](1) NOT NULL, [Gender] [char](1) NOT NULL, [HireDate] [datetime] NOT NULL ) ON [PRIMARY] GO
Now let us open SQL Server Business Intelligence studio. (Path: Start >All Programs > Microsoft Sql Server 2008 R2 > SQL Server Business Intelligence Development Studio. Select Integration Services Project. Create a new project and save it.
Click on Control Flow and drag Data Flow Task to the right side pan.
Double click on the Control Flow task.
It will take you to Data Flow pan.
Drag Flat File Source from Toolbox to Data Flow task pan.
Configure New Connection by clicking New.
Select the Path of the file and specify Text Qualifier. For me the text qualifier is comma(,).
Click on the columns and adjust OutputColumnWidth – match it with the width of your original data. If you do not know leave it as default (at 50).
Click on on following screen.
Now Select OLE DB Destination from right side Toolbox and drag to below the Flat File Source.
Put them near to each other ad demonstrated below.
Connect Green Arrow to Flat File Source with OLE DB Destination.
Double click on the OLE DB Destination and connect to the database and table created earlier in the code.
After configuring connection the mapping needs to be adjusted as well.
Now on the mappings tab connect both the size. I have not connected very first column as it is identify column for me.
Clicking OK will bring me to following screen.
Now click on F5 and it will execute the package in debug mode.
Do let me know your opinion about this article.
Reference: Pinal Dave (https://blog.sqlauthority.com)
127 Comments. Leave new
Hi Pinal,
I am new to SSIS.
Here is my question:
How can we ensure SSIS picks up the latest CSV File from the folder?
Hi
I would also like to know this as I am doing something similar.
I have an SSIS package set up which goes to an FTP site and downloads a csv file for me then brings it into SQL.
However each day I need to go in and change the filename from: filename_011020011_02102011 to filename_02102011_03102011
When in fact all I want it to do is pick up the lastest file each day.
Hi Nick,
Have you find a way to figure this out. Actually for doing so .. we need to use Script task component and has to write the C# or VB script inside the Script task component. I would like to have the script. Could you please send me the script.
Hi Pinal,
I am new to SSIS.
Here are my questions:
How can we ensure SSIS picks up the latest CSV File from the folder?
How would SSIS Package be scheduled?
Hi Saranya,
Am also looking for the solution for the same problem (both questions). Kindly reply if you have any idea to do the same. Thanks in advance.
Hi Pinal
I have a .csv file which needs to be inserted in a column with datatype varchar(max). I am really new to import and export. how can i do this? Any type of help would be really appreciated.
Thanks
You can use bcp or bulk insert for this
Image in example above does not work
Hello Pinal Dave,
I have clearly understood the process of how we can upload a CSV file to a table in a database (sql server 2008 ) using SSIS package.
Although I want to execute the package from stored procedures.
I got some code which allows me to execute the package which involve upload from excel to database table through Stored Procedure.(which is working)
and using the same code I am not able execute package which involve .csv to database table upload.
–Step -1
USE master
GO
EXEC sp_configure ‘show advanced options’, 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure ‘xp_cmdshell’, 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure ‘show advanced options’, 0
GO
— Step 2
DECLARE @returncode int
EXEC @returncode = xp_cmdshell ‘dtexec /f “c:\your path\package.dtsx”‘
Pleas reply me ASAP.
Thanking You.
Hi Pinal Dave,
Like to read different posts of this website. It is a very useful website for getting solutions to various problems.
But i am having a problem with this particular .csv file
I have a .csv file which has a data in first row and then the headers for columns below it. Example
Company Name = “ABC DEF”
Then comes all the columns with headers. So if i want to parse this company name to a column in same table where i am going to pass all the remaining columns from this .csv file. How do i do that using SSIS?
Can you help me on this?
Thanks
Please provide us more input and expected result. You can anyway parse the informations and load data into respective columns
Hi Pinal,
I have the flat file source like this:
“email”,”fname”,”lname”,”address1″,”address2″,”city”,”state”,”zip”,”phone1″,”ip”,”url”,”dts”
“xyz@yahoo.com”,”test”,”test2″,”814 42nd st”,”NULL”,”beach”,”FL”,”33407″,”5618424410″,”65.11.166.48″,”xyz.COM”,”2010-10-05 10:36:00″
“yyyyyy@hotmail.com”,”john”,”test”,”7243 thoreau cir”,”NULL”,”atlanta”,”GA”,”30349″,””,”74.176.149.4″,”newsite.COM”,”2010-10-05 09:33:00″
When we insert the data to the table it will be added with double quotes (“), please give your suggestion how to resolve it.
Thanks
Subash
Hi Pinal,
Now I have added double quotes as text qualifier and able to get the data. Thanks for your good article.
Subash
how to upload excel file into database using ssis
Hi Pinal
Thanks for this solution. My client however requires that I write a procedure to import data with any number of fields. The Import doesnt care how it is formed. This requires that the table gets created from the data rather than pre-empted by the programmer. This is a problem if a table is required first. How can i implement this? A dynamic way you could say.
Thanks
Jude
Hello Everyone!
First of all, I’m impressed from the level of the article. Simply – a genious at work!
My question:
I’ve loaded already 350 CSV files into SQL server 2008 using the SSIS package (with loop container), but now I have to be able to read every day the current log of the day, formatted CSV, but updating every minute.
What I need is a way to connect to the ‘live’ CSV file and every new line written to it – to get it into SQL server 2008 (using some convertions, etc., like I did in SSIS).
Is there a way to do this (with SSIS or any other way)? Or I’ll just have to wait and get the full file after midnight (when the log file is rotated)?
Many many thanks for a good answer!
I’m still looking for a solution to my problem.
I see many questions after mine got an answer, and – for some reason – My question left alone.
Please…
Are you open to using PowerShell to parse your CSV to fetch new records? Are you familiar with PowerShell? In SQL server 2008 and greater you can trigger PwerShell scripts as a step within a SSA job and follow it up on the next step with an SSIS job to load the new records.
Marcus (and others),
I’ll do whatever needed to be able to read those live files.
I don’t know about PowerShell yet, but if you’ll tell me that’s the correct way to do it – I’ll do it.
Looking forward hearing a solution,
Offer.
Dear everybody.
I’m still waiting for a solution to load current data from live dynamic CSV file (mail server log file).
How can I make the connection to it, and read it line by line whenever a line is added?
Thanks!
Hi,
I’m looking for the similar solution. Please let me know if you able to find it.
Thanx..
Chirag
HI Pinal
The article is very helpful.However I have another challenge. If the data already entered in SQL database comes again in the flat or csv file, the sql database will get duplicate enteris. I can make the coulmn as primary, however that will generate the error and the pkg will fail. Is there a process i can check in the sql database before importing into sql. Kindly suggest how can i achieve this through SSIS.
Regards
Pranshu
Import data into staging table and remove duplicates and move this to target table
Hi,
Thanks for the nice article. I had some developers create a SQL Server 2008 based application for me. However, somewhere in the transformation of the data, some large numbers get rounded. They have looked and looked but can’t find where the rounding is occuring. Has anyone else experienced this using SSIS?
Thanks,
Ben
Hi,
Thanks for these article. But i want another article i have 200 flat files .
I want to create tables in SQL SERVER database with each FLAT file with different tables means(200 tables ) using SSIS please respond me my
EMAIL ID IS : [email removed]
HI! Dave,
If the flat files contains datetime column with dd/mm/yyyy hh:mm:ss mis format how can i set this to mm/dd/yyyy format and import, i am always having problems with the date formats, it always swaps the date to month and the months to date while importing. how does this been corrected in ssis as per your above steps in sql 2008
Read about SET DATEFORMAT in sql server help file.
You can do
set dateformat dmy
and do the import
i have 5 csv file in a folder. that i want to transfer to sql server table. i did that but in sql server table there is another field called csvfilename, means once i will put the dataflow task in the foor loop container, the data will process and simultaneously the name of the csv file will go to that table.like wise 5 csv will go to sql table with the CSV filename .
pinal, or anyone else can you help me on this. its very urgent.
what i am able to do-
i am able to move the content from all the csv file to one sql server table
what i am not able to do-
the csv file name is not coming in sql server table. everywhere it is coming as “null”, because the default value is null. i am not able to pass the file name from foor loop container.
I am following this simple example, and it is not working. I setup a Data Flow Task on the Control Flow tab, and a Flat File Source and OLE DB Destination on the Data Flow tab. The problem is that I cannot find my tables in SQL, which are clearly there. I am anle to use a query to define the destination…
SELECT TOP (1000) keynoun, abb
FROM EngineeringDocCon.dbo.tblKeyNoun
…and I am able to map the columns
I’m getting Valucadion errors about an inability to convert between unicode and non-unicode string types. Both destination fileds are of type nchar.
A little discouraged that the examples are not working, as I believe I am doing something really simple…
Thank you in advance.
Hi Pinalbhai,
I am working as BI Developer today I came up with the new requirements that I need to create dynamic excel file with everyday date (like ‘Reports_02_09_2012.xls’) on our network drive. Pl. can you help me how to create a dynamic excel file? the data source is SQLServer Table and destination is .xls file. PLssssssssss
Excellent. Easy to follow screen shots. Worked first time.
Hi Pinal,
I did the same thing in creating an SSIS package for importing data from a flat file. But this had the confusing and hilarious effect of interpreting dates inconsistently. Any dates where the day was greater than the 12th of the month would be correctly interpreted as dd/mm/yyyy but anything less than that would be backwards.
So 01/21/2011 would be correctly read as the 21st of Jan 2011.
But 01/06/2008 would happily be interpreted as 1st of June 2011.
Thanks Deepak for such a good solution. One thing i just want to ask that how we can schedule data loading from a particular text file.