Help Passing Complex Variable Types To A Web Service Task

Feb 23, 2007

I need a little help here and appreciate any insight into this issue.

I am building an SSIS package that retrieves data from a database to use in a web service task. So let me give you a little more broad overview of the package so you can understand how this is supposed to roll. A database is queried and those values are dumped into a recordset. A foreach loop uses each row of variables to call the web service and dump the returned values to another database. The first database holds a bunch of fields, but the four fields of interest are: a StartDate (DateTime), a StartFormat (single char), an EndDate (DateTime) and an EndFormat (single char). The output from the query of the first database is the input in the signature of a web service's .Load method. Sounds easy, right? Sure, why not?

Well I dragged the Web Service Task on to the pane and took a look inside. Lo and behold, I can hardcode the variables in for the web service or I can assign the inputs to package variables. How fancy, thanks Microsoft!

But that's where the difficulty begins. The method call for the web service looks like this: service.Load(string, int, GTTimestamp1, GTTimestamp2). The web service is expecting a string, an int, and two objects of the type GTTimestamp, which is a very simple class defined as this:

<System.Xml.Serialization.SoapTypeAttribute("GTTimestamp", "http://util.gtdw.pci.com")> _

Public Class GTTimestamp

Public calendar As Date

Public displayFormat As String

End Class

In the input pane for the Web Service, when I click in the value of the two GTTimestamps like I'm going to hardcode them in, another window pops up saying "Enter the values for complex type -in4" and the pane looks exactly like the previous pane with one very important exception: There is not place to check to assign the value from a package variable!!! Dang you Microsoft!!! I don't really understand why they would leave us out to dry on this... Oh, well, maybe they'll take care of it later...Time to work around it.

SSIS does allow you to create a variable of type System.Object, so after playing with the Web Service for a few minutes and giving up on that, I decided to create a script task that is supposed to create two GTTimestamp objects and assign them to two object variables in the package for passing to the WebService Task. The first challenge was to get the web service to play nice with the script. For those who have never done this, use a command prompt and the wsdl.exe to generate a .vb or .cs file to add to your script file using the right click, add existing item...

Once the file was accessable to my scripts, I created two GTTimestamp objects and assigned them to the Package variables of type System.Object. Running the package, I got this error:

"Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Type 'ScriptTask_8c868490237b4220b582bdc7c7a3ecae.GTTimestamp' in assembly 'VBAssembly, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' is not marked as serializable.

Not marked as serializable, huh. Okay, so I made the GTTimestamp serializable by adding the <Serializable()> before the class declaration. Then the error changed to:

The error is: Type is not resolved for member 'ScriptTask_8c868490237b4220b582bdc7c7a3ecae.GTTimestamp,VBAssembly, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null'.

And here I am, at a loss for what to do from this point. I'm a little lost, so I thought I'd stop and ask for directions. I'm sure I'm not the first to want to use complex variable types.

Only two options at this point. One is to try and store the GTTimestamp in the database and see if SSIS can deal with that. I'm not sure how to store objects in a database or if that is even an option for me, but it came to mind so it made it into this post. The other is to get this to work through the script above that I have run into a wall.

Again, any help is appreciated. Thanks for your time.

View 11 Replies


ADVERTISEMENT

Integration Services :: Passing Complex Type As A Variable In Web Service Task

Oct 5, 2011

When you pass a complex type (the one represented by class) to a web service the BIDS UI allows you to enter values for every field of that type as constants. But what if you want to pass a variable? Once again the UI allows you to specify a variable for that complex type parameter. But how to make this variable in SSIS?I understand it should have the type of Object. But how to specify what the runtime type of this object is? And how to assign all fields to that object?

View 6 Replies View Related

Web Service Task - Passing Variable As Input

Nov 24, 2006

Microsoft says it is possible but I just do not see how. Here is the
link to the help file where it said that variables could be pass as
input to web methods...I do not see the check box they mention on my
IDE.


Any help would be greatly appreciated.


Thanks,
Catherine

View 4 Replies View Related

