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


ADVERTISEMENT

Using A Excel Source To Get The Data From An Excel File Gets Null Values For A Couple Columns

Nov 19, 2007

I am using a Excel Source to get the data from an excel file to sql server 2005 table. A couple columns are coming in a double precision float, but some values have characters in them, but those values are coming out as null, even though I changed the datatype from float to unicode string. Any inputs on resolving this will be much appreciated.

Thanks,
Manisha

View 4 Replies View Related

How To Pass The Excel Sheet Names To The Excel Source Control Through Variables

Feb 22, 2006

I am trying to get the contents of the Excel Files dynamically and dumping into the SQL Database using SSIS. Through WMI Event Watcher, I could find when one or more Excel files dumped in a particular folder and using ForEach Loop Container I was able to take all the filenames and pass it through Variables. But at the same time in the Data Flow, I have to pass each Sheet of an Excel File to the Excel Source control and export the data to my SQL Database using OLEDB Destination.

For that I need to get the names of each sheets in an Excel File and pass it to the Excel Source Control through variables. But when I give Data Access Mode as "Table name or view name variable" and provide the variable name in that, then it is giving an error message as "A destination table name has not been provided".

And at the same time, Since I was not able to provide an static Filename (as I am passing through Variables), when I tried to map the columns in the OleDB Destination, it is not allowing me to map the columns.

So all these things I should do at Run-time using Variables in SSIS. I don't want to hard-code any filenames or Sheet names. If any one of you have a solution, please share with me.







Thanks & Regards,

Prakash Srinivasan

View 3 Replies View Related

Problem With Retreving A Excel Data Through Excel Source Component.

Sep 18, 2007



Hello,

I have a problem with retreving a excel data through excel source component.

I have source component as Excel Source which will connect to my .xls sheet.
To retrieve the values from the sheet i am using a query as,
"SELECT F14,F3 FROM [Charac Defn & Assgnment$]"

The column F14 is not formatted so that the format of the cell is "General" I have a different type of values in the F14 column such as "PE","PES",15,20,20.00,8888.9999 etc..
While i click on preview button of Excel source it shows only the text values and not the int or decimal values, its returning NULL for those cells. I tried to use convert function, its throwing an error as

TITLE: Microsoft Visual Studio
------------------------------
There was an error displaying the preview.
------------------------------
ADDITIONAL INFORMATION:
Undefined function 'Convert' in expression. (Microsoft JET Database Engine)


Is there any other function to change the format of the cell or i need to some thing else
Please help me how to solve this issue.

View 6 Replies View Related

SSIS - DataFlowTask - Excel Source - Dynamic Excel Template

Mar 13, 2008



Hi,

I am creating an SSIS package witha a Dataflow task, which reads from an Excel source and then uses script component to dumpt the data to multiple tables in Sql Server database

I need to some how make my Excel source dynamic, that is my excel template which i would be using to map the excel columns to script component's input columns would be dynamic..

In other words, I should be able to define the Excel Source, Column Mapping Information, Precedence constraint to the Script component dynamically

Please suggest how could i accomplish this


Regards,
Kalyan

View 8 Replies View Related

Integration Services :: Excel Column Turns To Blank / NULL While Import Using SSIS Excel Source 2008

Jul 6, 2015

While importing data from Excel source , some column is getting null value even though excel column has value.To Resolve the issue we tried with

HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoftOffice14.0Access Connectivity EngineEnginesExcel

1.Change the Value  of the Row TypeGuessRows from 8 (Default value) to 0  and ImportMixedType = text

• xls
HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcel

1.Change the Value  of the Row TypeGuessRows from 8 (Default value) to 0  and ImportMixedType = text

the connection string of the excel

UPPER(REVERSE(SUBSTRING( REVERSE(@[User::VarInputExcelFile]), 1, 5) ) ) == ".XLSX" ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::VarInputExcelFile] + ";Extended Properties="Excel 12.0;HDR=Yes;IMEX=1";":"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + @[User::VarInputExcelFile] + ";Extended Properties="EXCEL 8.0;HDR=Yes;IMEX=1";"

by doing the above setting also , the column is coming as null from excel source even though there is data in excel.

View 2 Replies View Related

Certain Numeric Fields Not Read From The Excel File When Using A Excel File Source.

Jul 20, 2006

I have the Excel Connection Manager and Source to read the contents from an Excel file. For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150. I am not sure why this is happening. I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.

All the other content from the excel file is coming thru except for the 2 numeric fields.

I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.

Any inputs on getting this addressed will be much appreciated.

Thanks,

Manisha

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

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

SQL Data Source Parameters Problem

Jan 21, 2008

 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.  

View 3 Replies View Related

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

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

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

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

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

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

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

