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
ADVERTISEMENT
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
Jun 11, 2007
Hi:
Can someone help me with a SSIS package that would consume from a Web Service (in fact two of them) and then load the data into SQL Server. I currently have Web Service task which connects to ForEachLoop task, and inside the loop task, I have a DFT. I am thinking, I would need to call the webservice utilizing the Web Services Task, and then store the output in a Full ResultSet variable. In my loop, I would like to loop thru the resultset, and store the data into SQL server. Inside the DFT, how would I construct this mechanism? Also, is this a good way to consume from a Web Service and then populate SQL Server? Are there any alternate ideas on this? Any documentation on this yet? Thanks.
View 7 Replies
View Related
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
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
Mar 7, 2007
We have some columns in a table where the date is stored as 19980101 (YYYYMMDD). The data type for this column is NUMBER(8) in Oracle.
I need to copy rows from Oracle to SQL Server using SSIS. I used the Data Conversion transformation editor to change it to DT_DATE, but the rows are not being inserted to the destination.
On Error, If I fail the component, then the error is :
There was an error with input column "ORDER_DATE_CONV" (1191) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
Regards
RH
View 3 Replies
View Related
Apr 16, 2006
Hi, I have a scenario in which an application generates events upon updates to the database, which need to be reliably transmitted to consuming applications running a) other SQL Server instances and b) as remote web services.
Now,
1) I am planning to use SQL Server Notification Services to generate these events, and allow the consuming applications to subscribe to them.
2) The events will be sent to a ReceiveEvent service, which will then process the event and put it in the respective queues for the Subscriber (consumer) applications.
3) The services representing these consumer applications will then get activated, and sent the message across directly to the service broker on the consumer side in case of situation a - where there is a SQL Server on the other side
4) In case the other side is a web service, I plan to make a SOAP request directly from my queue handler, and depending on the response received, decide whether to commit or roll back that particular transaction.
Does this seem like a good solution? I am very new to the Service Broker, and I am still getting my hands dirty. Any comments/suggestions would be very highly appreciated!
Regards,
Siddhesh
siddhesh@persistent.co.in
View 3 Replies
View Related
Oct 30, 2007
Hi,
I'm trying to replicate a SQL join across two seperate data sources in SSIS. If I were to write SQL to do this, it would be as follows:
SELECT Costs.CostRateEntryId,
Costs.UserId,
Costs.HourlyRate * 8 AS DailyCostRate,
Dates.DateKey,
Dates.ActualDate,
FROM Costs
INNER JOIN Dates ON Dates.ActualDate >= Costs.EffectiveDate AND Dates.ActualDate <= Costs.EndDate
Unfortunately, as the tables 'Dates' and 'Costs' are in two seperate SQL2005 systems, I can't really do this. I was hoping that it could be achieved in SSIS, but I cant seem to find any way that I can do a join that's <= or >=.
Can anyone help?
Thanks
Jeremy
View 7 Replies
View Related
Mar 9, 2006
Hi,
Anyone who can tell me why I get this error !
I can connect to Integration services on the server from another client.
Pls help
//T
View 10 Replies
View Related
Jul 19, 2006
Got above error on clustered sql2k5 x86 when connect to SSIS, any solution?
View 2 Replies
View Related
Nov 25, 2007
Hi,
I believe this question has been covered quite a bit, but none the less I wanted to ask it as I still have no resolution at the moment. I am consuming a table from Oracle which has a data type of NUMBER. When I use a .Net Provider to consume the data, the SCALE defaults to 0 for these select columns and thus we lose any decimal point information for these columns. I have tried using both the .Net Provider and the .Net for OLEDB providers for ORACLE. Both seem to suffer from this. The SSIS server itself is a Win 03' server on x64 hardware architecture. Also, when trying to use the Oracle Provider for OLEDB it throws a design-time error stating "Error in Initializing Provider".
Thanks!
Derek
View 4 Replies
View Related
Aug 9, 2007
Hello everyone,
I am newbie in SSIS and I have one problem.
I have defined in Control Flow one WebService Task, specified the WSDL-File.
I have chosen one method from WS as Input. But this method requires one parameter of the ArrayOf_xsd_string type.
If I try to choose it, I receive following error:
===================================
This complex type does not contain any properties. (Microsoft.SqlServer.WebServiceTaskUI)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.CustomGridControl.HandleEllipseBtn(Int64 row, Int32 col)
in my WSDL-File it already defined:
Code Snippet<complexType name="ArrayOf_xsd_string"><complexContent><restriction base="soapenc:Array"><attribute ref="soapenc:arrayType" wsdl:arrayType="xsd:string[]"/>
Could you help me? Where I can define this ArrayOf_xsd_string type, or how can I correct my problem?
Thanks a lot for your Help!
Regards,
View 1 Replies
View Related
Mar 28, 2007
I really need some information that shows how I could construct a query that take a complex type as parameter.
Regards, Christian
View 4 Replies
View Related
Jan 7, 2008
I am very new to Reporting Services. What I would like to know is if there is anyway for me to consume the raw data on a specific report from within excel. That is, can I call a report from reporting services directly from excel.
Maybe someone can outline the supported interaction between excel and reporting services for me. I have been having lots of trouble locating information about the interaction between these two.
--David
View 3 Replies
View Related
Aug 31, 2007
how to pass the numeric(12,0) data type to a variable in SSIS? what kind of variable data type should I choose?
I am trying to assign object_key column ( numeric(12,0)) to a variable in SSIS
If i select int32 , it keep giving me an error:
Error: 0xC001F009 at Row by Row process: The type of the value being assigned to variable "User::Object_Key" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
View 3 Replies
View Related
Oct 1, 2007
Hi, all my friends,
This is another rookie one.
Is there a way in SSIS dev environment that will let you convert/cast data type at desing time?
Under data flow mappings, there are so many things you can do, but why I did not find any easy way to convert?
I got this validation warning about not supportng converting type DT_STR to DT_I4, or something related.
Am I missing something obvious?
Thanks!
View 3 Replies
View Related
Mar 5, 2007
Hi All,
I am moving some numerical data from SQL server to excel. After the data is moved, i am getting green flag in excel cell to which the data is moved. I have configured the excel column to accept only numbers, but still i am getting green flag in excel cells. When i click the cell i am getting this message "The number in the cell is formatted as text or preceded by a apostrophe"
Can anyone tell me how can i over come the issue.
Thanks
Anwar
View 4 Replies
View Related
Jan 19, 2006
Hi,
This could well be down to my _limited_ knowledge of XSD.
I have a document and SXD supplied by 3rd party.
Both documents are valid, according to XMLSpy.
When I give the document and xsd to SSIS XML Source it complains about ambiguous complex types.
In the XML doc there is an element called Allowance that has child elements.
There is also a group which references many other elements including Allowance.
When I remove the group, SSIS stops complaining about allowance.
Would the problem stem from SSIS creating an output called Allowance ('cause of it's children), getting to the group and again, 'cause allowance has children, try create another output called Allowance.
Is my understanding of this correct? Is there a work around for a situation like this?
The only thing I can come up with is deleting the group....
Possible to alias an element? Could alias the Group > Allowance g_Allowance.
Cheers,
Crispin
View 3 Replies
View Related
Dec 15, 2005
If I have an executesql task that returns a result set, if the result set contains a tinyint, I can't find what SSIS data type to declare the variable as. They all cause errors.
View 10 Replies
View Related
Sep 3, 2007
Dear All,
the situation is that i have a column data comes from flat file and all i want to do is to check that the incoming column is numeric(12,3) and if the incoming data exceed that size "12,3" exception or redirect the row is happened.
the problem that i try to apply that with the data conversion or Derived column component but it in case of the scale of the incoming data exceed 3 the component trim until 3 scale.
i also try to perform it with the flat file data source component but i face a problem that if the data in the column is empty then flat file data source component read the numeric column as Zero
i hope someone help me coz i need to handle it soon.
best wishes
Maylo
View 5 Replies
View Related
Mar 27, 2007
I have a some raw data in a string that looks like
'1989'
I need that to become numeric 19.89
if I define a type double for rawAmount
and do this:
rawAmount = CInt(iRest.Substring(41, 4))
Row.amount2 = rawAmount / 100
where row.amount2 is a type double precision float from my script component task output, I get:
19.899999999999999
what output should i use in this data flow component to get 19.89.
thanks.
View 3 Replies
View Related
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
View Related
Oct 12, 2006
Hello,
I have an OLE DB Source and i want to transform the data type fields of the table before i export the table in an OLE DB Destination.
Is there a way to transform numeric value to float, and numeric to nvchar?
Thank you in advance.
View 5 Replies
View Related
Oct 2, 2007
Hi, all experts,
Is there a way in SSIS dev environment that will let you convert/cast data type at desing time?
Under data flow mappings, there are so many things you can do, but why I did not find any easy way to convert?
I got this validation warning about not supportng converting type DT_STR to DT_I4, or something like that on many occassions..
I am using OLEDB Source as source and SQL Server Destination as destination.
Am I missing something obvious?
Thanks!
View 6 Replies
View Related
Sep 25, 2006
My OLDB data source in the Lookup Transformation is not converting
When I try to join the colunms it claims the data types do not match the only differnece is the percision.??
ORACLE
select CAST(group_id AS NUMBER(18)) group_id from blah blah table
SSIS data type = [DT_NUMERIC], Percision = 38
SQL Sever data source
SELECT
CAST(PRINCIPAL_ID as numeric) GROUP_ID,
SSIS data type = [DT_NUMERIC], Percision = 18
View 1 Replies
View Related
Mar 18, 2008
OK I migrated a DTS package from MS SQL 2000 to MS SQL 2005 64-bit SSIS. OK so I fixed a problem with a Double Global Variable... Now I am stuck at a connection to Oracle and it returning data... Here is the error message:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "Remedy_Count": "Unsupported data type on result set binding 0.".
My SQL Statement is this:
SELECT COUNT(*) AS Expr1
FROM DB.MYTABLE
When I run it from the Query Builder it returned this:
EXPR1 = 2983
Here are some details from the General Page on the Execute SQL Task
ResultSet - Single row
ConnectionType - OLE DB
Connection - Oracle Provider for OLE DB
SQLSourceType - Direct input
BypassPrepare - True
In the Result Set I have The following:
Result Name - Variable Name
0 - Remedy_Count
So... what can I do to fix this. I have the Remedy_Count set as a:
EvaluateAsExpression - False
Value - 2414
ValueType - Double
I have tried setting the ValueType to Object and other things... Same error... I read some where about how SSIS has issues when Oracle returns a Numeric type data. Can someone help me with this? Anything you all can tell me would be great.
View 4 Replies
View Related
Sep 21, 2007
I'm contemplating a sql server archive strategy that rolls really old data off any sort of dbms and onto low cost media like dvds in a non relational archive format. I dont want to ever worry about these archives spanning different versions of sql when i go to retrieve a range of data that happens to span sql versions (eg one disc was sourced from 2005 another by 2008 but my report needs a union of both).
So I'm thinking about neutral/efficient formats for these archives and a live homegrown catalog that can determine exactly what disc(s) need to be mounted based on passed from and to date parameters...all so that the data that might span discs (and versions and maybe even schemas) can be merged and loaded into my sql version d'jour's "throw away" archive database for a one time report or other unplanned activity.
I remember raw data types being very convenient as an ETL format for our customers who have ssis, but wouldn't want our sqlexpress customers to be left without the archiving capability. Do the "things" that read and write raw data files really originate in some special T-SQL command that all sql editions can use, or is it strictly an ssis thing?
View 4 Replies
View Related
Oct 11, 2007
We have an SSIS package that reads in a tab delimited file and throughs the data into a SQL Server 2005 table. The package worked fine on the test files but when we recieved the production files we are getting the error message:
Invalid data for type "numeric".
Needless to say this is not very discriptive. I ran a trace on the DB durning the call and its doing a bulk insert
BULK INSERT [dbo].[FNIAllAppData]
FROM 'GlobalDTSQLIMPORT 00000000000015b800000000009fbd88'
WITH (DATAFILETYPE = 'DTS_Buffers', CODEPAGE = 'RAW', CHECK_CONSTRAINTS, TABLOCK)
When I changed the Data Flow Destination off to OLE DB Destination the error does not occure and all records are loaded successfully.
Any suggestions or help would be appreciated. Thanks.
View 3 Replies
View Related
Oct 13, 2015
I have to perform several data checks before loading data into target table. For example I am having 1 flat file with below column
Id Name Age
Int Varchar(100) Int
My requirement is to create package, checks will be performed on each record, column of the files. Any records which failed the checks considered as error records and will be written to the exception table.
View 4 Replies
View Related
Apr 24, 2007
It seems I can do the type conversion in two place:
1. The advanced editor for flat file source => Input and output roperties. And I can set the output columns data type properties there.
2. Using a Data conversion box in Data flow transformation.
Any difference between these two? Which one should be used?
View 1 Replies
View Related
Jun 9, 2015
I have excel column with numeric and special character values , when I take that into SQL table using SSIS, the special character values enter as null value. the example column values are given bellow
1
2
2/1
1/2
1/2 means 1 or 2 ,
how can I read this values exactly into SQL table?
View 13 Replies
View Related
Mar 2, 2007
I have a CSV Flat File Source with a Decimal column - but DataPrecision property is grayed out - why?
View 1 Replies
View Related
Jun 27, 2007
Hi all,
I am creating a customer data flow component for SSIS for use in a package. I've got some custom properties that I am exposing using the supplied advanced editor (no custom property editor here).
Some of my properties are enumerated types, and I have deciphered how to get those properties to show as dropdown lists of their respective enumerations. (For those of you who may be looking as hard as I did as to how to accomplish this, see the end of this post.)
I also have a few properties which request SSIS package variable names - such as an file name variable. However, I can't figure out how to tell the advanced editor that the property is looking for an SSIS variable, so that it can show a dropdown list of package variables, much like virtually any other Microsoft supplied Data Flow component can.
Is there a Type Converter I could specify for those custom properties? Is there another way to instruct SSIS that my custom property is expecting a variable? Or do I need to code a custom UI for editing my Data Flow Task?
To create a dropdown list of values for a custom property that represents an enum, do the following:
1. Create your enum definition, such as "public enum ThisIsMyEnum { one, two }"
2. Create a new class that inherits from TypeConverter, such as "public class MyEnumConverter : TypeConverter"
3. Override "CanConvertFrom", and return true if "sourceType == typeof(string)"
4. Override "CanConvertTo", and return true if "destinationType == typeof(string)"
5. Override "ConvertFrom", and return the enum value (such as "one" or "two" in my example) that corresponds to the string passed in the parameter "value"
6. Override "ConvertTo", and return a string that corresponds to the enum value passed in the parameter "value"
7. Override "GetStandardValuesSupported" and return true
8. Override "GetStandarValuesExclusive" and return true to indicate that ONLY the enum values should be accepted
9. Override "GetStandardValues", and return a new StandardValuesCollection constructed with Enum.GetValues() of your enum, such as "return new StandardValuesCollection(Enum.GetValues(typeof(ThisIsMyEnum)));"
10. Just above your "public enum" declaration, add a "TypeConverter" attribute to link your type converter to your enum, such as "[TypeConverter(typeof(MyEnumConverter))]"
11. In "ProvideComponentProperties", after you've created your custom property like this: "IDTSCustomProperty90 propEnum = ComponentMetaData.CustomPropertyCollection.New()", add another line to specify the TypeConverter property of the property to the full assembly name of your type converter, like so: "propEnum.TypeConverter = typeof(MyEnumConverter).AssemblyQualifiedName;"
View 11 Replies
View Related