SQL Data Source Parameters Problem

Jan 21, 2008

 I writed That Code
Dim 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)
AS
Insert into Machine_Component(Name) values (@Name)
Go
 
That error Message display

procedure or Function 'Add_Machine_Component' expects parameter '@Name', which was not supplied.

 

 

View 3 Replies


ADVERTISEMENT

Teradata Data Source And Parameters

Dec 27, 2006

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.

R

View 1 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

Pass Report Parameters To Data Source (2000)?

Apr 4, 2007

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?



Thank you.

View 3 Replies View Related

Calling A Stored Procedure On A Data Source (with Parameters)

Nov 29, 2007

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

"usp_ValveStatusForDay '" + @[User:ate] + "','" + @[User::Report] + "'"

which typically evaluates as

usp_ValveStatusForDay '18 Oct 07','Report_Name'

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

IF @month = '1' SET @month = 'Jan'

IF @month = '2' SET @month = 'Feb'

IF @month = '3' SET @month = 'Mar'

IF @month = '4' SET @month = 'Apr'

IF @month = '5' SET @month = 'May'

IF @month = '6' SET @month = 'Jun'

IF @month = '7' SET @month = 'Jul'

IF @month = '8' SET @month = 'Aug'

IF @month = '9' SET @month = 'Sep'

IF @month = '10' SET @month = 'Oct'

IF @month = '11' SET @month = 'Nov'

IF @month = '12' SET @month = 'Dec'

SET @start = @day + ' ' + @month + ' ' + @year + ' 00:00:00'

SET @end = @day + ' ' + @month + ' ' + @year + ' 23:59:59'

--delete from ValveStatus where SampleDateTime between dbo.ToBigInt(@start) and dbo.ToBigInt(@end)

exec dbo.usp_ValveStats_ReportName @reportName, @start, @end, '1h'

END

View 8 Replies View Related

Passing Parameters To DataSet Useb As Report Data Source

May 21, 2007

Hi guys,



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:

- despesaTableAdapter (from myDataSetTableAdapter)

- despesaBindingSource (Windows.Forms.BindingSource)

- RelatorioDataSet (from myDataSet)

- relatorioDataSet1 (from myDataSet)



Any help will be very appreciated.







Tks and rgds,

Luis Antonio

View 1 Replies View Related

Dynamically Creating The Data Source Of A Report At Run Time Using Parameters

Feb 13, 2008



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.

View 1 Replies View Related

Parameters In Data Flow Task With Oracle Database Source

Jul 9, 2006

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.

Am I missing an obvious workaround?

View 7 Replies View Related

Reporting Services :: Passing Parameters To SSRS That Is Using Web Service As Data Source

Jul 16, 2009

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?

View 5 Replies View Related

Date Parameters Show As Strings When Using Analysis Services As A Data Source

Jun 7, 2007

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.



Thanks very much in advance for your help.



Regards,



Steve

View 7 Replies View Related

SSIS OLEDB Data Source Query Parameters Connect To Oracle

Oct 26, 2007

Hi,

I'm trying to use query parameters with an Oracle OLEDB Source in a data

flow task and I'm having problems.

I've tried formatting the query each of the following ways...

--

select

frq_code,

frq_name,

update_frq,

uptime_frq

from frequency_bcs

where update_frq > ?

and update_frq <= ?

--

Parameters cannot be extracted from the SQL command. The provider might not

help to parse parameter information from the command. In that case, use the

"SQL command from variable" access mode, in which the entire SQL command is

stored in a variable.

Additional information

---> Provider cannot derive parameter information and SetParameterInfo has

not been called. (Microsoft OLE DB Provider for Oracle).

View 7 Replies View Related

Reporting Services :: Passing Report Parameters To A Query Using WCF Data Source?

Nov 29, 2010

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>

[code]....

View 17 Replies View Related

Parameters Approach To Fill Report Header With Source Data Doesn't Work

Jan 19, 2007

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.



What's wrong in the workflow described?

View 3 Replies View Related

Parameters In Reporting Services And Data Source Is Analytical Services

Dec 10, 2007



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?

View 1 Replies View Related

An Error Has Occurred During Report Processing. A Data Source Instance Has Not Been Supplied For The Data Source DetailDS_get_o

Mar 13, 2008

hi ,

i am trying for a drill through report (rdlc)

ihave written the following code in drill through event of reportviewer, whenever i click on the first report iam getting the error like

An error has occurred during report processing.


A data source instance has no
t been supplied for the data source "DetailDS_get_orderdetail".







the code is



using System;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

//using Microsoft.ApplicationBlocks.Data;

