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

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

SSIS: How to set the values of the variables, other ways of setting the values of the variables?

Posted By:      Posted Date: September 14, 2010    Points: 0   Category :Sql Server
Hi Gurus, Is this the only way in setting the values of the variables. Please see steps below: 1. Build the SSIS Project. From the package's property pages, Deployment Utility, set the CreateDeploymentUtility to TRUE (It will create a Deployment Folder and inside the folder 3 files will be created a) SSISDeploymentManifest b) dtsConfig c)dtsx ) 2. Double click the SSISDeploymentManisfest file. A Package installation will be set up. 3. Follow the steps until you reach the Configuration Packages: Edit package configurations window. 4. From the Configuration part, set the values from the Values(column). 5. To Finish. Is this the only way of setting the values of the variable?

View Complete Post

More Related Resource Links

Declaring variables with values

In SQL Server 2008, I know I can declare variables as follows: DECLARE @var INT = 1; Instead of: DECLARE @var INT; SET @var = 1; Is the first example ANSI compliant?  just wondering in case we ever have to port some code.  Also, since this is really the first time I've had to concern myself with ANSI SQL standards, what's the best way to stay informed of them?  Kind of a noob question, I know, but your input is appreciated. Clint

Populate a Data Flow Task's variables with values from a .dtsConfig file?

I have an SSIS Project to import exchange rates from an XML file into SQL. The project works when I have certain values hard-coded into variables, such as the URL of the XML File, proxy username and passwords etc. I decided to put in a .dtsConfig file, and have it update the values of the variables in the data flow task. So, one of my SSIS Packages in this solution is called ECBDailyRates.dtsx. It contains one control flow, which is a data flow task. The data flow task is a script task which does some XPATH on an XML file, and then provides outputs to an OLE DB Destination, which in turn puts the data into a table in SQL. That much worked before I put in the dtsConfig file. There are a few variables declared inside the data flow task, such as; PricesXMLUriDaily, String ProxyAddress, String ProxyAddressPort, Int32 etc. etc. These are now being updated from the dtsConfig, which is below. It was my understanding that SSIS would run through the config, and update any of the variables as required, and then run my data tasks. However when SSIS runs my script task, none of the variables have been populated, and so it falls over... as it were :) <?xml version="1.0"?> <DTSConfiguration> <DTSConfigurationHeading> <DTSConfigurationFileInfo GeneratedBy="...." GeneratedFromPackageName="dim_Institution" GeneratedFromPackageID="

Crossing values of Session variables


Hi all,

We are working in a web application using Session variables to store the clients ID to operate with them in all app (making orders, reports, etc....) but randomly users get the client ID of another user in any moment using the application.

I have tried to solve it in a first step adding to system.web section in the web.config file this:

<httpRuntime enableKernelOutputCache="false"/>

No changes after this. I have tried another possible solution, changing the SessionState to StateServer:

<sessionState timeout="30" mode="StateServer" stateConnectionString="tcpip="></sessionState>

ASP.NET State Service is running in server normally, It began run just before this last change, but we have not solved the problem either with this solution.

The server is Windows Server 2003 Standard Edition SP2 32bits running IIS 6.0, and the application is made with Framework 2.0

Are we doing wrong or forget something?


Setting Managed MetaData default values on site creation

I am working on a project where the requirement is to have specific document content types.  I have therefore create a site columns feature which creates all my columns, I have a Site Contentypes feature which creates all my content type and I have list templates based on my content types.  I also have created a couple of Site definition features which create specific list instance based on my list template As part of my content types I have created a few Managed Metadata fields which I wanted to set defaults for on the creation of my sites.  I have therefore created a feature which is right at the end of the WebFeatures section in my ONET.xml.  This features enumerates through all lists, looking for specific column names(managed metadata columns) and looks to set these to specific values.  The code to set the default value on the field is: TaxonomyField documentCategoryField = (TaxonomyField)library.Fields["Document Category"]; if (documentCategoryTerm != null) { documentCategoryField.DefaultValue = documentLibraryDefault.ToString(); documentCategoryField.Update();                                                     }                         &n

Comparing Null Values(from variable) in SSIS

