.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Failing Connection String Environmental Variable, XML Variable, System Variable (SQL Server) Setup

Posted By:      Posted Date: October 13, 2010    Points: 0   Category :Sql Server

We attempted to implement the Chapter 2 Framework from the Knight, Vermeen, Moss, Hackney book on SSIS 2008: Problem Design Solution. We see a couple of problem areas (at least) in our interpretation of the guidelines from the book.

1. We are attempting to have one connection string per server: dev/prod

2. We have set up environmental variables per server: dev/prod

3. We have set up XML Package Variables per server: dev/prod

4. We have set up System Variables per server in SQL Server: dev/prod

Problem 1:

We've had to manually key in the common connection string to all packages on a server environment in the component properties area (control flow; not data flow--ok there)

Shouldn't the connection property find the connection that we've instantiated and provide us with a pull down to select it?

The components are un-editable after we select the common connection string, but they still run properly in our SQL Server Agent Job.

Problem 2:

The dev server variable overlays the prod server variable information duringprod component editing

We are'nt sure if these problems are related.

Here is our package variable:



Here is our DTSconfig file; we have a few variations of this that we've tried.

View Complete Post

More Related Resource Links

Error: The variable System::ErrorDescription contains a string that exceeds the maximum allowed leng


i get the following error message, when I try to email the error description, by invoking a 'Send Mail Task' in OnError event.

"Error: The variable System::ErrorDescription contains a string that exceeds the maximum allowed length of 4000 characters."

Any idea how to resolve this issue? I am using substring when I build the expression. But still am facing this issue.

Pass the server variable to search core result web part


I'd like to custom the search core result web part to filter results based on the log on user name.

It can easily be done in Dataview web part.

However the search core web part doesn't accept "<parameterbining>" to pass in the server variable [logon_user]. I can't figure out how to use <xsl:param> to get this server variable either.


Any ideas? Thanks a lot!

If you get confused, listen to the music play...

label concentation with a string variable


I am trying to do something as simple as this in visual studio.net

double num1 = 2;

num1 = num1 * 2;

 Labe1.Text = "The result shown is" & num1.ToString();


but I keep getting the error message operator '&' cannot be applied to operands of type 'string' and 'string'

query string or session variable or neither for this scenario?

Hello,example scenario: user login has a list of associated 'accounts' it has access to... this list of accounts is stored in a database table, say tblAccountAccessControl, something like:login, accountID-----------------------userA, account123userA, account456userA, account798userB, accountABCuserB, accountDEFetc..this info is used throughout the web application and determines the 'domain' of information the particular login has access to. For example there are many other tables that have the 'accountID' field and whenever a page pulls data from the table it only pulls data that the currently logged in user should be allowed to see, based on that tblAccountAccessControl data.the security question:1) do I need to query the database every time I need to get this list of allowed accountID's? I was about to use a plain old query string to pass a particular accountID to another page but quickly caught myself as I realized that would be a major security flaw (the receiving page was going to use that accountID to grab info from the database, and there would be nothing preventing a user from simply typing in a url manually to get data from an accountID they are not supposed to be allowed to view). So I then thought I'll just store the list of accountID's in a session state variable so they will be carried with the user throughout the sessi

SSIS Deployment Reads local Environment Variable and not Server

Hi All, I have an SSIS package that uses an Environment variable to hold the connection string for the local SSISConfiguration table.  This works properly on my local machine.  However, when I import the package to Integration Services(through SSMS) it reads the environment variable from the local machine and not the server it is deployed to.  If I log on to the server remotely and do the same process it uses the the correct environment varible... (SQL Server 2005) Is there a way to deploy the package to the server and get it to use the correct Env variable without having to log-in to the server and import it?  Let me know if anyone is looking for additional information.   Thanks!

Variable issue - SQL Server to Oracle

i use a variable (date type is Object) to store number from sql server, the format is: 123 456 789,00 (Norwegian) when i execute the package and sync it to Oracle (the input parameter is numeric), then an issue occured, it said can not covert the string to numeric. how to transfer it to the stardant format: 123456789.00?   thanks

What type variable can I use to assign a possible Null or a string to?



I am converting a VB6 app to VB2008 and are having problems with replacing Variant variables.