using Microsoft.Reporting.WebForms;

using DAC;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

ReportViewer1.Visible = false;

}

protected void Button1_Click(object sender, EventArgs e)

{

DAC.clsReportsWoman obj = new clsReportsWoman();

DataSet ds = new DataSet();

ds = obj.get_order();

ReportViewer1.LocalReport.DataSources.Clear();

ReportDataSource reds = new ReportDataSource("DataSet1_get_order", ds.Tables[0]);



ReportViewer1.LocalReport.DataSources.Add(reds);

ReportViewer1.LocalReport.ReportPath = "C:/Documents and Settings/km63096/My Documents/Visual Studio 2005/WebSites/drillthrurep/Report.rdlc";

ReportViewer1.LocalReport.Refresh();

ReportViewer1.Visible = true;

}

protected void ReportViewer1_Drillthrough(object sender, DrillthroughEventArgs e)

{

DAC.clsReportsWoman obj = new clsReportsWoman();

ReportParameterInfoCollection DrillThroughValues =

e.Report.GetParameters();



foreach (ReportParameterInfo d in DrillThroughValues)

{

Label1.Text = d.Values[0].ToString().Trim();

}

LocalReport localreport = (LocalReport)e.Report;

string order_id = Label1.Text;

DataSet ds = new DataSet();

ds = obj.get_orderdetail(order_id);



ReportViewer1.LocalReport.DataSources.Clear();

ReportDataSource reds = new ReportDataSource("DetailDS_get_orderdetail", ds.Tables[0]);

ReportViewer1.LocalReport.DataSources.Add(reds);

ReportViewer1.LocalReport.ReportPath = Server.MapPath(@"Reportlevel1.rdlc");

ReportViewer1.LocalReport.Refresh();





}



}

the code in method get_orderdetail(order_id) is

public DataSet get_orderdetail(string order_id)
{
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
cmd.Parameters.Add("@order_id", SqlDbType.VarChar, 50);
cmd.Parameters["@order_id"].Value = order_id;
ds = SQLHelper.ExecuteAdapter(cmd, CommandType.StoredProcedure, "dbo.get_orderdetail");
return (ds);
}pls help me.

View 1 Replies View Related

Amo And Creating Data Source And Data Source View Code

Feb 2, 2008

,
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?

Database dbNew = new Database (databaseName,
Utils.GetSyntacticallyValidID(databaseName, typeof(Database)));
srv.Databases.Add(dbNew);
dbNew.Update(true);
***********************************************************

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

db.DataSources.Add(dsNew);
dsNew.ConnectionString = connectionString;

dsNew.Update();
2)

RelationalDataSourceView rdsv;

rdsv = db.DataSourceViews.Add(
datasourceviewName,
Utils.GetSyntacticallyValidID(
datasourceviewName,
typeof(RelationalDataSourceView)));

rdsv.DataSourceID = ds.ID
***************************************************************
3)
OleDbConnection cn = new OleDbConnection(ds.ConnectionString);
OleDbCommand cmd = new OleDbCommand(
"SELECT * FROM [" + tableName + "] WHERE 0=1", cn);
OleDbDataAdapter ad = new OleDbDataAdapter(cmd);

DataSet dss = new DataSet();
ad.FillSchema(dss, SchemaType.Source);

*************************************************************
4)

// Make sure we have the name we thought

dss.Tables[0].TableName = tableName;

// Clone here - the original DataTable already belongs to a DataSet
rdsv.Schema.Tables.Add(dss.Tables[tableName].Clone());
rdsv.Update();


5)

MiningStructure ms = db.MiningStructures.Add(miningstructureName, Utils.GetSyntacticallyValidID(miningstructureName,
typeof(MiningStructure)));
ms.Source = new DataSourceViewBinding(dsv.ID);
ms.CaseTableName = "Customer";

Add columns:
ScalarMiningStructureColumn smsc;

// From table "Customer" we will add a couple of columns
// CustomerID - key
smsc = new ScalarMiningStructureColumn("Customer ID",
Utils.GetSyntacticallyValidID("Customer ID", typeof(ScalarMiningStructureColumn)));
smsc.IsKey = true;
smsc.Content = "Key";
smsc.KeyColumns.Add("Customer", "customer_id", OleDbType.Integer);
ms.Columns.Add(smsc);

*******************************************
6)

MiningModel mm = ms.MiningModels.Add(miningmodelName,
Utils.GetSyntacticallyValidID(miningmodelName,
typeof(MiningModel)));