Passing A SSIS Global Variable To A Declared Variable In A Query In SQL Task

Mar 6, 2008

I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:

DECLARE @DV INT;
SET @DV = (SELECT MAX(DateValue) FROM tblTG);
DECLARE @PV INT;
SET @PV = @DV - 1;

I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:

DECLARE @DV INT;
SET @DV = ?;
DECLARE @PV INT;
SET @PV = @DV - 1;


I have almost 50 references to these parameters in the query so a substitution would be helpful.

Dan

View 4 Replies View Related

Execute SQL Task: Variable Mapping Failed With Complex SQL Script. Please Help!

Jul 23, 2007

Hi all,
I'm working with Execute SQL task. Connection type: OLE DB. With the following settings, the task works fine:

Parameter mapping:



Code Snippet

Variable Name Direction Data type Parameter Name
User::InputFile Input NVARCHAR 0
User::DesiredOutput Input NVARCHAR 1
SQLStatement:



Code Snippet

exec [spu_CreateOutput] ?, ?

However, I want to put in some conditions so I modified the task as:

Parameter mapping:



Code Snippet

Variable Name Direction Data type Parameter Name
User::OutputFile Input NVARCHAR 0
User::InputFile Input NVARCHAR 1
User::DesiredOutput Input NVARCHAR 2

SQLStatement:



Code Snippet

if(? <> 'NotUsed')
Begin
exec [spu_CreateOutput] ?, ?
End

But the modification doesn't work. The following message was thrown:

Error: 0xC002F210 at CREATE OUTPUT, Execute SQL Task: Executing the query "if(? <> 'NotUsed')
Begin
exec [spu_CreateOutput] ?, ?
End
" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: CREATE OUTPUT

Can anyone tell me what I have been wrong with it? It seems that parameter mapping can only apply for a single select statement/function/procedure call :-?

Thank you,
GiaHi

View 5 Replies View Related

Passing Parameter To SQL Task Variable

Sep 25, 2006

I am trying to exectue SQL task as below by passing a parameter

If I try....

@v1 datetime

set @v1 = convert(datetime, ? ,103)

it fails with below error

" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

however the below code works well

delete from t1 where last_update = convert(datetime, ? ,103)

What could be the problem?

View 4 Replies View Related

Not Executing SSIS SQL Task By Passing Variable

Jun 11, 2008

Hi,

I am creating SSIS package which using Execute SQL Task to which I am supplying one .sql file code is

delete from Test where ID = @ID

I defined @ID variable to SSIS and also path where .sql file placed but i am not able to execute this package i am getting error like can not find C:@Path file......

As i got information that passing such variable to .sql you need ADO.net connection so I changed SQLSERVER Database connection string to ADO.net .... after that when i set hard core value for these variable i.e for @ID and @Path then it runs sccessfully but by setting parameter i am getting above error


Any suggestion that will be gr8 help for me

T.I.A

View 2 Replies View Related

Passing Object Variable As Input Parameter To An Execute SQL Task Query

Mar 29, 2007

I've encountered a new problem with an SSIS Pkg where I have a seq. of Execute SQL tasks. My question are:

1) In the First Execute SQL Task, I want to store a single row result of @@identity type into a User Variable User::LoadID of What type. ( I tried using DBNull Type or Object type which works, not with any other type, it but I can't proceed to step 2 )



2) Now I want to use this User::LoadID as input parameter of What type for the next task (I tried using Numeric, Long, DB_Numeric, Decimal, Double none of there work).



Please give me solutions for the above two..



View 6 Replies View Related

Sql Types - Simple SQL Server Queries/handling Variable Types

May 26, 2005

SQL Server 2000, ASP.Net 1.1

I've been writing this stuff for a while, and can't seem to come to the
conclusion of how I should be retrieving data and assigning this data
to variables.

Since i'm using SQL Server, I'm convinced that I should be using the
datareaders GetSqlDouble (or whatever) function, but this would mean i
need my local variables to be one of the SQL types.  The problem
with that is, that there will have to be lots of conversions done by me
to be able to use a SQL type in my application.

