06 Import csv file to sql server using SSIS | Load CSV File in SSIS 

Thanks good to know that you liked the video.

Thanks for your comment. I am just a learner to SSIS.

Hii i got error like please provide .net provider in ole db destination?

Glad you liked it.

Thanks for your comment, good to know that you liked the video.

Thank you so much inder kaur.

Thanks for you comment.

You are most welcome Sir.

You are welcome.

Its nice to hear from you.

Thanks for your suggestion, will try to implement it.

I am sorry for the late reply on it, you can use a data conversion transformation to change the data type of a field. An example is given below ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-aQJCky2qfCs.html

Its nice to hear that it helped you Aishwarya.

Thanks for your comment.

I am not sure how this can be done. Maybe we can need to write some code in C# and read and generate the file in correct format using C#.

Actually flat file connection manager can be used to read a csv file but yeah you can not use the OLE DB destination to write it to an AWS server. Unfortunately I don't have any experience working with AWS server. If you can get the code to populate data to AWS server using C# then you can use that code inside script task in an SSIS package.

Did you mapped the columns from source to destination correctly while configuring the OLE DB Destination ?

ha ha. Good suggestion. Recently I made a video on the same topic but it was in Hindi. Will try to make a new one in English as well.

Thanks for your comment.

Did you installed SSIS along with Visual Studio and SSDT on your machine ? ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-0NozIVoNyY4.html

Watch this video in which I shown how to read data from excel file ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-12_JoGTTuH8.html And then take a look at this video to understand how to write the data to a csv file. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-n94-pFXP8eA.html

Thank you so much

You can use the foreach loop container to load the files if their name is changing but their header information is same. header should not change. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-BjpaSxMZMxs.html

Oh, I think you missed something. Check your destination sql table and check your connection. Thanks.

How to create dynamic connection manager in ssis ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-QDSRzGxLe1g.html How to make folder path and file name dynamic, watch this video ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Y69bmI455uA.html

Sure Smriti, will load such kind of video in future.

In the Flat file destination, if you right click on it you will find a check box ticked by default, over write data, you need to un check the checkbox. Then it won't over write any data. Sorry for the late reply.

Can you please check this one ? stackoverflow.com/questions/44359741/ssis-error-delimiter-for-column-columnx-is-not-found

Sorry Man, I never fetched data from Oracle, Netija and cms so I don't know how it will be done. I know from Oracle you can get the data without using the 3rd party components, you should be able to find some videos to do that, however for fetching data from other 2 sources, you might need to use 3rd party components from cozyroc, kingswaysoft and from zappysys. Thanks.

I just just created a database with random name as SSIS. You can create a database with any name and then you can import some test data into it, you can download test data from this site and import it to sql tables www.mockaroo.com/

It seems like the issue is with installation, check if machine has 64 bit processor or 32 bit processor and then redo the installation of SQL Server along with SQL Server Integration services accordingly.

Varchar data type can store almost all data type values like numeric, date and string thus varchar can work without changing it to specific type.

@@learnssis thank you for the reply. It would be great if you could explain about advance editor of flat file source and why to use it

@@saikiranp.r6966 Sure, I will try to explain it in future videos. But currently I am busy with some projects so not sure when I will be able to resume the video recording.

If the layout of both txt and csv is same and just the extension is different then you can use the same connection manager, and if the layout of both csv and txt is different then you would need to create different connection managers for each of them.

Yeah it is same as Bulk insert task. However this is the best approach to import the file as it gives the highest level of control on the data that you are importing. For example using Bulk insert task, you can not change the data before writing to the sql server table, it will be read from csv file and will be inserted to sql server table, you don't have any control to change the data on the fly, while if you are using data flow task then you have full control on the data, you can alter it, like you can remove leading or trailing spaces, you can replace a string, you can do the data type conversion, you can uppercase or lowercase data using several data transformations like derived column transformation, alternatively you can use lookup transformation in between and can compare the data with the existing data in a sql server table and only insert new records from csv file to sql server table, so you can do a lot of things in data flow task which are not supported in bulk insert task.

@@learnssis Got it Bhai .. Shukria. Ye concept to ni bhoolta ab kabhi .. 🔥🔥🔥

@@user-xz9bz8nj9rGlad you got it.

Hi Omar Mansoor, thanks for your question, I tried to import the file that you gave using flat file connection manager in SSIS but it does not work, now it seems like we would need to write C# script to load the data from csv file into sql table. So far I don't have a video on this topic on my channel, bur for now you can check this video and try to import the csv file into sql server table using C# script ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Zg1aZpoS0I8.html

@@learnssis thanks for the prompt reply sir, can you share a bit of code to read these kind of CSV please

@@muhammadomarmansoor2067 The link I shared, if you watch the video, the code is given there, that's why I shared that link. Thanks.

@@learnssis Thanks for your quick response, I just want to know that can we can get the data table out from the script task and then use the data flow task to insert it to the DB?

Use a data conversion transformation if you are getting the error of unicode conversion ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-aQJCky2qfCs.html

Sorry I never worked on any Unix platform, so don't have any idea about it.

No, we can only check from SQL Server if the data got loaded or not. If you want to check only from Visual Studio, then you can try to fetch data from sql server using a data flow task, and in the data flow task you can take an OLE DB Source and try to fetch the data from the same table, if data will be populated to sql server table then it will show you the data.

Please share your email id.

@@learnssis my email is abrahammbombaa@gmail.com

If we think that comma can be present in flat file, then we should try to enclose the columns in double quotes. So while generating the flat file we can have double quote as text qualifier.

@@learnssis The commas within the fields will mislead my SSIS package to understand that file row has more columns than previously said! How to resolve this? Eg: Name,Amount,Address Me,50,000,My Home,India you,53,300,Your Home,Where here only 3 columns exist but SSIS assumes all commas used to separate fields;Actually not all.Amount Column and Address Column have extra commas. can you please upload a video on this?

​@@nadeemahmed8803 Ask the creator of the file to fix such bad rows, there is no built in way to fix this. One possible way might be that you read all data in a single column from source and then in transformation use a script component and then check the number of commas for each row, and remove the comma or column if more columns are found but this is not a simple way to do. Thanks.

@@learnssis Thanks a lot, I just wanted to know if there is any built-in way to fix this but now my doubts are cleared. I resolved this issue by using C# code in the script task.

@@nadeemahmed8803 Great good to know that.

It means that file will really be opened by some process. Try to rename the file if you can't rename it then log off from the machine and log in again. Thanks.

Hi, Currently I am very much occupied with some other projects, but in future I am planning to upload on SSAS as well.

Thanks so much.

Thank you for your suggestion, yes you are right it was one of my very basic video which I uploaded without editing it, I made it in one go. I will take care of your suggestion for future videos, thanks.

At least give him credit for trying. There’s a more constructive way to ask for additional clarity

క్షమించండి నాకు తెలుగు రాదు

Kṣamin̄caṇḍi nāku telugu rādu