mm.Algorithm = "Microsoft_Decision_Trees";
mm.Parameters.Add("COMPLEXITY_PENALTY", 0.3);


MiningModelColumn mc = new MiningModelColumn("Customer ID",
Utils.GetSyntacticallyValidID("CustomerID",
typeof(MiningModelColumn)));
mc.SourceColumnID = ms.Columns["Customer ID"].ID;
mc.Usage = "Key";
mm.Columns.Add(mc);


mm.Update();

Please exactly say, whatever I want
Thanks a lot for your answer
Please don€™t move this question because I don€™t know where I should write this.

View 1 Replies View Related

How Do I Add An ODBC Connection Data Source As A Data Flow Source

Mar 2, 2007

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?

Thanks in advance

ADG

View 2 Replies View Related

Source & Dest In Parameters In DTS

Jun 29, 2001

Hello,

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

Thank you

View 1 Replies View Related

Using Parameters With DataReader Source

Nov 15, 2007

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.

What else do I need to do to set this up?

Thank you for your help!

cdun2

View 10 Replies View Related

Excel (source) And Parameters ?

Sep 5, 2006

I want to query my excel source sheet with a paramater so i create a sql statement like

select * from xxx where name like 'fred%'

Then I want to parameterise it with

select * from xxx where name like @Name or select * from xxx where name like ?

both allow me to map the Parameter neither syntax works in the data flow or preview but ? still works in the build query window !

Anyone know the correct Param declaration for the Excel Source (i believe it should be oledb @Name declaration ?

is this a bug or am i missing something here ...

Cheers



Colin













View 3 Replies View Related

OLE DB Source Parameters Ignored When Subquery?

Feb 20, 2006

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.

Can anyone shed some light on this?

Thanks,

Laurence



View 4 Replies View Related

OleDB Source And Parameters

May 11, 2007

Hi,



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!

View 5 Replies View Related

Passing Parameters To A OLE DB Source

Mar 10, 2008

I have an OLE DB Source that talks to an Oracle Db and returns back some data. I need to be able to pass some parameters to my query from the SSIS variables.

How can accomplish this ? Do I need to create a stored proc in my Oracle DB..is that the only way I can pass parameters ?
Is so how ?

Any examples would be really appreciated

Thanks

Smx

View 13 Replies View Related

Parameters In Function In OLE DB Source

Jan 12, 2007

Hi there!

I need to use some parameters in an OLE DB Source like that:

select * from mdm.mdm_pos_hierarchies
where dbo.fkt_get_guelt_von (posh_valid_fr) <= dbo.fkt_get_guelt_von (?)
and dbo.fkt_get_guelt_bis (posh_valid_to) >= dbo.fkt_get_guelt_bis (?)

The function returns a valid datetime. These parameters normaly works fine, but not with use as funtion-paramerters. I get feeble error messages (by trying to map the parameters) like that:

"Falsche Syntax in der Nähe von ')'. (Microsoft SQL Native Client)" - what means "Wrong syntax near ')'" and says nothing. If i replace the parameters with the mapped value (i.E. '200601') it works fine.

What is the reason for that? It there any solution or a workaround?

Thanks, Torsten



View 3 Replies View Related

Question OLEDB Source With SQL And Parameters

Mar 14, 2008

Hello All,

I understand that the parser is having issues parsing my second command but I was wonder if anyone had stumbled on a work around for this:

-- OLEDB Source has no problem with this:
SELECT *
FROM some_table
where
last_update_dt = ?


-- OLEDB Source errors on this:
SELECT *
FROM some_table
where
Convert( char(12), last_update_dt, 112 ) = Convert( char(12), Cast( ? as DateTime), 112 )

The first SQL statement won't return the desired rows because of the wonderful time stamp, the second works well but the provider won't parse it. I have used the "SQL command from variable" to work around this but we have some tables with 200+ columns, so the only way to use the "SQL command from variable" is to do a select *, which I'm trying to avoid, for both performance reasons and company standards.

Any thoughts?

Thanks,

Raymond

View 4 Replies View Related

Parameters For OLEDB Oracle Source Query

Jul 5, 2006

Provider cannot derive parameter information and SetParameterInfo has not been called. (Microsoft OLE DB Provider for Oracle)


I am getting the above error while opening the parameter box at OLEDB source for Oracle using SQL command option at Data Access Mode?? Can you any one please help me in this regard and trouble shoot this problem..

View 8 Replies View Related

Create Data Source, Data Source View From XML?

May 8, 2007

hi everybody,
i want to create data source and data source view for data mining, with using C Sharp.
i have create data source and data source view and export to XML file, but when i change to another computer, run those XML file, it return error, when i run statement to create and biuld mining model, what can i change on xml or how to run XML on another computer sucessfully,
and have i build data source and data source view, how to do it.?

thanks for you helps

View 1 Replies View Related

Cannot Create A Connection To Data Source 'data Source Name'

Dec 11, 2007

Today I was making a few reports.
When I tested the reports in Visual Studio, they worked great: I got the expected result.
But when I deployed the reports to our reportserver the problem started.
When I click on the directory in which my reports are deployed, I got my 4 reports.
Till now everything worked correct.
But when I click on a report to view the results it went wrong.
I got an error:
"Cannot create a connection to data source 'Live'"
(Live is the name of our data source).

We are using the Windows Logons and I am sure that I have all the rights on the server, I gave myself 'sysadmin' rights, so it should work.
I also have tried it with all the roles assigned on my account, but then it still won't work.

When I modify the data source, and set it to another server en database it works.
The datasource 'Live' exists on a x64 MsSQL server, en the other datasource is on a x86 MsSQL server.
Maybe that is the problem?

Can someone tell me what is wrong?

View 1 Replies View Related

HELP: A Data Source Instance Has Not Been Supplied For The Data Source

May 20, 2007

Hi there,



I'm trying to build a report for Windows Forms (C#) using .rdlc files.

But every time I run it, I get this error message. I've followed the step-by-step from MSDN instructions... but it doesn't work!



Could anyone help me?!





Tks a lot!



Luis Antonio - Brazil

View 1 Replies View Related

Loading Data Using Ole Db Source With Input Source Being A View

Dec 13, 2007

I was trying to load data using SSIS, Data Flow Task, OLE DB Source, source was a view to a OLE DB Destination (SQL Server). This view returns 420,591 rows from Query Analyzer in 21 seconds. Row length is 925. When I try to executed the Data Flow Task from SSIS, I had to stop the process after 30 minutes, because only 2,000 rows had been retrieved. I modified the view to retun top 440, 000 and reran. This time all 420, 591 rows were retrieved and written in 22 seconds. Next, I tried to use a TOP 100 Percent. Again, only 2,000 rows were return after 30 minutes. TempDB is on a separate SAN Raid group with 200 gig free, Databases on a separate drive with 200 gig free. Server has 13 gig of memory and no other processes were executing.

The only way I could populate the table was by using an Execute SQL Task and hard code an Insert into table selecting data from the view (35 seconds) from SSIS.

Have anyone else experience this or a similar issue? Anyone have a solutionexplanation?

View 13 Replies View Related

Using Stored Procedure As OLE DB Source With Parameters From Package Variables

Apr 4, 2006

Hi Guys,

(I have searched this forum extensively, but still can't find the solution to this problem)

Here it is:

I have step in my ETL process that gets facts from another database. Here is how I set it up:

1) I have to package variables called User::startDate and User::endDate of data type datetime

2) Two separate Execute SQL Tasks populate those variables with appropriate dates (this works fine)

3) Then I have a Data Flow Task with OLE DB source that uses a call to a sproc of the form "exec ETL_GetMyData @startDate = ?, @endDate = ?" with parameters mapped accordingly (0 -> User::startDate, 1 -> User::endDate)

When I run this I get an error 0xC0207014: "The SQL command requires a parameter named "@startDate", which is not found in the parameter mapping."

It is true that the sproc in fact requires @startDate and @endDate parameters, so next thing I tried to do is call the sproc the following way: "exec ETL_GetMyData @startDate = ?, @endDate = ?"

To no avail. It gives me the same error. Incidentally, when I hard code both dates like "exec ETL_GetMyData '2006-04-01', '2006-04-02'" everything works well.

Also, I want to mention that in the first two cases, I get an error right in the editor. When I try to parse the statement it gives me "Invalid parameter number" message.

This has been such a pain in my neck. I've waisted the whole day trying to monkey with the various parts of package/statements to get this to work and it still doesn't. I dont' want to say anything about Integration Services design right now, but you probably know what I'm thinking...

View 23 Replies View Related

ADO.NET 2-VB 2005 Express Form1:Printing Output Of Returned Data/Parameters From The Parameters Collection Of A Stored Procedure

Mar 12, 2008

Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:


Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlDbType

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")

Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)

testCMD.CommandType = CommandType.StoredProcedure

Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)

RetValue.Direction = ParameterDirection.ReturnValue

Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)

auIDIN.Direction = ParameterDirection.Input

Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)

NumTitles.Direction = ParameterDirection.Output

auIDIN.Value = "213-46-8915"

