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?
Watch this video in which I shown how to read data from excel file th-cam.com/video/12_JoGTTuH8/w-d-xo.html And then take a look at this video to understand how to write the data to a csv file. th-cam.com/video/n94-pFXP8eA/w-d-xo.html
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?
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/
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
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 th-cam.com/video/aQJCky2qfCs/w-d-xo.html
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.
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. th-cam.com/video/BjpaSxMZMxs/w-d-xo.html
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?
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.
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.
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 th-cam.com/video/Zg1aZpoS0I8/w-d-xo.html
@@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?
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.
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
How to create dynamic connection manager in ssis th-cam.com/video/QDSRzGxLe1g/w-d-xo.html How to make folder path and file name dynamic, watch this video th-cam.com/video/Y69bmI455uA/w-d-xo.html
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.
------------------------------ 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
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.
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.
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.
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).
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
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.
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.
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....
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.
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.
@@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.
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.
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.
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.
Thank you sir for this video. It helps me alot thank you very much.stay safe sir stay happy
Thank you so much inder kaur.
Aqil, bro you are a boss! Thanks for the tutorials
Thanks for your comment. I am just a learner to SSIS.
Hii i got error like please provide .net provider in ole db destination?
This is a great stuff you're doing. My only problem is that you're fast. But thanks for this free information
Thanks for your suggestion, will try to implement it.
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?
Watch this video in which I shown how to read data from excel file
th-cam.com/video/12_JoGTTuH8/w-d-xo.html
And then take a look at this video to understand how to write the data to a csv file.
th-cam.com/video/n94-pFXP8eA/w-d-xo.html
Thank you so much
How about doing a updated version of this videoi?
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.
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
Did you mapped the columns from source to destination correctly while configuring the OLE DB Destination ?
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?
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#.
Hi Akhil, There is no SSIS database in my SSMS.
How to fix this ?
Waiting for your help .
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/
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
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
th-cam.com/video/aQJCky2qfCs/w-d-xo.html
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.
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.
th-cam.com/video/BjpaSxMZMxs/w-d-xo.html
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?
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.
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.
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
th-cam.com/video/Zg1aZpoS0I8/w-d-xo.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?
Deep explaination thank you sir
You are most welcome Sir.
Very helpful tuts, thanks a lot.
Thanks for your comment, good to know that you liked the video.
Thank you! It was very helpful
Thanks good to know that you liked the video.
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.
Did you installed SSIS along with Visual Studio and SSDT on your machine ?
th-cam.com/video/0NozIVoNyY4/w-d-xo.html
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??
Oh, I think you missed something. Check your destination sql table and check your connection. Thanks.
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.
Sure Smriti, will load such kind of video in future.
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
How to create dynamic connection manager in ssis
th-cam.com/video/QDSRzGxLe1g/w-d-xo.html
How to make folder path and file name dynamic, watch this video
th-cam.com/video/Y69bmI455uA/w-d-xo.html
is it same as Using Bulk Insert Task ? and which options should be used in which case.
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 .. 🔥🔥🔥
@@samikhan-n5bGlad you got it.
------------------------------
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
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.
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.
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.
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).
I am having issue in connecting server on OLE DB Destination what to do anyone
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
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.
why are we not using sql server destination why are we using ole db destination
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.
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.
Thanks - very helpful!
Glad you liked it.
tesd connection failed while in oleb destination
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....
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.
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.
Can you please check this one ?
stackoverflow.com/questions/44359741/ssis-error-delimiter-for-column-columnx-is-not-found
Is it not required to change the data type to match the database table column data types?
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.
Simple and helpful, thanks!
Its nice to hear from you.
Thank you so much. Very useful. :-)
Thanks for you comment.
Bro I am getting an error of unicode conversion.
suggest me a solution
Use a data conversion transformation if you are getting the error of unicode conversion
th-cam.com/video/aQJCky2qfCs/w-d-xo.html
thanks. very usefull video.
You are welcome.
helpful ty u just saved me
Its nice to hear that it helped you Aishwarya.
sir, please provide the files which are used in this video class it will help a lot for practicing
Please share your email id.
@@learnssis my email is abrahammbombaa@gmail.com
Hi, I am new to ssis. Is unix scripting required for ssis?
Sorry I never worked on any Unix platform, so don't have any idea about it.
Giving error as file is already open by another process
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.
excellent
Thanks for your comment.
U r explaining as we are already know each n evey thing plz explain it properly
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
Hi can you please upload videos on SSAS
Hi, Currently I am very much occupied with some other projects, but in future I am planning to upload on SSAS as well.
How to deal with commas in comma delimited file when importing flat file as a source to SQL Server in SSIS
?
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.
Great!!!
Thanks for your comment.
Lucid Explanation
Thanks so much.
Idi telugu lo videos cheyyachu ga please
క్షమించండి నాకు తెలుగు రాదు
Kṣamin̄caṇḍi nāku telugu rādu