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
Hello Sir,
I m Raghavendra…Can you tell me how to dump 4 files of different strucutured data to 9 tables..???
I hope that you will reply soon..
I am getting package validation error message box stating Error at Data Flow Task [SSIS.Pipeline]: input column “” (239) has lineage ID 203 that was not previously used in the Data Flow Task.
There are few more exception messages being written like these! How to get over it?
nice explanation. thanks.
Hi der,
I am transformning txt n csv files into database through SQL plus/loader.
I have file of transections where there are more than 1 columns holds negactve values. Now the problem is that in txt file a negative sign occurs on the right side of the number. .So I have multiply certain colums contents with (-1) it works for 1 colum, but what can I do with more than 1 colums containing negatives at the end.
FOR EXAMPLE:
|Jusq. 0 |Jusq. 30 |Jusq. 60 |Jusq. 90 |Jusq. 180 |
|760.288 – | 653.612 | 145.456- | 117.814- | 170.807 |
|760.288 | 653.612-| 145.456 | 117.814- | 170.807- |
|760.288 – | 653.612 | 145.456 -| 117.814- | 170.807- |
|760.288- | 653.612-| 145.456 | 117.814 | 170.807 |
|760.288 | 653.612-| 145.456 | 117.814- | 170.807- |
Hello Pinal,
I need your help in Import 4GB .CSV file with 340 colums.
Its not allow me to import it
Thanks,
AMIT NAKOOM
Did you get any error?
Hi, I want same with mulitiple files to load into database using Foreach loop container.
Hi Pinal,
I upload csv file but it execute operating system error… What can i do for this error. Any updation for OS. I am using XP sp1 .
Thanks,
Pandiyaraj
Hi Everybody,
Could you please suggest me how can I insert data from one flat file to different tables in sql server Database using SSIS or SQL code?
TB1*AP*ENH5895*86597*20120620~
TB2*395*000001*0052959874~
TB3*0569*00*E042165578*0034603409~
TB1*AP*ENH5895*86597*00501025836~
TB2*395*000001*00529265684~
I have a data like this format in flat file. I need to insert it into different 3 tables in sql server 2008 , named TB1, TB2 and TB3. here in raw file : fieldterminator = ‘*’ and rowterminator = ‘~’
1st and 4th rows records should be inserted in TB1 TABLE.
2nd and 5th rows records should be inserted in TB2 table
and 3rd row’s records should be inserted in TB3 table.
Thanks,
Raj
How can I load the latest csv file in a folder into the database table.I have 5 csv files with different modified times….I want to load the latest file.
Thanks
Do the files have date values as part of the names?
Hi Madhivanan,
Am also looking for a solution for the same problem. Kindly reply if you have any solution for this. Thanks in advance.
Just got a doubt, if the file name changes everyday then what would be the mechanism. As in this case the file name is CSVfile.txt. Lets put an example. The file name would be CSVfile_20121008.txt (where 20121008 is today’s date). And this value will be changing regularly and the file would be placed in the same location(so the old file is deleted and only the new file as of current date is present). Is there any process for the same with keeping with the approach same of importing the data.
thanks pinal ..
Hi Pinal,
I’ve worked with and it worked great for a while. Now I have an issue. Somewhere in the csv file there is something bad. The file has about 200k lines in it, so manually searching for the error is not possible (although I’ve done that anyway). I seem to have no issues when reading the file into Excel or Access (for trying to find the error). Do you have an addition to the solution show, that enables me to exactly pinpoint the line that is causing the error?. By the way, I have tried to redirect the line containing the error to an alternative table. My (probably crude) attempt was not successfull
Thanks! great articles…
Great article! I am just learning how to use SSIS (today) and your article is very helpful. I am wondering if there is a way to load a CSV file and change all blank strings to NULL across all columns without writing it out column by column?
Hi Pinal, Great article to begin with, i am working in SSIS and am not sure on backward compatibility stuff supported in 2008, i have made a package with 97-2003 as source but if the version of excel is 3.0/4.0, would the behaviour of ssis change, do we need to explicitly handle this and also if we wanna make this .xlsx compatible what would be a good practice?
Thanks
Jinesh Rajesh Parekh
Nice example
Does SSIS support N to N column mappings as DTS did ? I do not see it graphically as I did with DTS
N to N column mappings as explained here (In DTS)
https://www.microsoft.com/en-us/download/details.aspx?id=51958
On column6, wat delimiter did u use? I have a csv file, all separated by commas. But mine seems to have A problem with the last column. Kept saying row and column cannot have same delimiter. I’ll greatly appreciate it if I can be sent the source code. Thanks
Hello,
How to import csv file from gzip file using gzip stream? I would not like extract import files because it’s very very large file > 20 GB and wouldn’t like spent time for extract and save csv file to disk.