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:
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.
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?
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.
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.
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 :-?
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)
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
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).
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...
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 ?
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:
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?
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?
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.
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:
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?
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.
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.
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()".
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.
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,
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
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();
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?
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
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
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
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.
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.
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.',
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?
