dinsdag 6 december 2011

Bulk change SSIS Package configuration

Did you ever had a lot of SSIS packages that you needed to change a package configuration string in? Did you start with opening each package in BIDS and ended up with a clicking finger?

This is my situation, in my packages I do use an environment variable that points to a configuration database for the packages. The configuration database is used to store package configurations, package logging and audit information for process runs.

Let's say that the environment variable used is named ENV_SSISConfig.
It points to a connection string that uses initial catalog dbSSISConfig.

All packages have a package configuration using the environment variable ENV_SSISConfig, when you look at the code of the package you will see the line:
<DTS:Property DTS:Name="ConfigurationString">ENV_SSISConfig</DTS:Property>
It's desided to give each SSIS project there own configuration database, for the current project this means the database dbSSISConfig is renamed to dbSSISConfig_DWH. No problem so far I can just change the ENV_SSISConfig environment variable to use the initial catalog dbSSISConfig_DWH.

But what when a second project is started? The environment variable ENV_SSISConfig is already in use.
So the first we did was renaming the ENV_SSISConfig to ENV_SSISConfig_DWH and then we need to edit each package created to change the environment variable ENV_SSISConfig to ENV_SSISConfig_DWH.

<DTS:Property DTS:Name="ConfigurationString">ENV_SSISConfig</DTS:Property>
will become
<DTS:Property DTS:Name="ConfigurationString">ENV_SSISConfig_DWH</DTS:Property>

Current this means that we need to open 300 packages in BIDS to change the package configuration, with a few people this can be done quickly but I'm a bit lazy and did use the below VBScript to change the configuration in all packages.

' Create a FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")

Const ForReading = 1
Const ForWriting = 2

' Define folder containg the files whe like to replace the string in
strPath = "D:\SSIS\SSIS_DWH\Solution\MAIN"

Set folder = objFSO.GetFolder(strPath)
Set files = folder.Files

' Loop through each file  
For each item In files
 If UCase(objFSO.GetExtensionName(item.name)) = "DTSX" Then
      End If  

'Function to replace the string
Function myReplace(strFileName)
 Set objFile = objFSO.OpenTextFile(strFileName, ForReading)

'Define strings to replace Used chr(34) because string has double quotes that need to be searched
 strOldText = "<DTS:Property DTS:Name=" & Chr(34) & "ConfigurationString" & Chr(34) & ">ENV_SSISConfig</DTS:Property>"
 strNewText = "<DTS:Property DTS:Name=" & Chr(34) & "ConfigurationString" & Chr(34) & ">ENV_SSISConfig_DWH</DTS:Property>"

 strText = objFile.ReadAll
 strNewText = Replace(strText, strOldText, strNewText)

 Set objFile = objFSO.OpenTextFile(strFileName, ForWriting)
 objFile.WriteLine strNewText
End Function

Run the script from a command prompt: cscript quick_replace.vbs.
Ofcource before doing so I copied the solution\MAIN folder to solution\20111206 folder to make sure I could roleback when something should go wrong.

You could improve the script by execpting input parameters as folder, oldstring and newstring if you like. I tried that but had some issues with the double quotes containd in the replacement string. So quick & dirty worked for me.

Hope you enjoy this blog! When you have comments, better solution and recommendation I like to hear from you.
Dynamic Intelligence | LinkedIn | Blog | Twitter

zaterdag 12 november 2011

Installation SQL Server “Denali” CTP3


Displaying the installation of SQL Server in a blog will give a very long blog to display. That’s why I wrapped this in a document you can download here: [Download]

This document is a working document, the current status is displayed below.

2011-11-12 Chapter: Initial Installation SQL Server “Denali” CTP3


During installation or configuration found issues are supplied as feedback to Microsoft Connect. They are displayed in the document also.

Issue_01 Adding domain local security group to DRC administrators role not possible during installation: https://connect.microsoft.com/SQLServer/feedback/details/700181/install-drc-does-not-except-domain-groups-during-install

Hope you enjoy this blog! When you have comments, better solution and recommendation I like to hear from you.

Dynamic Intelligence | LinkedIn | Blog | Twitter

maandag 26 september 2011

SSIS Errors in TDS Stream & the expression for variable "MyVar" failed evalution.

Current I'm developing SSIS packages on a development environment running Windows 2003R2 SP2 32Bits and SQL 2008 SP2 32 Bits.

I just recent did get access to this environment, before that all development has been done in production environment running Windows 2008 64Bits and SQL 2008 SP2 64 bits

In the nearby future I will receive a development environment that represents the production environment, but for now I have to do with the development environment I have.

While developing on the production environment all seams to be working fine, now developing on the development environment I ran into some strange issues.

First let me see you what I have for packages, it pretty simple,  a master that executes some packages in parallel:
When I ran this package from a SQL Agent job add random the package will fail with the error:
The expression for variable "MyVar" failed evalution. There is an error in the expression.....

The packages executed are not more then some audit info and a simple DFT: OLE_DB Source > OLE_DB Target.
The variables displayed in the processing packages are also defined in the master package.
When running the packages from BIDS no errors are displayed on the MyVar validation.

The second error I do receive is : SSIS package fails with Protocol error in TDS stream
This error occures when running a package that needs uses a view as OLE_DB source that uses the same table to check of inserts are needed as it is used as OLE_DB Target to write the records to.

First let's start with some solutions I found on the web for the second metioned error: SSIS package fails with Protocol error in TDS stream.

First I found comes from http://blogs.msdn.com/b/ramoji (Ramoji Ryali's Fundas). He says that this error is issued when the SQL Network Protocol is changed from TCP/IP to NamedPipes. The solution should be:
  1. Go to Start--> All Programs--> Microsoft SQL Server 2008--> Configuration Tools --> SQL Server Configuration Manager
  2. Expand the node SQL Native Client 10.0 Configuration
  3. Right click on "Aliases" and then select "New Alias..."
  4. Provide the IP address of the Source SQL server against the "Server" field and specify the Source server name against the "Alias Name" field.
  5. Choose the protocol as "NamedPipes" and click OK
He also metioned that he is processing data from server X to server Y, this is not my case, I'm even processing on the same server for developing and same database.

The second possible solution I did found came from Microsoft: http://support.microsoft.com/kb/945977
They mention that I could have to do with the TCP Chimney feature that is enable in Windows 2003 SP2.

First thing to do is to check or you have the latest NIC driver on your server installed. When this is the case you can try to disable the chimney feature following the below steps.
  1. Click Start, click Run, type cmd, and then click OK.
  2. At the command prompt, type:
  3. Netsh int ip set chimney DISABLED
  4. Press the ENTER key.
I tried this second solution and was able to do 3 runs before writing this blog without the TDS error. But I also find that I did not use with(nolock) in the source view query on the join of the table that I need also to insert the records into. Because it's working I don't want to enable the chimney again to see what was really causing the error. Maybe when I have some spare time left I will let you know. For now I hope you here find a solution for your problem.

Let's get back to the first error: The expression for variable "MyVar" failed evalution. This seems to occure on the master packages, I think I already know why, I will tell you what is done in the master package.

In the master package I do have some connection managers for the package that need to be executed. I use a connectionstring expression on the connectors to make it flexible to run the packages when they are placed in a different location.

