SSIS – Create Text File Logging for SSIS Package Execution With DateTime.

Aim:

We have created a SSIS Package. Now we want to enable Logging for our Package and want to create a log file ( Text File Logging) each time the package executes. The Log file name should look like “PackageName_Date_Time.log”

Solution:

SSIS provides different types of logging such as
  • Text File Logging
  • SQL Server Profiler
  • SQL Server
  • Windows Event Log
  • XML File
In our case we will be using Text File Logging. Let’s start 🙂
 
Step 1: 
You can go to SSIS menu and then choose Logging Or right click inside Control Flow Pane and choose Logging as shown below
Step 2: 
Choose the Log provider, In our case we are using Text File Logging

Step 3: 
Create Connection for Log file as shown below

 

Step 4: 
Choose the containers , Tasks for which you want to enable logging

Step 5: 
Choose the events for each of the task or container you want to save in log file. For more details about events visit the link that I provided in Solution section

Step 6: 
If we run our package now, it is going to create a log file on desktop for us with the name MylogFile.txt. But our goal is not to generate a log file like that. We want to create a log file in PackageLogFiles Folder with PackageName and Datetime.

UntitledCreate a variable VarLogFolderPath and save the path of folder where you want to save the log files.

Step 7:

Create expression for our Log File Name for each execution. Go to MyLogFile Connection Manager and Right Click on it. Go to properties and then expression and write expressions as shown

Expression :
@[User::VarLogFolderPath]+”\\”+ @[System::PackageName]+”_”+Replace(Replace(Replace(SUBSTRING((DT_WSTR,50)(GETDATE()),1,16),”-“,””),” “,”_”),”:”,””)+”.log”

Capture
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s