24 How to use multiple transformations in an SSIS package ?

[@gauravtiwari6392]

Hello Akil, Nice video over here. Nowadays major business problem is to get connected with IBM DB2 database. Please make a video about that. Thank you.

[@ashitrivedi9856]

Hi I dont found table for the same in google drive.So i can perform same in my sql server.I have found only code for that video bt no tables.

[@learnssis]

In this video I am using the AdventureWorks2019 database, and this database is freely available to download along with data from Microsoft web site, I have created a video below how to download and restore this database to your sql server 2019 instance kzbin.info/www/bejne/gHyqf5x8ic16g7s

[@ashitrivedi9856]

Thanks alot for such a quick reply.

[@praveeng5319]

Hi, In an interview i got a question like benefits in ssis compartive with informatica or talend or any ETL tools

[@learnssis]

You can take a look at this detailed video from me. kzbin.info/www/bejne/hHjdmpiJmsmpq68 But what I can think right now is that if you already have a sql server license then SSIS comes free with SQL server license. If you already have some experience with a programming language like C# or VB.NET then you can use those languages to write the code inside SSIS packages. Because Windows is from Microsoft and SQL and SSIS is also from Microsoft thus it provides a better integration with your operating system and takes better use of system resources like Memory etc. You will find majority of help, support, resources, examples online on SSIS as respect to other ETL tools.

[@praveeng5319]

Another interview question - SSIS package daily running fyn but one day its taking long time to execute how you will debug to make it faster as usual?

[@learnssis]

If the package is still running then you can go to that sql server and run sp_who2 and see whether there is a blocking on the server or not which is causing the package to block, or see whether some other huge processes are also running on the server at the same time those are consuming the server resources and your ssis package did not get the enough recourses to execute. If the package is completed then you can try logging the start datetime and completed datetime for all tasks inside the ssis package along with SSIS package start date and completed date, this will help you to understand what tasks inside the ssis package are taking maximum time and you can try to fix and concentrate on those tasks, may be some indexes are missing or some unwanted indexes got created on a table, there can be tons of reason for a package to run slow. How to log start and end time of all tasks in SSIS package kzbin.info/www/bejne/aWimk4GMYt2rd6s If you want to see how to do performance tuning then I have shared few points in these videos How to do performance tuning in SSIS kzbin.info/www/bejne/baSudpqjZ9qEe68 kzbin.info/www/bejne/aaHElKuNmrmWm5Y

[@balasubramanyamjavvajivenk9663]

Hi, we can use the same derived column for gender is M and Mariatal status is single, why we need to go for script component?

[@learnssis]

Good question bala subramanyam javvaji venkata. Actually one of my subscriber asked to create an SSIS package which uses multiple transformations, that's why I used script component, otherwise we can do the same thing in derived column transformation as well.

[@HarshaV44]

Good One !!

[@learnssis]

Thank you Harsha vardhan.

[@balasubramanyamjavvajivenk9663]

Hi, In an interview i got a question like -> In a folder we have multiple types of files like excel,csv,txt etcc... we need to load those file names into sql server. i answered like we can create two variables 1. folder 2. filename take foreachloop container -> select for each file enumerator as type-> give folder path-> take edit script and write c# code to get the file names. Take one Data flow task -> and take Flat File Source and load in to the Destination. But he asked can we use Flat File source for Excel files? -> I was not able to answer this. Please answer me or make a video on this scenario.

[@learnssis]

You explained the method correctly. No, we can not use flat file source for excel files. To load excel files we can use either flat file source OR we can write C# code to load the data from excel file. Thanks.

[@balasubramanyamjavvajivenk9663]

@@learnssis can you make an video on this scenario??

[@learnssis]

@@balasubramanyamjavvajivenk9663 I already have a video on similar scenario kzbin.info/www/bejne/bH7daZuEnJlog6c

[@learnssis]

Hi, if you just want the names of files to be inserted into sql table, then you can use a foreach loop container and define a FileName SSIS variable of string type, now in foreach loop container, you can use Name and extension instead of Fully Qualified and it will just let you have only file name without file path and you can assign this value to the newly created FileName SSIS variable of string type, then you can use "Execute sql task" to insert the value from FileName SSIS variable into a sql server table. In execute sql task, in the expressions you can use "SQL Statement source" property and you can write a dynamic query there which can use the value from FileName SSIS variable. Below is an example of inserting a record into sql server table from SSIS variable using Execute sql task kzbin.info/www/bejne/emLPqoZjhLNkeqc kzbin.info/www/bejne/bHKsfmauoLRjfpo

[@learnssis]

I found this question interesting, thus will try to make a video on this soon. Thanks.

[@magedhendam6511]

amazing vedio can u please upload the table

[@learnssis]

This table exists in the AdventureWorks2019 database which you can download from Microsoft website kzbin.info/www/bejne/gHyqf5x8ic16g7s