ไม่สามารถเล่นวิดีโอนี้
ขออภัยในความไม่สะดวก
02 Get the file name from file path in SSIS and insert into SQL table | SSIS real time scenarios
ฝัง
- เผยแพร่เมื่อ 21 มิ.ย. 2021
- Get the file name from file path in SSIS and insert into SQL table | SSIS real time scenarios
Dts.Variables["FileName"].Value = System.IO.Path.GetFileName(Dts.Variables["FilePath"].Value.ToString());
Download the file\script used in the Video from below link
drive.google.com/drive/folder...
SSIS Tutorials: • SSIS Tutorials
SSIS real time scenarios examples: • SSIS real time scenari...
SSIS Interview questions and answers: • SSIS Interview questio...
Get the file name from file path in SSIS and insert into SQL table
How do I find the filename in SSIS?
How do I import a filename into SSIS?
How do I get the filename in SQL?
How would you save file names from different folders in a SQL Server table?
If you have any questions or suggestions please comment on the video or write to me at “aqil33@gmail.com”
Excellent video! As always, very clear, precise and concise.
Thank you so much.
Thank you for making this tutorial❤
These are really Wonderful example & helping a lots taking concept also working perfectly.
Thank you Manikant for appreciating it.
Hi Aqil, Great tutorial as always 👍 Thank you for sharing your knowledge!
You are most welcome.
Thanks for sharing
Million thanks
Thank you for a short and clean tutorial
Thank you for your response.
Thanks, this was very helpful!
You are most welcome Joseph.
Nice! Thanks for sharing this
Glad you liked the video.
Thanks for quick and simple solution.
You are most welcome Great Minds.
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
Thank you so much, it worked❤
Thank you Magaba.
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. ?
Thanks!
Thank you so much for the support, although you don't need to pay anything. Happy Learning.
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.
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
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]
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.
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
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.
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.
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
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.
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.
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.
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, 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
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
@@user-qh7ji6le4t You are most welcome.
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
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.
cant we directly use the varaiable name in derived col transformation...
Yes, you can do 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
[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
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.
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.
At the filename
It showing (DT_STR,50,1252)@userfilename