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
Thanks for your great articles, you are a most valuable expert
Hi, pinal!
Have you ever used a custom delimiter in CSV files?
I used bulk load task and I couldn’t use delimiter for example ASCII(7)… SSIS have failed on compilation…
Best regards,
Eugene
Eugene,
I’ve only used commas, pipes and tabs as main delimiters myself, but you should be able to split the columns up using the derived column transformation.
I find that derived column transformation to be a bit of a pain to use though so what i normally do is just load the data into a scratch table with a varchar (or nvarchar) field that’s large enough to fit the entire row, or section that contains the custom delimiter, then write a t-sql procedure to split it out into columns and load to the final table.
Zach
Pinal,
Nice primer on SSIS. I should point out though that you mention your text qualifier is a comma. This is not the case, and generally wont be the case for a CSV file.
In setting up the flat file connection there is a “columns” section where you specify the column delimiter:
The text qualifier you mention is used when you have a text field, like notes and such, that might contain commas and the qualifier prevents the import process from dividing that field up every time it sees a comma. Often if you’re exporting from Access this is a double quote (“).
Zach
Good article, i have had better luck using CSV files to import data as compared to excel files. In excel i do run into the issue of coulmn having unicode types and i have had to use the Data Conversion task within the data flow to covert unicode to nonunicode and then import the data into sql server.
Hi Pinal Dave,
I’m a young DBA just getting started and I LOVE articles like these. Thank you so much for taking the time to share your knowledge with us! As I mature I hope to someday “give back” in a similar fashion.
Thanks,
Tom
Hi Pinal,
Lots of People don’t understand the MS BI (SSIS/SSAS/SSRS)
It’s been nice article for new SSIS people.
How SSIS Works.
Thanks
Suhas
Hi Pinal,
Your article is good for beginning.Here are some points:
When you are using CSV file or text file and your file have Column Names in first Data row then check the check box says “Column Names in First Data Row”, It will automatically takes Column Name as the CSV or Text file has. If not checked or your file don’t have column names then you have to follow the process which you defined in your BLOG.
I would like to ask one question what is the use of “,” Comma in Text Qualifier in your article.
If the columns are seperated by comma, you need to use comma there
Sorry madhivan, but that is incorrect, the column delimiter is the comma, but the text qualifier is useful for splitting out text strings that contain the delimiter. for example:
1,2,3,4,”aae,sdef”,5,7,4
the text qualifier ” means that the string aae,sdef would be treated as one column
Hi Madhivanan,
As Barny says,Text Qualifier is used for a different purpose. Suppose you CSV has a column Address and in this You have a address like “Street no.5,Delhi,1″. Now when you use Comma ad a Column delimiter then SSIS will delimit Address into three Columns, but It must be read into a single column.
For this we take ” in Text Qualifier and SSIS will read Address (Street no.5,Delhi,1) into a single column.
i dont know..where he inserted the csv file
Hi Pinal,
can you explain me how to set the properties for TEXT Qualifier and Format.
could you plz explain me with an example.
in fomat we have—->Delimited,fixed width, ragged right.
what are the cases that we go for above three.
Thanks in advance,
Raghavendra.
i have once csv file like this
name sal
____ ___
sarath 20000
raja 30000
sara,d 12121
but not getting proper way of result
hi pinal,
this is ok but if i have 50 csv file with diffrent name can i use the same same ssis package for all files.
Hi Sanjay…
No matter how many CSV file you have you can import them by using SSIS provided the Structure of CSVs should be same.
Example: CSV1
ID,Name
1,A
CSV2
ID,Nam
2,B
You just have to use the ForEachLoop Container and in Collection use “Foreach File Enumerator” specify the folder where all your CSV files are and in Files use *.CSV and Retrieve file name as Fully Qualified…
And claick OK after that Drag A Data Flow Task inside the For Each Loop Container and use the CSVs as you wish..
Hi Pinal,
Can I create/extract master detail tables and data with SSIS project?, I am new in SSIS, so will you please explain How can I extract data from csv in master detail table?
ie.
EmpId Emp_name designation
1 abc1 IT Manager
2 abc2 Sr. Software Developer
3 abc3 Sr. Software Developer
4 abc4 Sr. Software Developer
5 abc5 Software Developer
6 abc6 Software Developer
7 abc7 Web Developer
8 abc8 Web Developer
9 abc9 Web Developer
I want to create two table from single table like
EmpId Emp_name designationId
1 abc1 1
2 abc2 2
3 abc3 2
4 abc4 2
5 abc5 3
6 abc6 3
7 abc7 4
8 abc8 4
9 abc9 4
and another one is
DesignationId Designation
1 IT Manager
2 Sr. Software Developer
3 Software Developer
4 Web Developer
Hi Harish,
Yes, you can create the master detail table data with SSIS. In your given data you can achieve this by follow the below steps:
In SQL: Make Three tables
1.For store Designation and DesignationID calls Designation.
2.For Store EmpId,Emp_name and designationId Calls Employees.
3.Staging table for storing the data as it is in the example data given by you.
EG:
CREATE TABLE Stg_Employee
(
EmpID INT,
Emp_Name VARCHAR(100),
Designation VARCHAR(100)
)
IN SSIS:
1.First Create a SSIS Package.
2. Drag a DataFlow Task on Control Flow Surface and Double click on this and take a FlatFile Source and OLEDB Destination.
3. Configure to both FlatFile Source and OLEDB Destination. FlatFile Soure to your main data and OLEDB Destination to the Staging table that we have created in our SQL part.
3. Map the appropriate Column names.
4. Drag Another DataFlow Task on Control Flow add the Precedence Constraint of previous DataFlow Task to this one and
Double Click on it.
5. Take a OLEDB Source and OLEDB Destination and in OLEDB Source choose SQL Command as Data Access Mode.
6. Write the query which should be like
SELECT DISTINCT Designation,DENSE_RANK()OVER(ORDER BY Designation) AS DesignationId FROM Stg_Employee
7. Map the columns of this query to the table which you made for store Designation and DesignationID.(Suppose the name of this table is Designation)
8. This is the trickiest part of this process for this again a DataFlow Task on Control Flow and join the Previous Precedence Constraint with it.
9. Double click on it and take a OLEDB Source LookUP Transformation and OLEDB Destination.
10.Take Stg_Employee as a source and take Designation table in LookUP transformation and match the Designation of stg_Employees
to Designation of Designation table and on the bases of match records take DesignationID.
11. In OLEDB Destination take Employees Table as A Source. Now you have Emp_ID,Emp_Name which will come from stg_Employees table and DesignationID which will come from Designation Table.
Click OK and Run The SSIS packge…
Note: Since we are not dealing the Errors in this process this may happen that due to some mismatch records in LookUP transformation the Package Fail.
And Please let me know if you have any more queries related to this.
Hi Deepak Sharma,
Thank you very much for nice solution. This solution works for me. I want to learn SSIS, so can you please suggest more blog and books on SSIS?
Hi Haresh,
I would suggest you to go to the below article on Technet
https://docs.microsoft.com/en-us/sql/integration-services/ssis-how-to-create-an-etl-package?view=sql-server-2017
Sure this link will help you.
Hi Pinal, This is a good introduction on how to import flat files into tables. In my case, I need to import three files having different number of data elements into three tables having the same corresponding number of columns. My questions are:
1) Which is the better and recommended method to use? – Using Bulk Insert command in a stored procedure OR using SSIS?
2) The file location, database server and database name are different for development, system integration, user testing and production environments. If I want to use SSIS to do the file import, how can I create one SSIS package that will dynamically get the file location, server name and db name from some configuration file and execute in the appropriate environment?
Hi Mallikarjun,
In my opinion, You can better achieve this by using SSIS.
For making the SSIS connections dynamic use SSIS configuration.
Just make 4 configuration files for each and every environment ands use them in your SSIS package for this do right click on control flow and enable Package Configuration and make a configuration file.
For more information do some googling on configuration file in SSIS.
Thanks
Pinal, two more questions after my previous email.
1) How do we truncate table before import from the file? I did not find any option in OLE DB connection setup to do that.
2) Can we import of 3 files having different number of fields in the same SSIS package?
Hi Mallikarjun,
1. Use Execute SQL Task on Control Flow of SSIS and make the configure it with OLEDB connection and in query use the TRUNCATE table statement.
2. For this you can use three Data Flow Tasks on Control Flow Tab and when you do double click on Data Flow task choose your Source,Transformation and Destination to configure them
Le me know if you have any more question on this topic.
HI Pinal
I want to import image file using ssis package.
Can you please help me how to do it ?
Hi Abhishek,
Yes you can use Import Column and Export Column for dealing BLOB and Images in SSIS. The process of import column transformation is complicated. You can follow the below LINK for the reference and let me know if you have more question on the same topic.
.
@Ramdas
Hi I believe u said that u used data conversion tool.
I am using the same tool but still have the error saying cant convert from unicode to non-unicode where my source data type is DT-STR
please reply me with a solution
HI Pinal
I am a big fan of yours and really value your articles. I use DTS to import flat files for our company and using temp tables to hold the data before sending it via standard interface that company has written to filter the data. My issue is here that you have not mentioned somthing to filter data such as trasformations used in ssis. I very much like to see thease trnsformation conditional split, data conversion etc. in action. Hope I am not asking a lot.
Thanks
Nilanka Sooriyabandara
Hello, I am very new in SSIS,
I want to import many txt files into sql many tables,
the table name is the same with the txt file name,
like this: file P.txt —- table P
Can I do this in one package?
How to do this?
Hi Pinal,
I am not able to find the solution for my problem.
here is my question :-
i have a data flow task to copy a csv file into a table. the file has 84 columns(8145 rows). when i do run my dft it runs fine(all green) .. I can see the data in preview and in data viewer too. I can see the data in data flow also like 1113 rows, 3000 rows …. and finally 8145 rows.
but at end it says
[DTS.Pipeline] Information: “component “OLE DB Destination” (9)” wrote 0 rows.
FYI
I have few columns with comma separated value in my CSV file. tht might be creating problems.