I used Variant in cases where the data might be string or Null.

Is there a way I could check for Null and just set the string to zero lenght?

Any help would be appreciated.



how to trim a string value of a workflow variable using SharePoint designer.


I build a custom workflow using SP designer and have a need to trim a string value of a workflow variable. I tried using Calculation or Dynamic String builder but without success.

i store a user name type value into workflow variable called  check_name_var (string type). the logon name have this format  domainname\first_lastname 
how do i trim this value in check_name_var to only first_lastname.

Can anyone show me how or have suggest on how to accomplish this or is it possible to do this in SP designer?

Thanks for your help

Empty diagnosis or lack of one variable in SQL Server



I want to  recongize that a nvarchar variable in stored procedure is null or not null.

and this example :

DECLARE @Count int;

SET @Count = (SELECT Count(Fld) FROM Tbl)

if there is no row how much whould be the value of @Count?????

zero or null?


Variable value within SQL Server table Configuration is not being updated within load of DTSX packag



I am probably missing something obvious here; -
I have created a package with a variable named; - ServerName and a value of 'KIERAN-PC'
I exported this variable as an SSIS configuration within a SQL Server table

I noticed the following record appeared within the SQL Server configuration table.


How to convert string variable to url?



 I need to convert a string to http url.

The way i have to do this is--I have to retriew a value from database and convert it into a sharable http url in Asp.net C#.


I am new to this concept,Can anyone help me.......................

How to validate XAML which is coming in string variable


I have written following code to validate XAML for duplicate name of controls. But it is not good way of doing because I am using Try for validation.

Please suggest me some good way of validating XAML.


bool validateXAML(byte[] imageFileToOpen)

            if (imageFileToOpen != null)
                MemoryStream mysr = new MemoryStream(imageFileToOpen);
                StreamReader memoryReader = new StreamReader(mysr);

                string xamlString = memoryReader.ReadToEnd();   //String of Xaml code
                memoryReader.BaseStream.Seek(0, SeekOrigin.Begin);

How do I recover xml from an SSIS String variable?


When I get my resulting xml from a webservice task, the response is not only well formed, but actually valid. When I put it into a string variable for passing it along to the next task in SSIS, the XPATH against it in the XML task it fails because what happens is the XML got munged as it went into a string. All the quotes got escaped and all the linefeeds and newlines ended up as \r\n, etc.

Is there a way to avoid this or recover from this without having to save the xml to file and then load the xml from the file for the next task?


How to Convert System.Drawing.Image Variable to Byte variable



I got a variable of type System.Drawing.Image and need to convert it to a variable of type byte so I can store the image in the database. Can someone show me how to do that in VB.NET code.

Thanks alot

Passing variable into function in SQL Server from Access



I am trying to call a function in SQL Server 2005 from Access 2003.  It works fine with the constant value, but fails with a variable of the same value.  This works:

strSQL = "SELECT * FROM functionName (constantValue)"
rs.Open strSQL, cn 

But when I use variable in the parameter, I get error.  I have tried creating parameter objects in Access, but couldn't execute it and don't know if this is a way to go anyway.

Would appreciate for any help or suggestion.


Thank you

Passing xml in javasript variable to server side



I am creating the new XML in javascript and assigning it to javascript variable and assigning it to html hidden field. But in server side I am not able to retrive its value when I debug hidden field shows [object object] in server side.

How can I solve the above mentioned problem? Is there any other better method exist?



Session variable worked on one Prod server but not on another server


We recently migrated a web app from one Prod server (Prod 1) to another server (Prod 2).  There is a session varialbe checked inside the page load event of the default master page. I have enabled session state both in web.config as well inside the pagedirective. The compilation went through without any issue. The web app is also running just fine on the server but the log file keeps logging a warning message. There is no run time error.

"System.Web.HttpException: Session state can only be used when enableSessionState is set to true, either in a configuration file or in the Page directive. Please also make sure that System.Web.SessionStateModule or a custom session state module is included in the <configuration>\<system.web>\<httpModules> section in the application configuration."

Any idea why this is happening on one server vs other? Am i missing any other config. settings? Appreciate your help.

The web app was developed using asp.net 2.0


ConfigurationFilter ConfiguredValue PackagePath ConfiguredValueType
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend