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 am building a webapp that calls an SSRS instance to display a report based on another servers web service.I can make a call from the ssrs server using this in the RDL:
<Query> <Method Namespace="http://machine/webservices" Name="HelloUser"> Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â <Parameters> Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â <Parameter Name="user"> Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â <DefaultValue>Craig</DefaultValue> Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â </Parameter> Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â </Parameters> </Method> <ElementPath IgnoreNamespaces="true">*</ElementPath> </Query>
This works no problem. but.. I want to have the user parameter "Craig" be a parameter passed in from the web app. If it were a regular SQL data source you would put @user in the query. How do you do it with XML text queries?
How do I get columns to output when I have a data reader source? My connection is an ODBC and does complex sql. I am connection to a Netezza database and I would like to execute a very complex query, but in essence does
Create newtable as
(select day, sessionId)
from source
// lots of other joins and unions
select day, sessionId from newtable
drop newtable
I have an ODBC connection and I have a Datareader source, I cannot connect this source to my SQL Server destination because no output columns are available. What am I missing here?
Are there any good examples of this, taking data from a ODBC source into SQL server?
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 writed That CodeDim SqlCom As New SqlDataSource SqlCom.ConnectionString = "data source=localhost;initial catalog=Mansouria1.1;integrated security=True" SqlCom.DataSourceMode = SqlDataSourceMode.DataReader SqlCom.InsertCommandType = SqlDataSourceCommandType.StoredProcedure SqlCom.InsertCommand = "Add_Machine_Component" SqlCom.InsertParameters.Add("@Name", TextBox1.Text) SqlCom.Insert() and the store Procedure@Name Nvarchar(150)ASInsert into Machine_Component(Name) values (@Name)Go That error Message displayprocedure or Function 'Add_Machine_Component' expects parameter '@Name', which was not supplied.
Does anyone have any experience setting up paramters when using a Teradata data source? I am specifically looking for how to set up dates and multi-select drop down boxes as parameters. Using a fixed query against Teradata using SSRS is straight forward and is working well. Parameters, not so much.
In the data tab of my report (Reporting Services for SQL Server 2000), I'd like to run a query that requires parameters be passed to it. Is there a way to pass the parameters of the report to that query?
This seems to be much more difficult than it was in DTS (or perhaps I just need to adjust to the new way of doing things).
Eventually I found that I needed to use "SQL command from variable" and using two other variables as input parameters. The expresion for the command is
This previews correctly and the resulting columns are available for mapping to a destination. So far so good.
By the way, is this the best way to call a stored procedure with parameters?
I have pasted the stored procedure at the end of this posting because I have come accross a puzzling problem. The query as shown below works correctlly but if I un-comment the delete statement, the preview still works and the columns are still avilable for mapping but I get the following errors when the package is executed.
Error: 0xC02092B4 at Data Flow Task, OLE DB Source [1]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC02092B4.
I realise that I could execute the delete query in a separate SSIS package step but I am curious as to why there is a problem with the way I tried to do it.
At one stage the stored procedure used a temp table and later I also experimented with a table variable. In both cases I got similar errors at execution time. In the case of the temp table there was another problem in that, while the preview worked, there were no columns available for mapping. Using a table variable seemed to overcome this problem but I still got the run time error. eventually I found a way to avoid using either a temp table or a table variable and the package then worked correctly, copying the data into the desitnation table.
It seems to me that if there is any complexity at all to the stored procedure, these errors seem to occur. Can anyone enlighten me as to what the "rules of engagement" are in this regard? Is one solution to use a wrapper stored procedure that simply calls the more complex one?
ALTER procedure [dbo].[usp_ValveStatusForDay]
(
@dateTime DateTime,
@reportName VarChar(100)
)
AS
BEGIN
DECLARE @day VarChar(10)
DECLARE @month VarChar(10)
DECLARE @year VarChar(10)
DECLARE @start VarChar(25)
DECLARE @end VarChar(25)
SET @day = Convert(Varchar(10),DatePart(day, @dateTime))
SET @month = Convert(VarChar(10), DatePart(month, @dateTime))
SET @year = Convert(VarChar(10), DatePart(year, @dateTime))
How can I pass a parameter used for the DataSet query? I'm using this DataSet file as data source of my .rdlc report for Windows Forms.
I've already done a .rdlc report to Web Forms where I passed the query parameter by ObjectDataSource.SelectParameters, but in Windows Forms this object was not created. I just hava the following created objects:
I have three databases which have the identical data structure. When the user runs a report, he has to select a database. For example, my report query would look like:
Select * from <theDatabase>.dbo.MyTable
How could I assign the value of <theDatabase> at run time depending on what the user selects from the list? In my datasource, I have only specified the server name and no value for initial catalog.
In many DTS packages I have used parameterised queries for incremental loads from Oracle database sources using the Microsoft ODBC Driver for Oracle.
Now I want to migrate these packages to SSIS, but the OLE DB connection for Oracle does not support parameters.
I cannot use the "SQL command from variable" data access mode because of the 4000 character limitation on the length of string variables and expressions.
I want to import the contents of a remote database every morning via a Web service to populate a data mart. I am looking for the most efficient way to do this. Step 1 is to use the Web Service Task in SSIS to grab the data and save to an XML file (since the content is far too large to store in the String data type).
For step 2, I can either use the SQLXMLBulkLoad object in VB Script to read in the file and populate the tables. Or I can using the XML Source object in Integration Services. It is clear that SQLXMLBulkLoad is the most efficient way to load data, but is the XML Source object in SSIS just a graphical representation of the SQLXMLBulkLoad object, or is it something else entirely that is inefficient because it requires loading of the XML content entirely into RAM before it can process the XML?
We have been a Crystal shop for ages; we are currently doing a proof-of-concept for a conversion to MS Reporting Services. As such, we are developing some Analysis Services 2005 cubes to drive some new SSRS reports, which our users will access through Report Manager. Unfortunately, we are all MDX noobs here, so we are making heavy use of the Wizards until we can come up to speed.
The problem we are running into is when we develop a report with Date Parameters. When we deploy this report, the date parameter box is a dropdown box instead of a date picker. I've seen a couple of other posts on this topic, but when I try to apply the fixes mentioned in them, I throw errors.
I have two quick questions:
Why does this happen? Is it a limitation in the MDX language, in SSAS, or SSRS? Are there any planned fixes? Can someone please show me how to fix this on my actual query string for one of our basic reports? I've highlighted the date parameters.
Code Snippet
SELECT NON EMPTY { [Measures].[Lead] } ON COLUMNS, NON EMPTY { ([Store].[Store ID].[Store ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@StoreStoreID, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@FromLeadCreationDateCalendarDate, CONSTRAINED) : STRTOMEMBER(@ToLeadCreationDateCalendarDate, CONSTRAINED) ) ON COLUMNS FROM [Referral Leads]))) WHERE ( IIF( STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED).Count = 1, STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED), [Lead Source Type].[Lead Source Type].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
I'm afraid, given my user community, that if I can't get the date picker to work properly, it could be a deal breaker.
I am attempting to pass report parameters to my query. My report's data source is a WCF web service. I can run normal queries fine, but I have found that without the know-how to pass parameters to my query I am limited with my capabilities.
Assume a string parameter named "Name" with a specified default value of "Jmachol90", how would I pass that into the following query at the designated place: Â Â <Query> Â Â Â Â Â Â <Method Namespace="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09" Name="SecurityPrincipalsGetRequest"> Â Â Â Â Â Â <Parameters> Â Â Â Â Â Â <Parameter Name="Criteria" Type="XML"> <DefaultValue>
It's well known issue, that one can't use any dataset fields in a report header/footer directly. One of the approach is to create query-based parameter that basically equals =First(Fields!@FieldName@.Value, "@DataSetName@") and use that parameter value instead. But it doesn't work in my case!
My report displays some entity description and is parametrized with EntityID param. Its header contains entity name that, according to the approach, is queried from the data source through the EntityName report parameter. There's important issue: the report is displayed in ReportViewer control, that is embedded into my application and entity ID parameter isn't ser by user in ReportViewer parameters area. Its default value is changed by the application with SetReportParameters() web method every time a user wants to view the report according to the entity the user is exploring in the application. But after the report has been rendered, its header always contains not actual (outdated) entity name. Nevertheless, the report body contains actual data (including entity name). If I alter entity ID parameter in ReportViewer or in web-based Report Manager and refresh report, header displays correct entity name.
Any recommendations for good advanced t-sql books/articles? I find myself involved with writing increasingly more complex queries and after spending a few hours on some, and then searching on this site for potential answers/help, I am wondering if there might be some good books on creating more advanced/complex t-sql for real world scenarios.
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.
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.
My requirement for the parameter is multivalue parameter with a text box. for example when user enters aa15 it need to include product aa15. when the user enters aa15, aa16, zz15 than it needs to include all the three products. the last case is when the user enters AA** than i need to inclued all the products start with AA. when i use default multivalue parameter with data source analytical services than i am getting a drop down box. I dont want that. I need a text box where user can enter the value. 1. In sql we have a like key word to query . for example select * from product where product like "AA%". what is equavalent mdx query to get such results ? 2.How to impliment the multivalue parameters without using dropdown box?
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?
, Hi In this code how can I create a new data source and new data source view and model and structure that it run dynamic. In this code I have a lot of errors, that they are about server and database don€™t have in current code, In this code, first I should definition server or no?
How can I create data source and data source view and model and structure? Please say code of that, and guide me. databasename and srv is unknown. Do I add other reference with analysis services? Please explain about these codes: ************************************************************************ 1) RelationalDataSource dsNew = new RelationalDataSource( datasourceName, Utils.GetSyntacticallyValidID( datasourceName, typeof(RelationalDataSource)));
I have set up a new connection as a connection from data source, but I cannot see how to use this connection to create my Data Flow Source. I have tried using an OLE DB connection, but this is painfully slow! The process of loading 10,000 rows takes 14 - 15 minutes. The same process in Access using SQL on a linked table via DSN takes 45 seconds.
Have I missed something in my set up of the OLE DB source / connection? Will a DSN source be faster?
I will like to be able to call a DTS to which I pass in parameter the name and the path of a DBF file of which the function will be to create a table in my SQL server identical by the structure and the contents to the DBF. The problem is that I never know in advance the structure of the DBF. But, in DTS designer I am already obliged to inform the name and the structure of the target then to make the correspondences of column.
PS: I know how to pass parameters to a DTS via utility DTSRUN... / A... etc in order to provide him the name and the path of the DBF
Hello, I have a DataReader souce configured to an ADO.NET connection manager that uses a .NET ODBC Data Provider. The DSN configured in the connection manager uses the IBM DB2 ODBC Driver.
I have a package variable, the value of which I want to use in the WHERE clause of the query contained in the Data Reader source. The query is as follows:
SELECT T1."IRK_ACCT_CD", T1."IRK_COMPANY_NBR", T1."IRK_ACCT_NAME", T1."IRK_ADDRESS_1", T1."IRK_ADDRESS_2", T1."IRK_ADDRESS_3", T1."IRK_STATE_CD", T1."IRK_POSTAL_CD", T1."IRK_AR_TYPE", T2."RK502_ITEM_RE_NO", T2."RK502_TRANS_CD", T2."RK502_TRANS_TYPE", T2."RK502_ITEM_AMT", T2."RK502_ITEM_DT", T2."RK502_PURCH_ORD_NO", T2."RK502_GL_EFF" FROM "CBDBOW"."IRK_RECORD" T1, "CBDBOW"."RK502_OPEN_ITEMS" T2 WHERE T1."IRK_COMPANY_NBR" = T2."RK502_COMP_NO" AND T1."IRK_ACCT_NUMBER" = T2."RK502_ACCT_NO" AND T2."RK502_ITEM_DT" < ?
I'm not very sure as to how to map the package variable this way. I think for ODBC parameters, I need to represent the parameter with a question mark.
I am using an OLE DB Source with SQL command for the data access mode. I defined parameters, then added a complex query with subqueries. It seems like the parameters for a query are not being filled correctly (because I am getting too many rows returned). At one point I saw an error message the said something to the effect that the parameters would be ignored when there was a subquery in the SQL.
Firtsly - I am new to SSIS if my approach could be improved then I welcome suggestions.
Scenario: I have a large SSIS package that consolidates / summarizes work week information from several data sources. Currently each data flow task in the control flow calculates the from and to date that is filtered on, for example:
DECLARE @FromDT AS DATETIME SET @FromDT = CAST(FLOOR( CAST( DATEADD(D, -7, GETDATE()) AS FLOAT ) ) AS DATETIME)
DECLARE @ToDT AS DATETIME SET @ToDT = CAST(FLOOR( CAST( GETDATE() AS FLOAT ) ) AS DATETIME)
I would like to remove these statements that appear in most steps and replace them with a global variable that is used throughout the package. This statement would only appear once & it would make the package much easier to run after failure etc.
Problem: I am using Data Reader Source with the 'SQLCommand' property specified. It looks like parameters are only supported if an OleDB connection is used?
So I switched to an OleDB connection and no parameters are recognised in the string - a forum search reveals that parameters in sub queries are not always found properly. The solution to this problem appears to be, to set 'Bypass Prepare' to True but this is a property for the Execute SQL task, not the Data Flow Task source.
Questions:
Does the Data Reader Source control from Data Flow Source toolbox section support parameters? Can anyone suggest a fix to the OleDB Source issue with Parameters? Is there a better way to solve my problem e.g. Using Execute SQL Task instead of Data Flow tasks etc
Example SQL:
This SQL is an example of the SQL for the OleDB Data Source (within a Data Flow task)
------------------------------ --RADIUS LOGINS ------------------------------ DECLARE @FromDT AS DATETIME SET @FromDT = CAST(FLOOR( CAST( DATEADD(D, -7, GETDATE()) AS FLOAT ) ) AS DATETIME)
DECLARE @ToDT AS DATETIME SET @ToDT = CAST(FLOOR( CAST( GETDATE() AS FLOAT ) ) AS DATETIME)
DECLARE @Attempts AS BIGINT SET @Attempts = (SELECT COUNT(*) FROM dbo.Radius_Login_Records WHERE LoggedAt BETWEEN @FromDT AND @ToDT)
DECLARE @Failures AS BIGINT SET @Failures = (SELECT COUNT(*) FROM dbo.Radius_Login_Records WHERE LoggedAt BETWEEN @FromDT AND @ToDT AND Authen_Failure_Code IS NOT NULL)
DECLARE @Successes AS BIGINT SET @Successes = @Attempts - @Failures
DECLARE @OcaV1Hits AS BIGINT SET @OcaV1Hits = (SELECT COUNT(DISTINCT LoginName) FROM dbo.Radius_Login_Records WHERE LoggedAt BETWEEN @FromDT AND @ToDT AND EAPTypeID = 25)
DECLARE @OcaV2Hits AS BIGINT SET @OcaV2Hits = (SELECT COUNT(DISTINCT LoginName) AS OcaV2Hits FROM dbo.Radius_Login_Records WHERE LoggedAt BETWEEN @FromDT AND @ToDT AND EAPTypeID = 13)
SELECT @Attempts AS ConnectionAttempts, @Failures AS ConnectionFailures, (CAST(@Successes AS DECIMAL(38,2)) / CAST(@Attempts AS FLOAT) * 100) AS SuccessRate, @OcaV1Hits AS OcaV1Hits, @OcaV2Hits AS OcaV2Hits
Please remember, I'm new to SSIS - so be detailed in your response. Thanks for your help!
