Category Archives: SSIS

Creating a SSRS report to show SSIS package run time statistics

In most companies developers are restricted from accessing the MSDB database and they rarely know the performance of their packages in a production environment unless they have access to third party software tools or a friendly DBA. This happened to me once when I wanted to know how long my packages ran in a production environment and I had no access to the MSDB database to look at the sysjobs and sysschedules tables. The work around is to enable SQL Server logging in SSIS packages and to create a SSRS report from the sysssislog table.

The logic behind this solution is to enable SQL Server Logging in SSIS packages while we create/develop the package and send it for deployment.

Follow the rest of the article from here

Configuring Protection Levels of an SSIS Package

We work with SSIS packages holding sensitive data like server credentials and customer information,it is always a  good practice to use Protection Level property to secure the credentials and prevent it from unauthorized access.The Protection can be set up either to an entire package or only to the part holding the sensitive data. Hence its very important for us to know where to go to enable the Protection Level while working with packages.

Thumb rule to be followed is the Package should have same protection level at Project and Package level.

It can be configured in any of the following ways

1. Project Level

Right Click at the Project level and open its Properties window, You can configure the protection level you want from the drop down available at Security part.

Protection Level in Package at Project Level

2. Package Level:

Click anywhere in the designer and open its Properties Window by pressing F4 or View -> Properties

Protection Level at Package Level

3. During Deployment:

If you haven’t configured the Protection Level, you can still configure the package before deploying it to server by this method if you host your solution in a repository say using TortoiseSVN.

Click anywhere in the designer and Go to File ->Save Copy of Package as

Hope it helps!!



Running SSIS Packages in 32 bit mode

The original error message will be like this,

[Excel Source from Direct [16]] Error: SSIS Error Code DTS_E_CANNOTACQUIRE CONNECTIONFROMCONNECTIONMANAGER.The AcquireConnection method call to the connection manager “Excel Connection” failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Solution will be to Open the Visual Studio/BIDS–>Right Click at Project level –> Open Properties–>SetRun64BitRunTime as False

Running SSIS package in 32 bit mode.

In Production mode the Error will be like,

The requested OLEDB provider Microsoft.ACE.OLEDB.12.0 is not registered

Solution is to Right Click the Job from SQL Server Agent–>Open Properties–>Go To Steps –> Click Edit–>Open Execution Options Tab –> Use 32 bit run time

Running SSIS package at 32bit from SQL Server AgentCommentary: I was told the above two error occurs when we have our development machine running a 64 bit OS and our SQL Server is 32 bit so we have to force it to run at 32 bit.I believed the same, but accidentally I found my Server was 64 bit when I was working on an other issue, So what’ causing this? This error happens only when a package uses any of Microsoft Office Product like Excel or Access database, to be more precise the Microsoft Products are installed as 32 bit by default in any machine, so the associated drivers with Microsoft products like ACE.OLEDB.12.0 doesn’t supports our 64 bit environment,hence we force them to run at 32 bit. 

Subversion in Visual Studio 2013 using TortoiseSVN and AnkhSVN:

I would highly recommend a developer to understand the concept of Subversion before accessing any projects stored in a repository.

Subversion is a generic term and developers uses this technology to store their source code, .proj, .soln,web pages and other project related files in a repository. Developers working in a team will be able to download the files and upload the files to the repository with any code related changes thereby providing a single point of access. Subversion belongs to Software Configuration and Change Management field. Different subversion products are available in the market like ApacheSVN, TortoiseSVN, etc. We would use TortoiseSVN for our below illustration.

TortoiseSVN is an open source subversion product developed by Collabnet. We create our repository using TortoiseSVN software. We use the AnkhSVN plugin to access the repository through Visual Studio. In other words, AnkhSVN is an add-on used to integrate Microsoft Visual Studio and Tortoise SVN.

First download and install the latest version of TortoiseSVN 1.8.7 and AnkhSVN 2.5.124751. The installation is straight forward process.

You can verify the installation of TortoiseSVN by right clicking your mouse on any Windows Explorer and you will be able to see options for SVNCheckout and TortoiseSVN in it. The AnkhSVN installation can be verified by opening the Visual Studio and click File, you should see an option for Subversion.

In most real time scenarios a repository will be already existing, so let’s jump on How to access an existing repository.

Copy a Read only Solution from Repository:

If you want to get a read only copy of a solution file,     

Step 1: Create an empty folder at your desired location. Let’s call it SQLCASTReadCopy.

Step 2: Right Click the folder, Select TortoiseSVN and Click Export

Export a Project File from Repository using TortoiseSVN

Step 3: Enter the location of the Solution/Project file in the URL Repository drop down   and the location of the above created folder SQLCASTReadCopy in the Export Directory field and click OK, you will have a copy downloaded from the repository to the above folder.

Export an SSIS Solution from Repository using TortoiseSVN

Now you can access the downloaded copy from your local machine and you will not be able to write any changes to the repository.

Copy a Read and Write Solution from Repository:

Step 1: Create an empty folder at your desired location and call it SQLCASTWriteCopy

Step 2: Open the Visual Studio 2013, Click File -> Open -> Open from Subversion

Step 3: Enter the location of the solution/project file at the repository in the From field of the dialog box and enter the location of above created local folder location SQLCASTWriteCopy from Step1 in the To field and Click OK.

Now you will have a read/write copy downloaded to the above location.

Your changes can be written to the repository by clicking COMMIT and UPDATE in the Pending Changes window of Visual Studio 2013.

In any of the above process if the dialog box prompts for your NTlogin and password, enter it and it will validate with the Active Directory.

Hope it Helps:-)