For instance, I have a class where i'm retrieving dates.  In order
to retrieve them correctly (Null values included), I need to retrieve
them with GetSqlDateTime(), then when it comes time to display the date
in a table, i must first check for nulls, then convert to a
string.  This seems to be very cumbersome.  Would I be better
off just using GetDateTime(), and the .ToString method, and ignoring
Sql Types all together?

so, basically, how are you guys using your sql server data?  with
the supplied sql types, and doing all of the post-processing work
manually?  I feel like i'm having trouble conveying my
issue...hopefully someone knows what i mean....i'd just like some
direction to save trouble in the long run, since i feel like there's
got to be a better way...

Confused!

Thanks,
JJ

View 1 Replies View Related

Passing A Variable Value (Package Scope) To A DTS Package Embedded Into Execute DTS 2000 Task

Jul 19, 2007

Hi friends,

I have a for each loop that populates from a set of flat files into a Sql Server table, I run the Flat file Import via a dts package embedded into Execute DTS 2000 Task. I want to pass the Sourcefile Name that is fetched by the For Each Loop to assign it Global Variable in DTS. how this can be made ?



Thanks

Subhash Subramanyam

View 4 Replies View Related

SQLXML, Complex Types, And WSDL

Apr 19, 2004

Hello -

I've used SQLXML to generate WSDL from a sample stored procedure that returns customerID and contactName from the 'northwind' database based on a runtime-supplied parameter.

However, the generated WSDL doesn't explicitly name the two columns (customerID and contactName) that the procedure returns. I've tried all of the possible combinations of 'row formatting; and 'output as' when configuring the virtual name. Instead, I get the following:

- <xsd:element name="TestResponse">
- <xsd:complexType>
- <xsd:sequence>
<xsd:element minOccurs="1" maxOccurs="1" name="RobTestResult" type="sqlresultstream:SqlResultStream" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>

To parse the results, I would have to cast the information correctly, which requires some hard-coding. Does anyone have any suggestions on how to force the WSDL file to correctly list out the individual elements in the result set?

Many thanks!

Robert

View 1 Replies View Related

Passing Variable Value Through Environment Variable

Feb 15, 2008



Hi All!

I have a parent package that contains two children... The second child depends on the succes of the first child.

THe first child generates a variable value and stores it in an Environment variable ( Visibility - All ) ...After the first succeeds, the second will start executing and will pick up the variable value from environment variable( through package configuration setting )...

Unfortunately, this doesn't work...As the second child picks the stale value of the environment variables...Essentially it assigns variable value not after the first child is finished, but right at the beginning of parent execution...

I tried to execute coth children as Out Of Proc as well as In Proc...The same

Would anybody have an idea how to resolve this problem?

Thanks in advance for any help!

Vladimir

View 5 Replies View Related

SSIS Web Service Complex Type Inputs

May 21, 2007

Hi,

I am trying to make a call to a third-party web service in my SSIS package. The request has custom complex data type as the parameter. As has been pointed out in this forum before, the Web Service Task only lets you assign the outside parameter from a variable, not the internal parameters needed to create the complex data type.

To be more specific, the web service input wants a 'ContactSearchRequest' parameter. I can assign this from a variable. If I click on the 'value' field under the 'Input' section for the web service task, it shows me that the 'ContactSearchRequest' data type is made up of the following:

contactId - long
numResults - int
offset - int
passKey - string
searchParam - string
sortType - int

Unfortunately, I can't assign these internal parameters from a variable, at least not through the web service task interface.

My next thought was to create a variable of type 'object' and then set it in a script task prior to calling the web service task. However, I'm not sure exactly how to do this. How will my script know about the class definition of 'ContactSearchRequest'? Do I just create a class called 'ContactSearchRequest'?

I've used this same web service in a .NET C# project and after I imported the web service, visual studio knew all about the custom data types. How do I do something similar in SSIS?

Of course, the easiest solution would for Integration Service to allow me to set those internal parameters via variables, but we're apparently not there yet.

Any suggestions?

Thanks,
Trey

