Tag Archives: SSIS

Code page warning in SSIS

Posted on by
1

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

Posted on by
2

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

Deploying SSIS Packages

Posted on by
15

In this article we shall take a look at the steps in building an SSIS Package and deploy it.

You may have designed the package in your Development System and tested the Package from BIDS. Now it’s time to move it to the SQL Server. Up to this point, you have developed a Package in BIDS but have not deployed it onto the Integration Services. Let’s look into the steps involved in moving it to SQL Server Integration Services.

Deploying SSIS Packages would be helpful when you have multiple packages in a single solution, so that you can deploy all of them at once.

Building the Solution / Packages

Once the packages are all developed, right click on the Solution in Solution Explorer and choose properties in the Menu. Upon selecting properties, a Dialog box appears as shown in the picture below.

The output path is by default bin, it is the directory where the files that are built are going to be placed by SQL Server.  For deploying the SSIS Packages we need to have deployment files which will not be created by default when you build a project, to enable this under the configuration properties in the left Pane, select Deployment utility as shown in the picture below and change the property value to True corresponding to CreateDeploymentUtility, additionally you can also set the Directory path where the deployment utility has to be placed upon building the solution. Choose your settings and click Apply to ensure the changes in the properties are saved.

The Next step is to Right Click on the Solution in the Solution Explorer and choose Build. (as shown in the picture below)

The status of the build process is showed at the bottom left corner of the BIDS as shown below.

When the build /rebuild is successful, navigate to the directory is referred in the DeploymentOutputPath couple of screens back. You should be seeing the packages in the solution and along with them an additional file usually of 1 KB which is the Manifest File what helps in deploying the packages in the solution.(Picture below)

Deploying the Packages

Double click the Manifest File to start the deployment. The Package Installation wizard begins and Deploy SSIS Packages step is the first screen that is presented.

This screen lets you select where shall the packages be deployed, as mentioned in the Dialog Box, deploying in SQL Server is more secure, since SQL Server stores the packages internally compared to File System where additional security measures needs to taken to secure the physical files. My personal choice would be to deploying inside SQL Server.

Additionally, choose the option to validate the packages after Installation.

If the deployment is on the same Server you can mention “.” in the Server Name or choose the Server Name from the drop down list if is a remote Server where you would like to deploy the packages. Choose the Authentication mode that you would like to connect to that Target Server you want to deploy. Click Next to continue.  

The dialog box above displays, the location where the Package dependencies are going to be stored and can be modified if needed. Click Next after deciding the location for dependencies.

Click Next in the above screen to deploy the packages. The Installation is done and the tasks in the packages are validated.  

Click on Next in Dialog Box and you should be able to see a View Installation details Dialog. Click Finish to complete the Deployment of the SSIS Package.

One of the next steps would be to schedule the Package using SQL Server Agent.

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