The expression is: @[User::varFolderPackages_str"]+"<nameofpackages.dtsx>"
The variable @[User::varFolderPackages_str"] is also an expression based variable that consists of the expression: @[User::varFolderSSISRoot_str"] + "Packages\\" where @[User::varFolderSSISRoot_str"] again is an expression @[User::varFolderRoot_str"] + @[User::varNameSSISProject_str"] + "\\"

Those are both filled hard by a package configuration with D:\SSIS and SSIS_Staging_Hubs for example.

Because I don't realy know what is causing this, and it does occure random, some runs the master packages runs without error and the next following run does again error on the variable evalution I think thought it might have to do with the parallel processing and handeling the variables.

The simpel solution is that I changed the package connection managers to a hardcoded connection string. For now this solves my problem, no errors last 3 run's, but it removed my flexibility. All my SQL connections are created by using package configurations based on a SSISMetaData.SSISConfiguration table. I think I will start adding the package connection managers to.

Hope you enjoy this blog! When you have comments, better solution and recommendation I like to hear from you. Dynamic Intelligence | LinkedIn | Blog | Twitter

dinsdag 12 juli 2011


At one of my current customers I created a simple package to export views to Excel documents.
Because I'm a bit lasy I copied the first package I did create to generate a new one for the next view.

The package contains the following DFT:

When I do run the package the following error is generated:

It's not really clear what the error is so let's take a look at the sysssislog:
The external columns for component "OLE_SRC OLE_DB_ZH308 ZH308GBSturing ZH308 varSQLSelectSource_str" (85) are out of synchronization with the data source columns. The column "ZH308_DBC_ID_Trigger" needs to be added to the external columns.  The column "Eerste_Trigger_Datum" needs to be added to the external columns.  The column "Laatste_Trigger_Datum" needs to be added to the external columns.  The column "ZH308_Aangemaakt" needs to be added to the external columns.  The column "Historische_Journaalposten_Aanwezig" needs to be added to the external columns.  The column "DBCTraject_Gegegevens_Aanwezig" needs to be added to the external columns.  The column "DBCFactuur_Gegegevens_Aanwezig" needs to be added to the external columns.  The column "DBCPatient_Gegegevens_Aanwezig" needs to be added to the external columns.  The column "DBCUZOVI_Gegegevens_Aanwezig" needs to be added to the external columns.  The column "UZOVI_Digitaal_Factureren_Mogelijk" needs to be added to the external columns.  The column "DBCSpecialisme_Gegegevens_Aanwezig" needs to be added to the external columns.  The external column "Bedrag" (333) needs to be removed from the external columns.  The external column "CodeVerzekeraar" (330) needs to be removed from the external columns.  The external column "Factuurnummer" (327) needs to be removed from the external columns.  The external column "Titel" (324) needs to be removed from the external columns. 

Seems to be clear, or not? When you looked at my DFT task you did maby see that I don't have the OLE_SRC OLE_DB_ZH308 ZH308GBSturing ZH308 varSQLSelectSource_str source in there.
All MetaData of the OLE_SRC, is what I would expect.
So do open the advanced editor for the OLE_SRC OLE_DB_ZH308 :

Thats strange, all messages saying the column needs to be added or column needs to be removed, why I do not see it here. Do you?

It seems to be there somewhere, so to be sure I did take a look at the XML code of the package and did found:

What is that doing there? Why is it none gone! From the previous error from sysssislog whe can see the error is generated from the component id 85

As you can see in the lower part of the image above, my current OLE_SRC is has component id 201. The shown component id 85 is not even visable in the DFT any more.

How to solve this? I tried to remove the component id 85 from the XML code but this will end-up with a corrupted package. Need to find out what to do next time. So my final but not real great solution was creating the package from scratch. I needed to do this because I did rename my OLE_SRC task already. Recreating a OLE_SRC task with the same name and removing it again does not solve the issue.

The best way is just prevent this issue! When you have duplicated your template package the first you do is remove the OLE_SRC task and add a new one.
You can replay this issue in the following way:
1 - Create a DFT
2 - Add a SQL OLE_DB source with setting the SQL Data access command to : SQL Command and get your query in.
3 - Now copy your package to generate a new one
4 - Change the SQL Data Access command for the SQL OLE_DB source to : SQL Command from variable
5 -  Save your package and look at the XML code, when fine you still see the SQL Command setting as component in your package...

Hope you enjoy this blog! When you have comments, better solution and recommendation I like to hear from you. Dynamic Intelligence | LinkedIn | Blog | Twitter

zondag 10 juli 2011

SharePoint 2010 using BCS with SQL Server database

SharePoint BCS (Business Connectivity Services) can be used to display information from you business applications in a SharePoint environment.

In this blog I try to explain how to use BCS to get data from a SQL server database.


Above image is a simple view of the environment I’m using.

  • SHP01 = SharePoint 2010 Web & Application server using NTLM security
  • SQL01 = SQL 2008R2 database server used to store the SharePoint databases
  • SQL02 = SQL 2008R2 database server used to create the BCS test data
  • PC01 = Regular Windows 7 client environment to be used

I created a site collection on SHP01 http://demosites.di.local/sites/BCSDemo using the blank site template

Domain user DI\Farm1Admin1 is the site collection administrator.


I created a domain user DI\adBCS_Reader that is used to create a BCS list based on Windows Authenticed secure store.

On SQL02 a demo database dbDemoBCS was created using the script you can download [here]. It contains a table tblDemoBCS that now has 1999 rows, why 1999? I will get to that later in this blog.

The user DI\adBCS_Reader is added to the database security with db_datareader rights. A SQL Login sqlBCS_Reader is created and also added to the database with db_datareader rights.

I think we are ready so lets get started.

BCS with SQL and Windows Authentication

First thing we need to do is create the secure store target application, we need central administration for this.

CA > Application Management > Manage Service Applications > Secure Store Service Application > Click on New (Click image to view configured values)




The secure store application is now created and we need to set the credentials



We are now ready to create a BCS Application using the SQL02 database.

We need SharePoint designer to do this, I use the PC01 client with the DI\farm1admin Login.

First open the site in SharePoint designer… and then follow the images..



Change Name of the BCS Application and start creating the data source


Now we need to give the SQL connection information, the Name setting is optional but to make sure that we can set the correct security later when we add the SQL based BCS I will give it a name. This I recommend to do when you create several BCS applications to the same database using different credentials, also use different connections.

After clicking OK we need to verify the security and need to login with the credential that is registered as credential for the Secure Store Application ID.

Now we can run into the first error you can get when using BCS:

I use the account DI\Farm1Admin1 as the site collection administrator an I logged in with this account on PC01 for using SharePoint designer. This means that when adding a BCS application (External Content Type) what we are doing you need to have privileges in the BCS service to do this.

CA > Application Management > Manage Service Applications > Manage the BCS Service Application


No add the Farm1Admin1 account with all possible priveleges:

Return to SharePoint Designer again. If you click ok on the error box you need to add the connection again. (see previous steps)

Now the connection is added, expand the database to view the tables, right click the table and select create all operations…




After clicking finish don’t forget to click Save button!


No we can go and add a list to the SharePoint BCSDemo site.






The list is created and data is displayed..

Now let’s see what happens when we switch user on the site, I use DI\Arjan who is member of the BCSDemo owners for the site.

This one we no already, lets get to CA > Application Management > Manage Service Applications > Manage the BCS Service Application. This time click the just created BCS application and select Set Permissions


I add the DI\Domain Users of course you could select a special group or only users that need to use this application. This time only execute will be enough.

A different error is displayed.


Why is this error displayed? It’s not really clear from the message. But I do have a solution that solved this so lets implement.

Remember that we created the secure store application? At some point we had to define some members that where allowed to impersonate as the given security privileges. We need to add the users that are going to use this application to this same members setting.

CA > Application Management > Secure Store Service Application > Manage

You can get to the members setting only by editing the secure store application. Just continue all steps until you get to where you can add members and add your desired privileges. I added domain users to keep it simple.


Return back to the site with the user with less privileges as the site collection administrator (in my case DI\Arjan), is the list displayed???


Yes it is!

BCS with SQL and SQL Authentication

In this case I want to use the SQL Authentication, most of the steps are the same as above and will skip some screens to not make this a massive blog Smile.

First create the secure store application, to not error I setting all corrections done above immediately.

No differences yet only in the name..

Here is a part of the difference between windows authentication.

This time I added domain users immediately, I now my errors already Smile.


This time we entered the SQL Login as credential.

We can switch to SharePoint Designer and add a new BCS application.



The previous connection is still there, we need to add a new one to make it work with the new secure store application.


This time we verify the SQL Login to have access to the database.

Now we create all operations again, see previous steps to display how. But end up with a screen like below and don’t forget to click the save button!


Go to CA to set permissions for the created BCS Application.


Remember this from previous given error solution..


Now return to the demo site and add a list based on the SQL BCS Application, I hope you remember how other wise scroll back Smile.


Is the list showing?

Does it also show when using a other login on the site?

One more time displaying the error:

Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.

Correlation ID:b6729321-564e-4a39-a47a-c621db88894e

Why? It has to do with the number of records in the table, soon I will update for now the sun is shining.

Hope you enjoy this blog! When you have comments, better solution and recommendation I like to hear from you.

Dynamic Intelligence | LinkedIn | Blog | Twitter