View 8 Replies View Related

Web Service Data Source Complex Parameters

Feb 8, 2007

I am building a report that takes a web service as data source. This web service does not take primitive types as parameters. The signature is something like this:

<soap:Body>
<GetXml xmlns="myreports.ws">
<root >
<TP Name="string">
<B Name="string" Surname="string">
<SA xsi:nil="true" />
</B>
<B Name="string" Surname="string">
<SA xsi:nil="true" />
</B>
</TP>
<TP Name="string">
<B Name="string" Surname="string">
<SA xsi:nil="true" />
</B>
<B Name="string" Surname="string">
<SA xsi:nil="true" />
</B>
</TP>
</root>
</GetXml>
</soap:Body>


My question is: how do I pass the xml to the web service from reporting services.

I have seen examples with simple types. They do it with this query:

<Query>
<SoapAction>myreports.ws/GetXml</SoapAction>
<Method Namespace="myreports.ws" Name="GetXml"></Method>
<Parameters>
<Parameter Name="Parameter1">
<DefaultValue>some value</DefaultValue>
</Parameter>
</Parameters>
<ElementPath IgnoreNamespaces="True">
GetXmlResponse{}/GetXmlResult{}/diffgram{}/NewDataSet/Element{@Attribute1,@Attribute2}
</ElementPath>
</Query>

But there are no example on using xml directly instead of parameters. Can anyone help me? Where do I put my xml?



View 3 Replies View Related

SSIS Script Task Alters Package Variable, But Variable Does Not Change.

Oct 25, 2006

I'm working on an SSIS package that uses a vb.net script to grab some XML from a webservice (I'd explain why I'm not using a web service task here, but I'd just get angry), and I wish to then assign the XML string to a package variable which then gets sent along to a DataFlow Task that contains an XML Source that points at said variable. when I copy the XML string into the variable value in the script, if do a quickwatch on the variable (as in Dts.Variable("MyXML").value) it looks as though the new value has been copied to the variable, but when I step out of that task and look at the package explorer the variable is its original value.

I think the problem is that the dataflow XML source has a lock on the variable and so the script task isn't affecting it. Does anyone have any experience with this kind of problem, or know a workaround?

View 1 Replies View Related

Consuming WCF Service With Complex Data Type In SSIS

Oct 9, 2007



Hi,

Need a guidance on consuming the WCF service with complex type in the SSIS package.
I have a WCF service with complex type inside the complex type (Nested complex type) in the web method as an argument. When I try to use this WCF service in the SSIS web service task, I get an error "The web method has unsupported arguments".

I am able to consume the WCF service with the web method having Complex type and simple/prmitive type in side that as argument.

For example:

The web method in WCF service which accepts the argument as comlex type say "Employee" whose structure is:

Employee
{
FName String;
LName String;
Age int
}

It is possible to consume this WCF service and pass the arguments.

But when the Employee complex type is changed to have one more complex type in side it it give the above mentioned error. The Employee type is modified as:

Employee
{
FName String;
LName String;
Age String
Type EmployeeType;
}


EmployeeType
{
type string;
}

Now I get the error Web Method has unsupported arguments.

Can this be done in SSIS?

Virendra

View 3 Replies View Related

Table Variable And User Defined Types

Oct 30, 2003

This is part of the codes of a stored procedure:

-- Create tables variables
DECLARE @_tab_PRNumList TABLE(
PRNum udt_PRNum
)

However, it cannot be compiled coz of this error:
Error 2715: Column or parameter #1: Cannot find data type udt_PRNum.

This user-defined data type is created in a user database only.

I tried to created the user-defined type into BOTH tempdb and master but same error.

How can i resolove it?
Or it is impossible to use user-defined datatype for table variable?

In the past,
i use this method:
CREATE TABLE #PRNumList (
PRNum udt_PRNum
)

and cerate udt_PRNum in tempdb only could solve it.

But now i want to use table variable.
It seems NOT work for my situation.

Any idea?

View 1 Replies View Related

Changing Data Types Of A Variable In An Expression

