Code page warning in SSIS

This article tries to address the code page warnings. This is a common warning encountered in SSIS packages especially when using OLE DB Sources.  The warning is a way to alert the user that SSIS is unable to fetch the Code page of the data source.  The picture shown below is displayed when the preview button is clicked to see preview the source data.

When you click on OK in the above dialog box, the preview is displayed, after previewing, you move on and finish saving the control flow component. The warning message is still displayed on the component with a yellow triangular symbol. The picture shown below is how it displays the tool top message when the cursor is moved over the data flow component.

In order to suppress this warning message, right click on the data flow component and select property. This will ensure the properties window appear, if it has been closed / hidden. In the properties window of the data flow component, under Custom Properties, change the default value False to True for the property AlwaysUseDefaultCodePage (picture below) and save the package. The warning message disappears.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru that let’s you know when there is a new blog post.

Bru Medishetty

Configure logging in SSIS packages

In this article we will look at the steps to enable logging in SSIS packages. Each control flow task in a package have multiple events  and logging enables you to look the execution details of these events.

Steps to configure logging

Open the package in Business Intelligence Development Studio (BIDS), see that you are in the design mode. When you are in the Control Flow, right click (do not right click on the control flow tasks) and select Logging from the drop menu displayed (picture below).

A dialog box Configure SSIS Logs is displayed. In the left hand pane, there is a tree view is displayed. Select the package by selecting the check box corresponding to it (picture below). You can check individual tasks also.

Upon selecting the package or task, you can then configure logging through the available logging providers from the drop down list as shown below. You can add multiple logs of the same type and/or another type. In our example we will look at selecting only one log provider and that is SSIS log provider for Text Files. After selecting the log provider, click on Add button.

Once the Log type is selected and added, the dialog box looks like the picture below. Choose the log file by selecting the check box to the left of it and go to configuration column to configure the location of the log file in our example it is a text file.

There would be a drop down list when you go to the configuration column, under which you would get a <new connection> listed, choose that and it will open a small window which would be similar to the one shown below.

Choose create file in the usage type and click browse button.. It would open a dialog box and we need to navigate to the directory where the SSIS package log file will be created. I am choosing the default Log directory of that instance here. (picture below) 

After choosing the location and the name of the file to be used, select Open button in the current dialog box that would take back to the previous dialog, select OK to configure the file location. Now we are all set, except the events that would be logging into this log file. To select the events, switch to the details tab as show below. Choose the events which needs to be logged into the log file. Choosing the events selectively is important, since we do not want too much of information is written into the log file, making it difficult to find information when needed. I always choose OnError and OnTaskFailed events for every task and some additional events in case of Data Flow tasks.

Continue to click a series of OK buttons to have the logging configured.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

Find SQL Agent Job name based on Activity Monitor

This is an article that I had never planned until today, late in the evening  when I was browsing through the forums at SQLServerCentral. I happened to see a thread where the user who posted the question was facing trouble identifying the name of the SQL Agent Job. You can visit the thread by clicking here. (Not required if you have actually been redirected from that thread).

Problem

When I initially looked at the post, I thought this is not a big deal and suggested to use Job Activity Monitor to look at the Active Jobs. What happenned next was made me sit for couple of hours on a Friday night to solve the problem, that eventually made in me write this article. The user replied back saying, what if there are multiple Jobs concurrently running? How can I identify which individual record in the activity monitor corresponds to a particular Job, since Activity Monitor displays the Job’s hexadecimal code instead of displaying the Job name.

I started to query the sys.sysprocesses and the sysjobs tables from Master and MSDB databases. From the first glance at the Name column of the processes and the Job_Id column of Msdb.dbo.sysjobs. After close observations of a particular Job records, I could find that the hexadecimal values in the program_name column of the sys.sysprocesses and the Job_Id column in Msdb.dbo.sysjobs are nothing but a rearrangement of the first 20 characters, the last 12 characters are same (at least from my observations until now).

When I could figure out the order of the characters, all I had to do was reshuffle those and compare, the complete script of the stored procedure is available below.

Use the following script to create the stored Procedure that is required to fetch the SQL Job Name.

When you next time identify in Activity Monitor that a Job Step is executing for more than the desired amount of time or using more system resources, pick that spid and execute the command as shown below.

EXEC dbo.FETCH_ACTIVE_JOB_NAME @SPID = 60

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru to know when there is new content.

–Bru Medishetty