Hello Aqil, I need your help! In the minute 5:5 to 5:45 you talk about connection manager. Do you have any videos on this topic? I don't have any connections available in the dropdown "OLE DB Connection Manager" Thanks!
how do i resolve this error?? [Flat File Source [15]] Error: Data conversion failed. The data conversion for column "id" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
You would need to increase the length of id column inside flat file connection manager inside advance option, save it. and then double click flat file source and click ok. th-cam.com/video/pTuouvbiWY4/w-d-xo.html
sir in variable settings in script task... ur just created two variable and one to read and another to write the result from foreach loop and put into v2. ?
I'm having an issue with extracting the Excel file name in SSIS. I've used a Foreach Loop and a Derived Column, but it always defaults to the first file instead of the desired one. What is the solution to this problem?
In the foreach loop container, in the variable mapping, map the FilePath ssis variable and also make the excel connection manager dynamic. You can watch below video to know how to configure this one. th-cam.com/video/TH9ptAkCHNA/w-d-xo.html
I have these text files that come in every day and I need to import that to a specific table that is already created in the database(I got that part figured out). Now I need to know how to automate the process so that I don't have to do it manually every day (Like I have to change the file location every day in the SQL query to get to the new file. I also have to find and replace the " quotation because they don't want that in the table). So, I need to figure out how I can ignore that "quotation when importing to my table and how to do that daily. Schedule some kind of job so it can run daily. I am using Microsoft SQL server management studio.
I faced errors on 2nd video practical as well. On 1st video you clarified and i also cleared by altering the table. Here im failing while fething the file . And a popup is been raised
Excellent video! Suppose, we are getting multiple file in FTP in .zip format and have to copy into local and unzip it and load files to SQL with different tables based on files name . please make a video on that
If you selecting data from MySQL from a table then you know the table name like from which table you are selecting it, you can add an additional column in the select list 'MyTable' as TableName OR you can use a derived column transformation and can create a new column and provide the table name there.
Hi Anna, while trying to execute the package, it shows an error "Exception has been thrown by the target of an invocation", can you help me please.... I sent the screen shot though mail
Hi Naveen, my name is Aqil. This question was originally asked by Anne. Instead of using the script task here, what you can do is Go to variables Pane, then go the FileName ssis variable go to expression and write below expression there RIGHT( @[User::FilePath] , FINDSTRING(REVERSE( @[User::FilePath] ) , "\\", 1) - 1) This will get FileName from FilePath.
Can you make the same demo, but for an excel source? I kept getting error the external table is not in expected format. But if i select the file directly it works. Also, what is a good way to confirm what the ado output is? Something like a data viewer. Thanks
If you want to make the excel connection dynamic, then you need to use the ExcelFilePath property instead of connection string. Take a look at this how we are loading multiple excel files. th-cam.com/video/TH9ptAkCHNA/w-d-xo.html Data viewer will only work when you will run the package.
@ thank you. I actually do that now and it works. But i want to get the filename out of a table row in sql. I figured out how to debug so i can test it tomorrow. By any chance you know if it is possible to skip a sheet in excel if it is not available on random dates? Example, file test.xlsx yesterday has 2 sheets (sheet1,sheet2) tomorrow the file only had sheet1 then next day it will have both again. Thanks
@@learnssis wow i did not know you can do this. I have been creating individual data flows for each sheet. I will definitely check this out. Thanks again!
Thank you For the Video I followed the steps and inserted the CSV file but when i tried to insert an Excel File it is giving me this Error "The connection string must contain one or more components of the form X=Y, separated by semicolons." And it only inserts one File eventhough i have two In the folder.
hey can you please help me i'm getting an error when specifying an folder path in for each loop container [Execute SQL Task] Error: An error occurred with the following error message: "Access to the path '' is denied.".
@@BTECH123 In each video I am showing how to do a small task of a project, so if you can learn these things then you can work on any project. At the moment I don't have one I will try to upload in future.
Hi Aqil, really awesome explanation. I was wandering what the steps would look like if you're loading those multiple CSV into multiple tables in SQL server and you a alter a column/ record in each CSV file ? Thanks as always.
Frankly speaking I have never tried that like alter a column in each csv file. Can you first load it as it is to the sql staging table and then execute the sql queries to alter a column and its value.
Hi Aqil, this video for me bought some hope that anything can resolved with the available technology. I had a query, i saw everyone explaining how to import excel/csv file from drive, but can we import from browser/some application on daily basis or hourly and where we can store the data into MS SQL Server.
If you need to import data from browser then you would need to write code in C# or VB.NET to read the data from browser and insert it into SQL Server. You can put that code inside SSIS package and then you can schedule SSIS package from SQL Agent job to run it any desired time.
This video is gold! So if you run the SSIS a second time will the "Email" Table still exist or do we have to rename the CREATE TABLE OLEDB Destination to "Email" again? If the "Email" table does exist then all we need to do is Truncate the table for a clean run? Thanks!
Thanks. So in real life scenario, there should be an execute sql task before the for each loop container and in the execute sql task, you can write code to may be drop and recreate the Email table every time the package will run. IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Emails')) drop table Emails CREATE TABLE [dbo].[Emails]( [Id] [int] NULL, [First_name] [varchar](50) NULL, [Last_name] [varchar](50) NULL, [Email] [varchar](50) NULL, [Gender] [varchar](50) NULL, [FileName] varchar(50) ) ON [PRIMARY]
sir script task is compulsory? do we need to have knowledge on c# or .net to learn ssis. i dont have knowledge on that with min of time i cannot learn them. should i go forward and learn ssis alone...to get a job?
Script task code is not compulsory even if you don't have much knowledge on C# or .NET still then you can be a good developer and you can learn the basics of C# and .NET over youtube.
Hi Aqil, I am new to ssis, My query is, how can we create a ssis package like when we place a particular file on a path then package first validate the file name then Execute the package. If there is any change in the file name package do not run.
Hi Saddam, to achieve this what you can do, you can use a foreach loop container and use SSIS variable that will give you the full file path, now in the foreach loop container you can use an script task to get the FileName from FilePath, I have shown this in below video, using a foreach loop container and getting file name from FilePath. th-cam.com/video/3QQOF2KVJ1E/w-d-xo.html Now the next thing you need to do is declare an SSIS variable for FileName and in the Script task assign the FileName from FilePath to FileName SSIS variable. In above video I am assigning a value to Load file SSIS variable so you can see that how we can do that. You also need to put the FileName SSIS variable in the read write variables list. Now between the Script task and Data flow task we will use a precedence constraint and put a condition like below @FileName=="your file name" So if this condition will be true then the Data Flow task will be executed otherwise file won't be loaded. I have written a condition in above video as well, so you can see how we can configure the precedence constraints. Thanks.
[SSIS.Pipeline] Warning: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console. I GOT THIS ERROR
![LAVROV's interview with Tucker CARLSON 😁 [Parody]](/img/n.gif)
These are really Wonderful example & helping a lots taking concept also working perfectly.
Thank you Manikant for appreciating it.
Thank you for a short and clean tutorial
Thank you for your response.
Excellent video! As always, very clear, precise and concise.
Thank you so much.
Hello Aqil, I need your help! In the minute 5:5 to 5:45 you talk about connection manager. Do you have any videos on this topic?
I don't have any connections available in the dropdown "OLE DB Connection Manager"
Thanks!
You need to click New and then it will open a new windows where you can provide the sql server instance name and database name.
Hi Aqil, Great tutorial as always 👍 Thank you for sharing your knowledge!
You are most welcome.
Thanks!
Thank you so much for the support, although you don't need to pay anything. Happy Learning.
Thanks for quick and simple solution.
You are most welcome Great Minds.
Thank you for making this tutorial❤
how do i resolve this error??
[Flat File Source [15]] Error: Data conversion failed. The data conversion for column "id" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
You would need to increase the length of id column inside flat file connection manager inside advance option, save it. and then double click flat file source and click ok.
th-cam.com/video/pTuouvbiWY4/w-d-xo.html
sir in variable settings in script task... ur just created two variable and one to read and another to write the result from foreach loop and put into v2. ?
I'm having an issue with extracting the Excel file name in SSIS. I've used a Foreach Loop and a Derived Column, but it always defaults to the first file instead of the desired one. What is the solution to this problem?
In the foreach loop container, in the variable mapping, map the FilePath ssis variable and also make the excel connection manager dynamic. You can watch below video to know how to configure this one.
th-cam.com/video/TH9ptAkCHNA/w-d-xo.html
I have these text files that come in every day and I need to import that to a specific table that is already created in the database(I got that part figured out). Now I need to know how to automate the process so that I don't have to do it manually every day (Like I have to change the file location every day in the SQL query to get to the new file. I also have to find and replace the " quotation because they don't want that in the table). So, I need to figure out how I can ignore that "quotation when importing to my table and how to do that daily. Schedule some kind of job so it can run daily. I am using Microsoft SQL server management studio.
I have done everything what you are talking about in this video using SSIS
th-cam.com/video/sKlDEuKnanw/w-d-xo.html
I faced errors on 2nd video practical as well. On 1st video you clarified and i also cleared by altering the table. Here im failing while fething the file . And a popup is been raised
Thank you so much, it worked❤
Thank you Magaba.
Excellent video!
Suppose, we are getting multiple file in FTP in .zip format and have to copy into local and unzip it and load files to SQL with different tables based on files name .
please make a video on that
if want to upload from MYSQL to SQL server and want table name then how i can do that?
If you selecting data from MySQL from a table then you know the table name like from which table you are selecting it, you can add an additional column in the select list 'MyTable' as TableName OR you can use a derived column transformation and can create a new column and provide the table name there.
Hi Anna, while trying to execute the package, it shows an error
"Exception has been thrown by the target of an invocation", can you help me please....
I sent the screen shot though mail
Hi Naveen, my name is Aqil. This question was originally asked by Anne.
Instead of using the script task here, what you can do is Go to variables Pane, then go the FileName ssis variable go to expression and write below expression there
RIGHT( @[User::FilePath] , FINDSTRING(REVERSE( @[User::FilePath] ) , "\\", 1) - 1)
This will get FileName from FilePath.
@@learnssis Thanks alot bro
Can you make the same demo, but for an excel source? I kept getting error the external table is not in expected format. But if i select the file directly it works.
Also, what is a good way to confirm what the ado output is? Something like a data viewer.
Thanks
If you want to make the excel connection dynamic, then you need to use the ExcelFilePath property instead of connection string. Take a look at this how we are loading multiple excel files.
th-cam.com/video/TH9ptAkCHNA/w-d-xo.html
Data viewer will only work when you will run the package.
@ thank you. I actually do that now and it works. But i want to get the filename out of a table row in sql. I figured out how to debug so i can test it tomorrow.
By any chance you know if it is possible to skip a sheet in excel if it is not available on random dates? Example, file test.xlsx yesterday has 2 sheets (sheet1,sheet2) tomorrow the file only had sheet1 then next day it will have both again.
Thanks
@@geeseeoh You can use a foreach loop to loop through all sheets in an excel file and import them
th-cam.com/video/1YELvo4FCKE/w-d-xo.html
@ thank you
@@learnssis wow i did not know you can do this. I have been creating individual data flows for each sheet. I will definitely check this out. Thanks again!
cant we directly use the varaiable name in derived col transformation...
Yes, you can do that.
thank bro
Glad you found it helpful.
Thank you For the Video I followed the steps and inserted the CSV file but when i tried to insert an Excel File it is giving me this Error "The connection string must contain one or more components of the form X=Y, separated by semicolons." And it only inserts one File eventhough i have two In the folder.
At the filename
It showing (DT_STR,50,1252)@userfilename
This is really helpful. if we have to load 50 files in 50 different tables what should be the steps..please share that video if you have
Hi, I don't have the video at the moment but I am planning to make a video on this topic soon. I will update you as soon as I made a video. Thanks.
@@learnssis Hi, have you created any video for this scenario. Really appreciate if you can share some ideas to do it with minimal manual intervention.
@@jenxz10 Sorry still not created a video on this topic, will try to make a video next week.
Hi, how is the file path fetched and assigned to file path variable in for loop. Please explain
Hi,
you can watch my below video for more explanation on Foreach Loop container with File enumerator
th-cam.com/video/cqw1Ce28FUs/w-d-xo.html
hey can you please help me i'm getting an error when specifying an folder path in for each loop container
[Execute SQL Task] Error: An error occurred with the following error message: "Access to the path '' is denied.".
Hi, it seems like either your path does not exists or you do not have access on the path.
Thanks, this was very helpful!
You are most welcome Joseph.
Dear Ahmed,
I hope you are well,
Can you please create a video to consume the data from APIs?
Best regards,
Alnebras.
Hi Nebro,
I will try to find a public api if I can find one to make a video on. Thanks.
ssis error code dts_e_primeoutputfailed
Please share the full error message.
@@learnssis tq sir problem solved. I watched ur individual videos and applied in realtime. The problem is with container.
@@BTECH123 Good to know that it worked for you.
Sir please upload atleast 1 realtime project
@@BTECH123 In each video I am showing how to do a small task of a project, so if you can learn these things then you can work on any project. At the moment I don't have one I will try to upload in future.
Hi Aqil, really awesome explanation. I was wandering what the steps would look like if you're loading those multiple CSV into multiple tables in SQL server and you a alter a column/ record in each CSV file ? Thanks as always.
Frankly speaking I have never tried that like alter a column in each csv file. Can you first load it as it is to the sql staging table and then execute the sql queries to alter a column and its value.
hi sri,
without using script task, can i directly insert the filename into sql table?
Yes, you can.
Hi Aqil, this video for me bought some hope that anything can resolved with the available technology.
I had a query, i saw everyone explaining how to import excel/csv file from drive, but can we import from browser/some application on daily basis or hourly and where we can store the data into MS SQL Server.
If you need to import data from browser then you would need to write code in C# or VB.NET to read the data from browser and insert it into SQL Server. You can put that code inside SSIS package and then you can schedule SSIS package from SQL Agent job to run it any desired time.
@@learnssis Thank You So Much
@@RukminiFocus You are most welcome.
This video is gold! So if you run the SSIS a second time will the "Email" Table still exist or do we have to rename the CREATE TABLE OLEDB Destination to "Email" again? If the "Email" table does exist then all we need to do is Truncate the table for a clean run? Thanks!
Thanks. So in real life scenario, there should be an execute sql task before the for each loop container and in the execute sql task, you can write code to may be drop and recreate the Email table every time the package will run.
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Emails'))
drop table Emails
CREATE TABLE [dbo].[Emails](
[Id] [int] NULL,
[First_name] [varchar](50) NULL,
[Last_name] [varchar](50) NULL,
[Email] [varchar](50) NULL,
[Gender] [varchar](50) NULL,
[FileName] varchar(50)
) ON [PRIMARY]
sir script task is compulsory? do we need to have knowledge on c# or .net to learn ssis. i dont have knowledge on that with min of time i cannot learn them. should i go forward and learn ssis alone...to get a job?
Script task code is not compulsory even if you don't have much knowledge on C# or .NET still then you can be a good developer and you can learn the basics of C# and .NET over youtube.
Nice! Thanks for sharing this
Glad you liked the video.
Hi Aqil, I am new to ssis,
My query is, how can we create a ssis package like when we place a particular file on a path then package first validate the file name then Execute the package.
If there is any change in the file name package do not run.
Hi Saddam,
to achieve this what you can do, you can use a foreach loop container and use SSIS variable that will give you the full file path, now in the foreach loop container you can use an script task to get the FileName from FilePath, I have shown this in below video, using a foreach loop container and getting file name from FilePath.
th-cam.com/video/3QQOF2KVJ1E/w-d-xo.html
Now the next thing you need to do is declare an SSIS variable for FileName and in the Script task assign the FileName from FilePath to FileName SSIS variable.
In above video I am assigning a value to Load file SSIS variable so you can see that how we can do that.
You also need to put the FileName SSIS variable in the read write variables list.
Now between the Script task and Data flow task we will use a precedence constraint and put a condition like below
@FileName=="your file name"
So if this condition will be true then the Data Flow task will be executed otherwise file won't be loaded.
I have written a condition in above video as well, so you can see how we can configure the precedence constraints. Thanks.
@@learnssis thank you Aqil.
@@sahuanalogchannel You are most welcome.
What website do you use to get this dataset ? because i am looking for a JSON File dataset.. Can you please advice?
I usually download the data from below website
www.mockaroo.com/
@@learnssis thx
[SSIS.Pipeline] Warning: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console. I GOT THIS ERROR
Thanks for sharing
Million thanks
you are superman!
Ha ha. This was one of the simple thing in SSIS.
@@learnssis I am working as an intern and your videos are really helpful , you are one of the best teachers. Again thanks for existing :)
@@priyam_1007 Thank you for your kind words. I am just a learner as well.
Can you please share the dataset
Okay, sure. I will upload it in next 5 min.
Ok thx
Do you offer one on one training?
uploaded the dataset here
drive.google.com/drive/folders/1PZF0bC7UitqEGEoUDx8QAW0f5WIsIZ7h?usp=sharing
@@ericsos101 I do, but I am busy this week. You can email me on aqil33@gmail.com in case you want further details for next week. Thanks.