DTS With Excel File As Source

Feb 3, 1999

I'm trying to write a DTS package that reads data from an excel spreadsheet. I'm having a problem getting all the data from the spreadsheet, seems that OLE DB is "too" smart. There is one column that has either numeric values or text values in its row cells. When I browse the spreadsheet in DTS (transform properties, browse button) I only see the text values. OLE DB has placed nulls or blanks into the cells with the numeric values. If I edit the spreadsheet to change the column header to contain a number, then the browse window shows only the numeric values and blanks out the text values. Any suggestion on how to get OLE DB/DTS to treat the numeric values as text? In the spreadsheet, I've tried changing the cell formats to text and to general. This had no effect.

View 1 Replies View Related

OLE DB Source &&amp; Excel Destination

May 3, 2006

Hi,

My OLE DB Source and Excel desintation values all will be assigned during the run time but it does work during design time but as on runtime columns are different. That's why it does not work.

Here is what I want to accomplish, I have table which contains all my report which needs to dumped to excel at the month end.

SQL Task using ADO enumrator read one record(one report), Give that record to For Each contair which Create the Excel file on the fly using one of variable from my table and uses a stored procedure to dump data to excel using Dataflow Task.

xlsQuery

CREATE TABLE `Sheet1` ( `FiscalYear` Short, `FiscalPeriod` Byte, `STORE #` Short, `Total Markups` Decimal(15,2), `Less Markdown SubTotal` Decimal(15,2), `Total Markup` Decimal(15,2) ) GO

sqlQuery

Exec Report.MyReport 1

Does it mean for 10 reports, I have to create 10 different data flow tasks, or it can be done using one data flow tasks but changing columns on the run time.

Please Help

Thanks

Shafiq



View 10 Replies View Related

SSIS - Excel Source

Dec 28, 2007

Dear Friends,
I need to import data from several excel files. How can I configure excel source object to dinamically import each file?
The name of the file will be in a parameter of ssis package and this name change frequently, and ach time the filename change I dont want to change the configuration on the excel source?
What you sugest?
Shoul I use a script component as source?!
Regards!

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

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

Excel File As Data Source

May 19, 2007

Hi everyone!
I am trying to import data into my sqlserver 2005 database from an Excel 2000 file. The database is empty. I am using the worksheets from the file to create the tables and copy the rows. I am getting follwing errors:
- Pre-execute (Error)


Messages
Error 0xc0202009: {674E15E4-102E-4935-90A2-8B1FFFEFB11D}: An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".(SQL Server Import and Export Wizard)
Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009.(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Source 64 - vw_TempOrderDetails" (5280) failed the pre-execute phase and returned error code 0xC020801C.(SQL Server Import and Export Wizard) 
 
Any suggestion is most welcome.
Regards

View 5 Replies View Related

Excel Source With Optional Columns

Jan 31, 2007

Hi:

I use a SSIS package to loop thro a folder and load data from multiple excel files to a SQL2005 table. Works fine except when an excel has a missing col.

Col names in xls are always a subset of col names in the table. The missing cols are random, else I would just have made another package:-)

Once a missing column is found, I get runtime and design time errors, and metadata problems. How can a get SSIS to ignore missing columns?

TIA





View 3 Replies View Related

Blank Row Comming Out From Excel Source?

Jan 20, 2006

Hi,

I use an excel datasource to populate some simple dimensions, but when i extract the excel file i get alot of blank rows from the excel files...

How can i overcome this issue? Is this normal?

I never had problems like this using DTS in the 2000 version

Best Regards,

Luis Simões

View 6 Replies View Related

Using .xlsx As Excel Source In SSIS

Oct 17, 2007



Hi,

I am using Excel datasource and Excel destination in a simple SSIS package which i created. I have got two queries regarding this


How does excel datasouce/destination accept .xlsx files, since i observed that they accept only .xsl file

Does excel destination ascept .xlsm files, because i have some macros enabled in destination
Thanks in advance for advice

Warm Regards,
gchanduu.

View 3 Replies View Related

Errors Reading From An Excel Source

Nov 27, 2007



Hi,

I have an Excel spreasheet - the first column is text, the second numbers, the third a mix of the two. If I point An Excel Source at this in my data flow, it will import the first two columns without problem, but not the third: all cells containing text are being imported as nulls, but those containing numbers are imported just fine.

Even if the numbers are stored as text, they are converted into numbers at import and genuine text is still discarded. It's treated as if the entire column is numeric if there's just one numeric value in it.

I can get around this by creating a .csv or .txt file from the excel file, but that will add an extra layer of admin to this process and I'm tryuing to make it as seamless as possible

View 3 Replies View Related







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