Hi All I need to create a script that checks to see if a variable value is NULL, if it is then it executes the next SQL Task and if not then the package has finished. I have this in a SQL 2000 DTS package script: Function Main() If isnull(DTSGlobalVariables("NextLoadDate")) Then Main = DTSStepScriptResult_DontExecuteTask Else Main = DTSStepScriptResult_ExecuteTask End if End Function As you can see, all I want to do is check to see if a variable is null then don't execute the next task. But i cannot get it to work in a SQL 2005 SSIS package, so far I have created a script and come up with the following: Public Sub Main() If Dts.Variables("NextLoadDate").Value.ToString <> "" Then Dts.TaskResult = Dts.Results.Success Else Dts.TaskResult = Dts.Results.Failure End If End Sub Any Ideas? Thanks BigGopher

Creating SSIS Package Variables Programmatically

Hi There, I am fairly new to SSIS.  I am creating a number of custom SSIS Tasks that I want to be able to share information between at runtime.  I figured that the best way to do this was through package variables, as in the Execute() method each custom task has access to the package variables through the VariableDispenser object.  I have managed to read variables at runtime that I create in the designer, however I am having trouble creating and writing to them.  I have created a very simple custom task below that I would expect to create the specified variable... [DtsTask(DisplayName = "TestVariableWriter")] public class Variab : Task { public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction) { try { string varName = "TestVariable"; string varValue = "TestValue"; Variables vars = null; variableDispenser.LockForWrite(varName); variableDispenser.GetVariables(ref vars); if (variableDispenser.Contains(varName)) vars[varName].Value = varValue; else vars.Add(varName, false, string.Empty, varValue); vars.Unlock(); return DTSExecResult.Success; } catch (Exception ex) {

SSIS Query requiring two variables

I have a query in which I need to use two variables. How do I implement this in SQL task editor for the intake of two variables????

Importing Data from Excel into SQL Server using SSIS: some datetime values appear as NULLS How to Re

I created a Package in the Business Intelligence studio to Import data from Excel file  into SQL Server 2005 using a Excel Source and a OLE DB Destination that uses a data convertion transformation before it reaches the destination a mjority of the data is copied over. However i am having 2 Issues. 1. In the Date field some of the values appear as Null in SQL Server 2. I need to change the format of the date in Excel from dd/mm/yyyy to mm/dd/yyyy before inserting into SQL Server if Possible. I am not sure of the solution for Ques 1 but i attempted using a script task for #2 It did not work. Please Advice what the best way to proceed Thanks.

Using User Variables (ONLY) in SSIS Conditional Split

After considerable effort I have not been able to perform a successful conditional split on a user variable. The idea is that I am reading a flat file, and I place the number of rows read into a variable (variable A). I then do some data conversion and perform another row count (variable B) I would like to do a conditional split if the value of variable A <> value of variable B. I am not referring to any of the query columns in making this comparison. It is always evaluating to the value of the variables before the package is run. For example if variable a=0 and variable b=0 at the start of the package, the conditional split evaluates to true (since 0=0) What I am failing to see is a's value getting updated and b's value getting updated in the conditional split. I have added watches for the variable and they are updating, but the boolean a==b is not. Any help is appreciated.

what is the purpose of "evaluateasexpression" in SSIS variables? I mean when I need to set it as TRU

Friends, I have some SSIS package which is using variables. I built an expression for some purpose and I am using the following expressin in my ssis package. "_GROUP" + (DT_WSTR, 2) (((DT_I4 )(SUBSTRING( @[System::MachineName] , 12, 3 )) + 1)/2) + "_" The purpose of this is to get a group number basing on Machine name (taking the last two digits in the machine name) and dividing that number by 2. I am getting data conversion error if evaluateasexpression is set to TRUE. Can some tell me why? Thanks in advance.

HELP! Setting field values - CONVERTING ARTEMIS SQL TO T-SQL 2005

I have the following statement written in ARTEMIS (an ancient SQL project management product): What would be an equivalent code syntax within SQL Server 2005's T-SQL? I am new to Sql Server and have not been able to understand the online help... set in tishead use stmeng1 set if xshopord exi in stmeng1 xbusunit=busunit if busunit present xcustnam=custname if custname present xopnlt=openlett if openlett pre xengrrel=engrrele if engrrele pre xlineup=lineup if lineup pre xlinent=lineupne if lineupne pre xmfgship=mfgship if mfgship pre xsubmodl= modelcod if modelcod pre xmpl=mpl if mpl pre xcwantdt=custwant if custwant pre end end

Setting SharePoint 2010 Form Fields Using Query String Variables


Hi All,

I have a requirement to populate sharepoint form field value with query string while uploading document to document library.

While uploading the document to document library, I want to populate claim number available from query string in a pop up page i.e in my case EditForm.aspx, Located at http://servername:port/sitename/documentlibraryname/forms/EditForm.aspx.

Additional Information:

  • Created document library and added custom columns like claim number etc..
  • Created a view to filter uploaded documents as per claim number.

Server Details:

  • Windows Server 2008 R2
  • SharePoint 2010
  • SQL Server 2008 R2

I have written javascript for the same, but not sure where exactly to add the code in EditForm.aspx page.

Any help on this would be greatly appreciated.

Thanking you in advacne.



start SSIS package via script task with package configuration via variables



it is possible to start ssis packages with dtexec and set variables as package configuration like this:

dtexec.exe /SQL "\mart1\import_fact_calls" /SERVER "R08" /SET \package.variables[FullImport].Value;True

Now I'm trying to start a package via script task with this code:

Public Sub Main()
    Dim pkg As New Package
    Dim app As New Application
    Dim pkgResults As DTSExecResult

    If Dts.Variables("package").Value <> "" Then
      pkg = app.LoadFromSqlServer(Dts.Variables("package").Value, "R08", "USER", "PASS", Nothing)
      pkgResults = pkg.Execute()
    End If

    Dts.TaskResult = ScriptResults.Success
End Sub

How can I add a package configuration like the one with dtexec to the script code?

I like any ideas, links, docus ... Thanks!

Setting Values to dropdown in Repeater Dynamically


Ok So I have a Dropdown in a repeater, which currently has a drop down linking to a single datasource which just 10 items in it..so all the drop downs will have 1-10 as options.

Now I need to change this since each item in the repeater (i.e products) could have a different quantity.

This is what I currently have

<asp:Repeater ID="rptEventsList"  DataSourceID="srcServicesList" runat="server" >
        <input type="hidden" name="hidden<%# Eval("displayOrder").ToString()%>" value="<%# Eval("itemCode").ToString()%>" />
        <input type="hidden" name="price<%# Eval("displayOrder").ToString()%>" value="<%# Eval("unitPrice").ToString()%>" />
        <asp:HiddenField ID="hdnItemCode" runat="server"  Value='<%# Eval("itemCode").ToString()%>'/>
        <td><asp:DropDownList  ID="qtyList" AutoPostBack="true"  OnSelectedIndexChanged="cblServicesLi

SSIS - Lookup Percentages values change


Good day, i need help please.

The problem i have is that a percentage colume in my table, when i sum the total = '100.0000000'

This fine, but when i insert in SSIS - Lookup & i preview the data in Lookup task the Total = '100.072' which makes my calculations incorrect.

is there something i need to do to keep it in its original sum - but i view my decimal places in advanced properties decmal(38,12) so this should be fine.

Is in the query that i have to perhaps force it to be 100.0000, how?   

Can somebody please direct me on what i should do



Setting Session variables at the time of login authentication


Dear Rahul,

I am having a website in which I am setting nearly 20 Session variables at the time of login authentication. ( Ex : Name, EmpID, Dept, Location, Email...).

Can u please tell which is one of the following is the better way to get Login user details 

 1) Through session variables at the time of login 

 2) In each and every page by calling the Stored Procedure to get login user details


Regards >> HemaBhushan 


Setting Task Field Values in Custom .aspx Edit Form and accessing through ExtendedProperties


Since we're using WSS 3.0 and not MOSS 2007, we have to create and use ASP forms for any custom Workflow Task Edit pages instead of being able to use InfoPath forms (which would be nice...maybe some day we'll step up to MOSS). The custom Workflow Edit Pages are used for custom Task Content Types that we create so that our Workflow Tasks can have additional fields beyond the standard Workflow Task fields. Once you've collected the values for the Task fields from the user on the ASP Workflow Task Edit form, the typical procedure is to create a hashtable and fill the hashtable with key/value pairs that represent the names of your Task fields and their associated values, as such:

// This function is called when the user clicks the Save/Submit button
// on the Custom Task Edit ASP form
public void btnSubmit_Click(object sender, EventArgs e)
  // Create a hashtable that will be used to update the values of task fields
  Hashtable taskHash = new Hashtable();
  // For each field, create two values in the hashtable. If the field name 
  // matches a field in the Task content type, the field name will not be 
  // searchable as a key within the ExtendedProperties hashtable. So 
  // create a second field that is a duplicate of the first but having a 
  // name that doesn't exactly match an existing field in the Task 
  // content type. That way it will be a searchable ke
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