.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 Query requiring two variables

Posted By:      Posted Date: September 07, 2010    Points: 0   Category :Sql Server
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????

View Complete Post

More Related Resource Links

SSIS OLEDB DataSource - Parameterized Query

Hi Team,We use SSI 2005 for one of our projects. We need to connect to the Oracle database and extract data. When we extract data from the source, we use the OLEDB Source and pass the SQL statement through an expression. We have a limitation of 4000 character when we use these expressions. To avoid this, we tried using parameterized queries as mentioned below:SELECT col1, col2, col3 ... col n FROM tab1 WHERE col1 > :PARAM1;When we try to assign the parameters, we end up getting an error as below."Parameters cannot be extracted from SQL Command. The provider might not help to parse parameter information from the command. In that case, use the "SQl Command from Variable" access mode, in which the entire SQL commands is stored in a variable"However, if use the SQL Server as a data source, I can assign the parameters. Could anybody let me know if this is a limitation from SSIS orif I am doing something wrong.Please suggest.ThanksAtukuri

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) {

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.

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

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?

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.



SQL Query Help - Aggregate Functions Across Several Variables


I have a table that looks like this….


Size                  BillCode           Qty      Weight

4                      A                     3          230

4                      B  &nbs

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!

Export SQL query from Execute SQL in SSIS


I am new to SSIS and I was wondering if it was possible to export a SQL query using Execute SQL query to an excel or text file.

All i need to do is export the results of

SELECT AccountID, BranchID, DepartmentID, ClientName, GroupNumber, PAREA, ProductCode, ProductDescription, SearchType, Quantity, ClientType, Filler,
VRM, VIN, [Transaction Date], TransactionValue
FROM MangoletsiHoldings
WHERE (SearchType = '4') AND ([Transaction Date] BETWEEN '01/08/2010 00:00' AND '05/08/2010 00:00')

to an excel or txt file, any help is much appreciated.



Why can't I execute a DELETE query in SSIS?


Select * from Table    ->  works

Delete from Table       -> throws error about no instance instantiated

SSIS SharePoint List Adapter - how to query Users & Groups?


I am using SSIS SharePoint List Adapter to load data in and out of my SharePoint Lists from within SSIS, and most of it is working fine.  However, I need to perform a lookup against the Users & Groups collection in SharePoint so I can retrieve the UserID and set the value when writing back to SharePoint.  Any help would be tremendously appreciated!


SSIS are there component to use query and then transform the table


Are there any components that I can use for query for instance after the flat file source is set up to grab all the values into table(column, column) then using the query to filter it out to transform data??


For instance my query look like this


                      LEN(CHGCURRQ1), 1) = '0' THEN CONVERT(int, SuBSTRING(CHGCURRQ1, 1, 8) + '0') WHEN SUBSTRING(CHGCURRQ1, LEN(CHGCURRQ1), 1)

                      = '{' THEN CONVERT(int, SuBSTRING(CHGCURRQ1, 1, 8) + '0') WHEN SUBSTRING(CHGCURRQ1, LEN(CHGCURRQ1), 1) = 'A' THEN CONVERT(int,


                      8) + '2') WHEN SUBSTRING(CHGCURRQ1, LEN(C

SSIS - Passing a date parameter to a SQL Query question again....


I need to pass a date string to my sqlcommand inside of my "datareader source".  I read some of the questions about setting expression variables but I can't seem to figure out how to do it.  I pasted the query below.  The table name contains a date in it so I need to pass that date as a string into my sqlcommand at runtime.  Do I create a variable with the entire SQL command in it or can I just create a single variable that holds the date and just reference that in my datasource reader sqlcommand property?  Can someone assist me with the syntax for creating the expression?

     pages_20101006.`date` AS full_date,
     pages_20101006.`cat` AS category_no,

Thanks very much,



Get list of package variables in SSIS code


Hi all,

i need to get list of package variable names during execution of a custom control flow task.

Best regards Crom

Building a query into a SSIS package


I have a simple package that imports data from an excel file to a 2008 R2 database.

here is a sample of the excel file

54            Siobhan Kennedy               Health

22            Peter OChahan                  Education

31            Ryan Harkins                     Finance


My table in the database looks like this:


CREATE TABLE [dbo].[Employee](

[EmployeeID] [int] NULL,

[FirstName] [nchar](50) NULL,

[LastName] [nchar](50) NULL,

[Department] [nchar](50) NULL)


I want to import the data into the database, splitting the name column into firstname and lastname using the SSIS package.

So far (on the data control tab) I have an 'Excel Source' Box and an 'OLE destination' Box.

I think i need to pick an option from the 'Data Flow Transformations' menu in the toolbox. Is this correct?




Variable types are strict, except for variables of type Object- SSIS 2008


Hi , I declared a user variable MINIODate  as string in package level and assigned this variable in an execute sql task 2008 , where my source is ado.net and sql command is Select CSTR(MIN(IODate) ) as MINIODate  from IOData ( This is source table available in MSAccess DB) , The above senario working fine in 2005. During the run time in 2008  i am getting the following error

[Execute SQL Task] Error: An error occurred while assigning a value to variable "MINIODate": "The type of the value being assigned to variable "User::MINIODate" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

SSIS - Variables sharing from child to parent package



I'm a having a problem in passing the value of a variable from child package to the parent package in SSIS.

Can anyone please help me??? Any process would be helpful..either using configurations or using scripting (c# or vb.net)..



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