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
I have a folder which has multiple files and their names are dates (e.g. 04-08-2014.CSV,04-09-2014.CSV) and in my SSIS package I want to load the most recent one by filename. How can I achieve it?
I am currently using a hardcoded filename in my Connection Manager.
My SO question is here:
All help is greatly appreciated.
Thanks.
Is it possible to get files from a FTP Server in stead of a local source? I really need some steps in how to do this.
hi all,
i am trying to load dynamically from csv files to sql database,
i face this error can any one help me?
Error: System.Exception: Failed to initialize ‘Write data into table’ metadata. Exception from HRESULT: 0xC02020E8 —> System.Runtime.InteropServices.COMException (0xC02020E8): Exception from HRESULT: 0xC02020E8
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
at CozyRoc.SqlServer.SSIS.DataFlowTaskPlus10.DynamicDbDestination.Setup()
— End of inner exception stack trace —
at CozyRoc.SqlServer.SSIS.DataFlowTaskPlus10.DynamicDbDestination.Setup()
at CozyRoc.SqlServer.SSIS.DataFlowTaskPlus10.DynamicSetup._Setup(Boolean thunk)
at CozyRoc.SqlServer.SSIS.DataFlowTaskPlus10.Execute(IDTSConnections100 connections, IDTSVariableDispenser100 variableDispenser, IDTSComponentEvents100 events, IDTSLogging100 log, Object transaction)
Regards,
venu
thanks a lot , it works fine
Hi all,
I have a 7Years data in db. i need to stored each and every month data into FlatFile, and next flatfile will be generate automatically. By using SSIS. It will take all updates.
Hi,
In my case I would like to import 2 csv files with different names and the latest dropped file for each one of them, the validation and table to be insert it into are the same. How would I go about doing so? Thanks!
Very helpful and images do help. Thank you for posting this SSIS tutorial.
Glad it help you GT!
Hi Pinal,
Keep updating for the ssis packages….
Matish – you mean more blogs on SSIS packages?
Pinal,
I feel I can really use your valuable help. I am trying to import this csv file into my SQL destination table. I have my flat file source component and OLEDB destination component for my SQL table. The error is follows:
“SFH OPS File [43] Error: The column delimiter for column “Scheduler_Full_Name” was not found.
Even though the delimiter is there, it says not found. Since this is the last column i have it set to CRLF.
I can post the .dtsx and the flat file for further discussion.
Thanks,
Fahad Zia
Provide simple repro.
Hi Pinal,
How can i load a single file with different data sets, like 3 different table structure which are separated by 1 blank row into 3 tables using SSIS. I know i have to use script task or component. Can you help on this
If any other suggestions…you are most welcome
Hi, I am trying to import data from csv file to SQL Server using SSIS, however in one column there are more that 20000 character in it, I unable to load it due to columns size 8000 character, even I have set nvarchar(max) it is not working, please let me know what is the way to import that column data. if anybody come across this scenario please let me know and appreciate it. Thanks
What if we have embedded text qualifier as shown bellow. In BI development tool 2008 if we assign ” as text qualifier it works fine and gets (Mailing “Inactief”) as description correctly. However in Visual studio data tool 2012 and 2013 it does not work (Error: The column delimiter for column “Description” was not found.). The easiest solution seems to be setting TextQualified property in Advance Editor to False and then delete ” at the beginning and end of the text using a script or conversion task. Why it does not work in 2012 and 2013 as it does in 2008?
ID~Description~Customer_ID
1~”Mailing “Inactief””~3
Thanks a ton Dave.. This helped me lot in my automation activities.
This a good example, how would I modify it to have a variable as a file name? I have filenames which change as the date changes daily. Could I have a pre-process that builds the file name and then the flat file gets referenced and processed into my table? How would I do that?
Also, once the project works how do I deploy it to our SQl server and get it scheduled?
You can create package and deploy it in SSIS. Also, you can programmatically change the name.
Where do I programically change the file name?
When I need something critical about sql server, this blog always appears at google result.. Thank You, master Dave
A great resource, i was able to create my first SSIS package using this resource, it has created a bit of a buzz in the team and are now looking to use SSIS for more flat file conversions/imports/exports
Many Thanks
It’s helpful ! Thanks
When inserting data from a CSV file into my SQL database using a SSIS package, I lose data; 200 rows out of 2 million, do you have any idea why this is? I was wondering if this was a SQL database size issue but the data doesn’t look like it is cutting/truncating the last chunk of information out. Are there any limitations with using this approach on a large data set with over 2m rows?
Hi
I nedd to load the data into db and i am using a txt file.what should be the derived column expression should i used show that the data with negaive value at the end of the column data can be insert easily.
secnario
Suppose for a clomun ABC the value in text file is 12345+ and i need to load the data into db as 123.45+, using DT_NUMERIC() i can convet this a numeric fild, but i am not getting the + sign in the db.
I guess a suitable derive column can help me in doing this.
I have used a derived column that can insert signd at the beginning but i not able to do when at the end.
It might be the case of stored proc,try to create two or more update flow(split the data or column into half ) in your package and then try.I was facing the same issue when i was loading the same amount of data.
Can you please while we import how can we get null values in table against blank values in csv or excel file?