PubsConn.Open()

Dim myReader As SqlDataReader = testCMD.ExecuteReader()

Console.WriteLine("Book Titles for this Author:")

Do While myReader.Read

Console.WriteLine("{0}", myReader.GetString(2))

Loop

myReader.Close()

Console.WriteLine("Return Value: " & (RetValue.Value))

Console.WriteLine("Number of Records: " & (NumTitles.Value))

End Sub

End Class

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.

Thanks in advance,
Scott Chang

View 29 Replies View Related

1 SP With Dynamic Input Parameters And Multiple Rows As The Source Of The Query

Dec 4, 2005

How can I run a single SP by asking multiple sales question eitherby using the logical operator AND for all the questions; or usingthe logical operator OR for all the questions. So it's alwayseither AND or OR but never mixed together.We can use Northwind database for my question, it is very similarto the structure of the problem on the database I am working on.IF(SELECT OBJECT_ID('REPORT')) IS NOT NULLDROP TABLE REPORT_SELECTIONGOCREATE TABLE REPORT_SELECTION(AUTOID INT IDENTITY(1, 1) NOT NULL,REPSELNO INT NOT NULL, -- Idenitifies which report query this-- "sales question" is part ofSupplierID INT NOT NULL, -- from the Suppliers tableProductID INT NOT NULL, -- from the Products table, if you choose--a ProductID, SupplierID is selected also by inheritenceCategoryID INT NOT NULL, -- from the Categories tableSOLDDFROM DATETIME NULL, -- Sold from which dateSOLDTO DATETIME NULL, -- Sold to which dateMINSALES INT NOT NULL, -- The minimum amount of salesMAXSALES INT NOT NULL, -- The maximum amount of salesOPERATOR TINYINT NOT NULL -- 1 is logical operator AND, 2 is OR)GOINSERT INTO REPORT_SELECTIONSELECT 1, 1, 2, 1, '1/1/1996', '1/1/2000', 10, 10000, 1 UNION ALLSELECT 1, -1, -1, 1, '1/1/1996', '1/1/2000', 10, 1000, 1You can ask all kinds of sales questions like:1-I want all employees that sold products from supplierID 1(Exotic Liquids), specifically the ProductID 2 (Chang) from theCategoryID 1 (Beverages) between Jan 1 1996 to Jan 1 2000 and soldbetween $10 and $10000 - AND for my 2nd sales question2-I want all employees that sold CategoryID 1 (beverages) betweenJan 1 1996 to Jan 1 2000 and sold between $10 and $1000I want to get the common result of both questions and find outwhich employee(s) are in this list.Here are some of the points:1-I want my query to return the list of employees fitting theresult of my sales question(s).2-If I ask three questions with the logical operator AND, I wantthe list of employees that are common to all three questions.3-If I ask 2-3-4. questions with the logical operator OR, I wantthe list of employees that are in the list of the 1st "successful"sales question (the first question that returns any employee isgood enough)4-You can ask all kind of sales question you want even if theycontradict each other. The SP should still run and returnnothing if that is the case.5-Let's assume you can have the same product name from the samesupplier but under different categories. So entering a ProductIDshould not automatically enter the CategoryID also; whereasentering the ProductID should automatically enter its SupplierID.6-SOLDFROM, SOLDTO, MINSALES, MAXSALES, OPERATOR are mandatoryfields, you can't leave them NULL7-SupplierID, ProductID and CategoryID are the dynamic inputparameters, there can be 5 different combinations to choose from:a-SupplierID onlyb-SupplierID and a ProductID,c-SupplierID and a CategoryIDd-SupplierID, ProductID and a CategoryIDe-CategoryID onlyf-Any time you choose a ProductID, the SupplierID valuewill be filled automatically based on the ProductID'srelationshipg-Any of the three values here that is not chosen by theuser will take a default value of -1 (meaning return ALLfor this Column, in other words don't filter by this column)The major problem I have is I can't use dynamic SQL for choosingthe three dynamic columns as the 2nd row of records would have adifferent selection of dynamic columns (at least I don't know howif the solution is dynamic SQL). The only solution I can think oflooks pretty bad to me. I would use a cursor, run each row at atime, store a TRUE, FALSE value to stop processing or not andstore the result in another detail table. Then if all ANDquestions have ended with TRUE do a union of all the result andreturn the common list of employees. It sounds pretty awful as anapproach. I am hoping there's a simpler method for achieving this.Does anyone know if any SQL book has a topic on this type ofquery? If so I'll definitely buy the book.I appreciate any help you can provide.Thank you

View 7 Replies View Related







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