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

Поделиться
HTML-код
  • Опубликовано: 29 окт 2024
  • Import csv file to sql server using SSIS | Load CSV File in SSIS
    Download the file\script used in the Video from below link
    drive.google.c...
    SSIS Tutorials: • SSIS Tutorials
    SSIS real time scenarios examples: • SSIS real time scenari...
    SSIS Interview questions and answers: • SSIS Interview questio...
    How do I import a CSV file into SSIS?
    How do I automatically import a CSV file into SQL Server?
    How do I import data from multiple CSV to SQL using SSIS and Visual Studio?
    How do I import a CSV file into data?
    How do I convert a CSV file to SQL?
    How do I import a CSV file into SQL Developer?
    How do I import a CSV file into SQL Server using SSIS?
    How do I import a CSV file into SQL Server?
    How do I import a CSV file into Visual Studio?
    If you have any questions or suggestions do write to me on "aqil33@gmail.com"

Комментарии • 106

  • @kaurinder9689
    @kaurinder9689 Год назад +1

    Thank you sir for this video. It helps me alot thank you very much.stay safe sir stay happy

    • @learnssis
      @learnssis  Год назад

      Thank you so much inder kaur.

  • @nickt423
    @nickt423 3 года назад +1

    Aqil, bro you are a boss! Thanks for the tutorials

    • @learnssis
      @learnssis  3 года назад

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

    • @jaganlesner3901
      @jaganlesner3901 5 месяцев назад

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

  • @emmanuelawa9534
    @emmanuelawa9534 3 года назад

    This is a great stuff you're doing. My only problem is that you're fast. But thanks for this free information

    • @learnssis
      @learnssis  3 года назад

      Thanks for your suggestion, will try to implement it.

  • @sanketchaudhari3458
    @sanketchaudhari3458 2 года назад

    Deep explaination thank you sir

    • @learnssis
      @learnssis  2 года назад

      You are most welcome Sir.

  • @medalatnejad3311
    @medalatnejad3311 7 месяцев назад +1

    Aqil, thank you for valueable videos. I have a problem.could you please help me.I have an Excel file with a column that include commas, and I want to convert it to CSV format . how can handle these commas properly?

    • @learnssis
      @learnssis  7 месяцев назад

      Watch this video in which I shown how to read data from excel file
      ruclips.net/video/12_JoGTTuH8/видео.html
      And then take a look at this video to understand how to write the data to a csv file.
      ruclips.net/video/n94-pFXP8eA/видео.html

    • @medalatnejad3311
      @medalatnejad3311 7 месяцев назад

      Thank you so much

  • @Roxie-423
    @Roxie-423 Год назад

    Thank you so much for this informative video! Is there a way to import a CSV file on a different server such as an AWS server? In that case I assume the flat file connection cannot be used?

    • @learnssis
      @learnssis  Год назад

      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.

  • @NaveenKumar-fq4sb
    @NaveenKumar-fq4sb Год назад

    ofter loading course in ssis sucessfully but in sql server if select * from course ,table giving result as NULL valus,what is reason pls let me know

    • @learnssis
      @learnssis  Год назад

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

  • @vishalsaha2341
    @vishalsaha2341 2 года назад

    Hi Akhil, There is no SSIS database in my SSMS.
    How to fix this ?
    Waiting for your help .

    • @learnssis
      @learnssis  2 года назад

      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/

  • @SabiraWoltze
    @SabiraWoltze Год назад

    Hello, I am stuck with an import issue and was hoping someone here knows how to solve for it. We are getting csv file in different encoding 1252 and 65001 and if i use flat file connection i can only pick one or other encoding. Is there a way to pre-process all incoming csv files and save them in standard encoding before it gets loaded to the SQL server DB?

    • @learnssis
      @learnssis  Год назад

      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#.

  • @steverowden7125
    @steverowden7125 4 года назад

    Hi - very helpful video but having one issue. After I created and configured the connections and mapping getting an error that you cannot covert a Unicode to a non-Unicode column. My source is a .csv and my table column is varchar(50). I’ve researched and haven’t found a solution. Any suggestions? Thanks

    • @learnssis
      @learnssis  4 года назад

      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
      ruclips.net/video/aQJCky2qfCs/видео.html

  • @johnconnor9787
    @johnconnor9787 5 лет назад +1

    Thank you! It was very helpful

    • @learnssis
      @learnssis  5 лет назад

      Thanks good to know that you liked the video.

  • @mannah
    @mannah 5 лет назад +1

    Very helpful tuts, thanks a lot.

    • @learnssis
      @learnssis  5 лет назад

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

  • @samikhan-n5b
    @samikhan-n5b Год назад

    is it same as Using Bulk Insert Task ? and which options should be used in which case.

    • @learnssis
      @learnssis  Год назад +1

      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.

    • @samikhan-n5b
      @samikhan-n5b Год назад

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

    • @learnssis
      @learnssis  Год назад

      @@samikhan-n5bGlad you got it.

  • @mecsihn
    @mecsihn Год назад

    Hi Akhil,i'm learning a lot with your videos i really apreciate it! I have one questioin, is it possible to use the same connection to import csv and txt files? or i need to create a different connection for each one? TKS.

    • @learnssis
      @learnssis  Год назад +1

      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.

  • @KumarAnalytic
    @KumarAnalytic 2 года назад

    Sir today I had an interview, and some question I failed to answer.
    First Question: I've a CSV file(column ID, Name,Address) and same table structure in SQL Server table, but here in address column I want to ignore any comma. So how do you acheive it.
    Second Question: In Packages where config file resides:
    Third Question: If package is running slow then how will you fix it
    Fourth Question: Does Lookup finds all matching instance or only one? Like in sql server table column ID and State, and ID is two times but state is different(like ID: 101,101 State: UP, MP). so explain if you're trying to load Text file into sql server then Lookup will give all matching in SQL Server table or only first match will tell you(This question I was not so clear little bit).

  • @CaribouDataScience
    @CaribouDataScience 7 месяцев назад

    How about doing a updated version of this videoi?

    • @learnssis
      @learnssis  7 месяцев назад

      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.

  • @smritijainmca
    @smritijainmca 4 года назад +1

    Hi Sir,
    I liked your videos and all are very helpful.
    Sir, please provide the video for how to load data from single csv file to multiple SQL server tables using some logics apply on the csv file itself.

    • @learnssis
      @learnssis  4 года назад

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

  • @RupaRudrawar
    @RupaRudrawar 10 месяцев назад

    Hi Aqil, I am new to SSIS and I have a scenario where I have to pull number of flat files where the names are changing everyday and I need to load these files to SQL server everyday with new modified date Can you please tell me or share any video or steps that is going to help me to handle this scenario.

    • @learnssis
      @learnssis  10 месяцев назад

      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.
      ruclips.net/video/BjpaSxMZMxs/видео.html

  • @ekw59
    @ekw59 4 года назад +1

    Thanks - very helpful!

  • @anweshpradhan7825
    @anweshpradhan7825 3 года назад

    I loaded the csv file to ole db destination can it run successfully but when i check on sql server i find 0 rows on the table??

    • @learnssis
      @learnssis  3 года назад

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

  • @SaiReddy-w1c
    @SaiReddy-w1c 10 месяцев назад

    Hi sir could you please explain how to give dynamic sql connection and dynamic folder path to the package. I want to create a package dynamic using dynamic parameters that is sql connections and Csv file path I need these values dynamically and I have to give dynamic file name

    • @learnssis
      @learnssis  10 месяцев назад

      How to create dynamic connection manager in ssis
      ruclips.net/video/QDSRzGxLe1g/видео.html
      How to make folder path and file name dynamic, watch this video
      ruclips.net/video/Y69bmI455uA/видео.html

  • @muhammadomarmansoor2067
    @muhammadomarmansoor2067 3 года назад

    Sir i am trying to import a csv file through ssis but unable to do so, the file manager is unable to recognize the columns in the file, the csv file is exported from a system and I have to load it into SQL server.

    • @learnssis
      @learnssis  3 года назад

      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
      ruclips.net/video/Zg1aZpoS0I8/видео.html

    • @muhammadomarmansoor2067
      @muhammadomarmansoor2067 3 года назад

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

    • @learnssis
      @learnssis  3 года назад

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

    • @muhammadomarmansoor2067
      @muhammadomarmansoor2067 3 года назад

      @@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?

  • @harisankar2579
    @harisankar2579 Год назад

    I'm unable to find the data tools in my machine even though I followed all the steps that was shown in the previous videos. Can anyone help on it.

    • @learnssis
      @learnssis  Год назад

      Did you installed SSIS along with Visual Studio and SSDT on your machine ?
      ruclips.net/video/0NozIVoNyY4/видео.html

  • @ianujthakur
    @ianujthakur 4 года назад

    Hello sir, Need your help here. I want data from different source like Oracle, netija and cms to one database using SSIS. Can you please help me.

    • @learnssis
      @learnssis  4 года назад

      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.

  • @jaitiwari241
    @jaitiwari241 Год назад

    ------------------------------
    Test connection failed because of an error in initializing provider. The 'SQLNCLI11' provider is not registered on the local machine.
    Showing this error when i click on Test connection

    • @learnssis
      @learnssis  Год назад

      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.

  • @madhuanusha8985
    @madhuanusha8985 4 года назад

    If I want to export data from multiple servers into single CSV file data is getting overwritten .can you please suggest how we can avoid overwritting the data

    • @learnssis
      @learnssis  4 года назад

      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.

  • @POV_hamza
    @POV_hamza 11 месяцев назад

    I am having issue in connecting server on OLE DB Destination what to do anyone

  • @NaveenKumar-rx3fq
    @NaveenKumar-rx3fq 2 года назад

    Hi sir, it's really nice.....
    I have one doubt...
    We checked the data is loaded or not into the table by using SQL SERVER
    Is there any chance will check in Visual Studio....

    • @learnssis
      @learnssis  2 года назад +1

      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.

  • @НиколайБардаков-б5щ

    thanks. very usefull video.

  • @Shashankmr-b7u
    @Shashankmr-b7u 7 месяцев назад

    why are we not using sql server destination why are we using ole db destination

  • @saikiranp.r6966
    @saikiranp.r6966 4 года назад

    Is it not required to change the data type to match the database table column data types?

    • @learnssis
      @learnssis  4 года назад +1

      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.

    • @saikiranp.r6966
      @saikiranp.r6966 4 года назад

      @@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

    • @learnssis
      @learnssis  4 года назад

      @@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.

  • @Mishramultiverse
    @Mishramultiverse 3 года назад +1

    Hi Akhil. Thanks a lot once again. I have been following your videos continuously as it helps me to learn ssis. I facing an issue while loading a csv file. I have a simple csv file, so when I load that and run the package I got an error like:
    " [Flat File Source 1 [12]] Error: The column delimiter for column "Closing Balance" was not found.
    ".
    Closing Balance is the last column. However I've searched for this issue but not get any result. Could you please guide me where I am getting wrong.

    • @learnssis
      @learnssis  3 года назад

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

  • @veeruch6205
    @veeruch6205 4 года назад

    Hi, I am new to ssis. Is unix scripting required for ssis?

    • @learnssis
      @learnssis  4 года назад +1

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

  • @amanahmed6057
    @amanahmed6057 Год назад

    Bro I am getting an error of unicode conversion.
    suggest me a solution

    • @learnssis
      @learnssis  Год назад +1

      Use a data conversion transformation if you are getting the error of unicode conversion
      ruclips.net/video/aQJCky2qfCs/видео.html

  • @werspiritual
    @werspiritual 4 года назад

    Thank you so much. Very useful. :-)

    • @learnssis
      @learnssis  4 года назад

      Thanks for you comment.

  • @aishwaryaingole4728
    @aishwaryaingole4728 3 года назад

    helpful ty u just saved me

    • @learnssis
      @learnssis  3 года назад

      Its nice to hear that it helped you Aishwarya.

  • @parveensultana8464
    @parveensultana8464 4 года назад

    excellent

    • @learnssis
      @learnssis  4 года назад

      Thanks for your comment.

  • @iagodossantosandrade6686
    @iagodossantosandrade6686 3 года назад +1

    Simple and helpful, thanks!

    • @learnssis
      @learnssis  3 года назад

      Its nice to hear from you.

  • @ajinzrathod
    @ajinzrathod 3 года назад +1

    Lucid Explanation

  • @narayanagottipati5980
    @narayanagottipati5980 5 лет назад

    sir, please provide the files which are used in this video class it will help a lot for practicing

    • @learnssis
      @learnssis  5 лет назад

      Please share your email id.

    • @brams3955
      @brams3955 3 года назад

      @@learnssis my email is abrahammbombaa@gmail.com

  • @nadeemahmed8803
    @nadeemahmed8803 5 лет назад

    How to deal with commas in comma delimited file when importing flat file as a source to SQL Server in SSIS
    ?

    • @learnssis
      @learnssis  5 лет назад +1

      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.

    • @nadeemahmed8803
      @nadeemahmed8803 5 лет назад

      @@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?

    • @learnssis
      @learnssis  5 лет назад +1

      ​@@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.

    • @nadeemahmed8803
      @nadeemahmed8803 5 лет назад

      @@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.

    • @learnssis
      @learnssis  5 лет назад

      @@nadeemahmed8803 Great good to know that.

  • @nikhilpatil3383
    @nikhilpatil3383 5 лет назад

    Giving error as file is already open by another process

    • @learnssis
      @learnssis  5 лет назад

      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.

  • @MdAlam-de4pf
    @MdAlam-de4pf 6 лет назад

    Great!!!

    • @learnssis
      @learnssis  6 лет назад

      Thanks for your comment.

  • @alihaider6435
    @alihaider6435 5 лет назад +1

    U r explaining as we are already know each n evey thing plz explain it properly

    • @learnssis
      @learnssis  5 лет назад +2

      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.

    • @antwanwimberly1729
      @antwanwimberly1729 9 месяцев назад

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

  • @ravitutika1671
    @ravitutika1671 6 лет назад

    Hi can you please upload videos on SSAS

    • @learnssis
      @learnssis  6 лет назад

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

  • @balajikomma541
    @balajikomma541 2 года назад

    Idi telugu lo videos cheyyachu ga please

    • @learnssis
      @learnssis  2 года назад

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

    • @learnssis
      @learnssis  2 года назад

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