May 18, 2006

Greetings once again SQL friends,

Sorry to bug you with my silly questions but this little problem has been driving me crazy for the last hour or so!

I am attempting to change the data type of a variable in an expression but I keep getting an error saying that the expression can not be evaluated.

The syntax I am using is as follows :

(dt_str) @[User::MAX_OFFER_PRICE_ID]

My variable MAX_OFFER_PRICE_ID is type int32 and I am attempting to cast it as a string.

What am I doing wrong?!

 

Thanks for your help in advance.

View 7 Replies View Related

Passing SAML Token From Security Token Service To Reporting Service

Mar 28, 2007

Hi,

I am using SQL Server 2005 Reporting Services. I want to make it secure. I am also using WCF services and made them secure using Claim based System.Identity Model.

I want to apply same claim based model to Reporting Services.

How can I do that?



Amit

View 2 Replies View Related

Amazon Web Service Call With Web Service Task

May 4, 2006

With the new improvments to the web service task, we can now use variables as arguments in web service calls. I am trying to setup a call to the amazon web service ECS. I am trying to do a simple sellerlookup. I have played with the settings and gotten nowhere. I get one of two error when I try to execute. I can always use a scripting task or write my own task, but I would like to use the built in task if it is possible. Has anyone used AWS with SSIS?



[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Object reference not set to an instance of an object.. at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".

or


[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Method 'ProxyNamespace.AWSECommerceService.SellerLookup' not found.. at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".

View 6 Replies View Related

Passing Value To A Variable

Aug 9, 2007



Hi,
This is pretty simple but I am doing somethin really stupid somewhere, I am trying to pass a value to a variable declared in a package using some SQL code, I declared a variable "var1" and assigned the scope to the package , then I dropped a execute SQL task within the package and wrote a simple sql code that would get the maximum id from a table, "select max(id) from table" , now I want to assign the variable this max value.so in the parameter mapping tab I select the variable give it a parameter name of 0 and size of -1 along with direction of "output". everythin looks simple but when I try to use this variable value in my next execute sql task it behaves weirdly, I am trying to update anothet table using this variable value, so I add another task and put this code in there " update table2 set id = ? " , and on the parameter mapping tab I set the direction as input, however when I look at the column it updated th column with a weird number , xomethin like "230072408" which doesnt mean anything. I know that tha max value of ID is 10.
Please help.

Thanks

View 13 Replies View Related

Passing Variable To SqlCommand

May 18, 2007

 Can't seem to pass a variable to the sql statement. I'd appreciate any help. I'm trying to pass pColName to  CommandText = "ALTER TABLE tb_roomInfo ADD @rColName  varchar(50);";Doesn't seem to work though. CODE:  [WebMethod]    public string addCol(string pColName)    {                    SqlConnection cnn = new SqlConnection(connString);        try        {                        cnn.Open();            SqlCommand cmd = new SqlCommand();            cmd.Connection = cnn;                       cmd.CommandText = "ALTER TABLE tb_roomInfo ADD @rColName  varchar(50);";            SqlParameter rColName = new SqlParameter("@rColName", pColName);            cmd.Parameters.Add(rColName);            int i = cmd.ExecuteNonQuery();            cnn.Close();            return "Insert Successful";        }        catch        {                        return "Insert Unsuccessful";        }    }

View 3 Replies View Related

SQL WHERE Clause Passing In Variable

Jun 14, 2007

Hi All, Would somebody be able to help me from pulling my hair out!??I have a form with a radiobuttonlist. I would like to change my select statement depending on what radiobutton value is selected.E.g.SELECT * FROM table WHERE <<variable from radiobuttonlist>> LIKE 'Y'So,if radiobutton value selected = 1, it will select * from column A  in the dbif radiobutton value select = 2, it will select from column B in the dband so on...  Am i attempting to do the impossible?Thanks All, 

View 4 Replies View Related

Passing Datetime Variable To A SP

Jan 18, 2008

Hello,
I have a SP that recevies a date value for a users date of birth called "dob".
However when passing it into the class which contains the Stored procedure it gives an error.
 Below is my code
please advice
Thanks
Ehi
 
  1
2 command.Parameters.Add(new SqlParameter("@usernames", SqlDbType.Int, 0, "RegionID"));
3 command.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar, 20, "username"));
4 command.Parameters.Add(new SqlParameter("@First_Name", SqlDbType.VarChar, 50, "First_Name"));
5 command.Parameters.Add(new SqlParameter("@Last_Name", SqlDbType.VarChar, 50, "Last_Name"));
6 command.Parameters.Add(new SqlParameter("@dob", SqlDbType.Date, 50, "dob"));
7
8 command.Parameters[0].Value = 4;
9 command.Parameters[1].Value = "username";
10 command.Parameters[2].Value = "First_Name";
11 command.Parameters[3].Value = "Last_Name";
12 command.Parameters[4].Value = DateTime.Parse(dob.Text);
 

