SQL Profiler is used to trace recreate and troubleshoot the problems in the MS SQL Server
The Trace for monitoring an instance of the Database Engine or Analysis Services.
Here, we going to generate a SQL Profiler Trace for a Data flow task.
Create a project in SSIS and Take Data Flow task
I have defined a Data flow task to take one table from one OLE DB Source sort the sources and inner join both tables using Merge Join and Create a table in the Database
To create a SQL Profiler Trace
Go to SSIS tab and in the options select Logging
A dialog box Configure SSIS Logs will appear on the left side there is a checkbox next to the package check that box then Select a Provider type as SSIS log provider for SQL Server Profiler.
Click on the Add button the SSIS log provider will be seen the table below
Go to the Details tab that is beside Provider & Logs
Select the events for which you want logs
Click on Configuration and select New Connection
File connection Manager will pop up Select the Usage type as Create file and
Click on browse
I have created a New folder in C: and name the file as SSISsqlprofiler.trc and
Click on Open
Note:-Don’t create a file and open it just specify the file name and click Open.
Once done click Ok in the connection manager you can see the filename
Now execute the package
After the execution, you will see a file created on the location specified and it will get replaced each time you execute it
If you don’t want the previous file to get replaced
Then add a variable as folderpath with datatype String and give the path of the file
right-click on the file name in the connection manager and select properties
In properties go to Expression and click on browse
Select ConnectionString from the Property dropdown and click on the browse
you can drag and drop the variable name from the Variable and Parameters
This following expression defines the filename it concatnates the package name with current date and time:-
@[User::folderpath]+@[System::PackageName]+”_”+Replace(Substring((DT_STR,50,1252)getdate(),1,17),”:”,””)+”.trc”
Then click Ok
You can see the files are not getting replaced but each time we execute a new file is getting created
You can open the files in the SQL Server Profiler
If you are new to MSBI start with the following must-watch video: -