HERE IS THE ERROR MESSAGE 
 Compiler Error Message: CS0103: The name 'dob' does not exist in the current context

Source Error:



Line 40: command.Parameters[2].Value = "First_Name";
Line 41: command.Parameters[3].Value = "Last_Name";
Line 42: command.Parameters[4].Value = DateTime.Parse(dob.Text);
Line 43:
Line 44: int i = command.ExecuteNonQuery();


Source File: c:inetpubwwwrootcellulant1App_Codesignup_data-entry.cs Line: 42  

View 7 Replies View Related

Passing Value To A Variable From Database

Apr 25, 2008

I am trying to get data from a database through a select statment and I want to pass the return query to a c# string variable. Any idea how can I do this?
 

View 2 Replies View Related

Passing Variable To Sql Statement

Apr 28, 2008

could anyone please help me to resolve this issue?
here's my sql query which retrieve last 3 month data
t.execute(SELECT * tbl1 where nmonth >= datepart(mm,DATEADD(month, -3, getdate())) or nmonth <=datepart(mm,getdate()) and empno='"+emppip+"'")
now instead of passing 3 in this query(datepart(mm,DATEADD(month, -3, getdate())) )
i need to pass a variable to retrieve data based on user requirements.
i tried this way,
dim mno as n
mno=4
t.execute(SELECT * tbl1 where nmonth >= datepart(mm,DATEADD(month, -'"+mno+"', getdate())) or nmonth <=datepart(mm,getdate()) and empno='"+emppip+"'")
its not working.
can i achieve this using stored procedure? or can i directly pass a variable to sql synatax?
thanks for any help   

View 8 Replies View Related

Passing A String Variable To Sql

May 11, 2008

hi there,
i am trying to pass a string which contains a string,
here is the code which is wrong :
{string sqlcommand = "select pnia.pnia_number, pnia.user_name, pnia.date_pnia, pnia.user_pnia, problem.problem, gormim.gorem_name, status.status_name from pnia,gormim,problem,status where (pnia.status='@p1' and status.status='@p1' and pnia.problem=problem.problem_num and pnia.gorem=gormim.gorem)";
OleDbCommand cmd = new OleDbCommand(sqlcommand,con);OleDbParameter p1 = new OleDbParameter("@p1",this.DropDownList4.SelectedItem.Value.ToString());
cmd.Parameters.Add(p1);
}
the problem is that the sql compailer doesnt take the parameter (@p1) as a string
if someone could help me with that it would be great !  tnx

View 2 Replies View Related

Passing A Query Into A Variable

Aug 9, 2007

What is the easiest way to pass a value into a variable...
TSQL...

declare @@test int

--select count(*) from authors
select count(*) into @@test from authors

View 1 Replies View Related

Syntax For Passing Variable-usp

May 20, 2007

hi guys what is the syntax for using the passing variable part into the name of a table in a store procedure. in particular: (assume already declared the variable periodseq.


select *
into Temp_Usage_@periodseq
from Master_usage
where Master_usage.PERIODSEQ = @periodseq


in particular the Temp_Usage_@periodseq line of code, how do i "add" the periodseq (which is a number) to the end of the name of Temp_Usage, i.e: Temp_Usage_112
is the syntax an & like Temp_Usage_&@periodseq?
Cheers
Champinco

View 1 Replies View Related

Passing A Variable To A SQL Statement

Mar 10, 2008

I've been coding a few years and SSIS makes me feel more stupid than any program I've ever used.
I've read BOL and bought a book. Can't say either one has really helped.

I'm still a complete idiot after one week of working with it. I apologize for asking so many stupid questions.

What I'm trying to do now is parameterize a SQL statement.

I have a variable that's a string. I have a DataFlowComponent as a data source. I find references all over the Internet and this forum to something called an "ExecuteSQLTask" but I sure can't figure out what that is. In my toolbox I have data flow sources for DataReader, Excel, Flat Files, Old DB, Raw file, and XML but no Execute Sql Task.

Anyway SELECT * FROM TABLE WHERE COLUMN='Value' in the SQL Command property is simple enough. Now I want 'Value' to be a variable. You know, like in T/SQL DECLARE @Foo VarChar(25). Creating the variable is easy as pie.

I have found at least 10 different examples of specifying variables on the web, all of which claim to be SSIS examples, Is it User::Variable? @[User::Variable]? @Variable?

I want to read rows from a table, with a a variable value in the WHERE clause, and pass them to the fuzzy lookup task.

Is my approach fundamentally flawed in some way?





View 25 Replies View Related

Passing A Variable To SQL QUERY

Oct 24, 2007



Hi
I am extracting data from Oracle via SSIS. There are three smilar schemas from where the data has to be extracted.
Say My query is " Select * from abc.dept" where abc is the schema name. I want to pass this schema name through a variable and it should loop as there are total 3 schemas. There is a table which provided list of schemas.
Can somebody please guide me how to do this. There are multiple references of this table is SSIS package.

View 11 Replies View Related

Passing Variable On To Next Record

Jan 28, 2008

I am running a query in with my daily import job that lets me know if a duplicate record was imported into the system. If so I want to setup my email table to mail me the following information. My code works fine except the value of the variable @ID is only giving me the first record in the set. How do I pass it on to the next record? I get the right number of rows inserted into the email table but the @ID variable is the same for all 3.

Drop Table #SER1

Select id#,

Colx=Count(*)

Into #SER1

From Business

Group By id#

Having Count(id#) > 1


Declare @ID Varchar (4)


Select @ID = ID# from #SER1


INSERT INTO Email

( [To],

[Subject],

[message],

[Table],

TableUKey,

TableSource

)

SELECT

'Firstname.Lastname@Company.com',

'Duplicate ID# Imported',

'ID# ' + @ID + ' has been imported multiple times ' + ' on ' + cast( getdate() AS varchar(30)) + '. You need to remove the record and rerun the import.',

'Daily Import',

@ID, 'Daily Import'

FROM #SER1

View 1 Replies View Related

Passing Parameter Value To Variable

Jun 1, 2007

Hello,

I can't figure out how to pass values from report parameters to a variable in custom code.
I have a Report Parameter called Parm1 of type string. It contains the following:

Label: Value:
Labell1 Value1
Label2 Value2

In custom code I have declared a variable thus:
Public Dim ParmValue As string = Parameters!Parm1.Value

What I want is this: When a user select a report, they will have to choose a parameter from a dropdown-list (created automatically when creating a paramete). The chosen parameter will be passed to the variable ParmValue which I use as one of the parametes in a text-box like this:
=code.InstanceName.MethodName(ParmValue)

InstanceName is the instance of a class from a dll I reference.
It works fine if I hard-code the paramter like this:
Public Dim ParmValue As string = "SomeString" instead of:
Public Dim ParmValue As string = Parameters!Parm1.Value

The error I get is this:
[rsCompilerErrorInCode] There is an error on line 0 of custom code: [BC30469] Reference to a non-shared member requires an object reference.

I don't know if I'm at all on the right track here.
Can someone tell me what I'm doing wrong?

Thanks
/Peter

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved