Hi I installed the add-in for excel data mining but when i try to select a table, in the tab of analyze, dont have any properties. please help me.
carlos of southamerica
Did you select a cell within a table?
Do you see "Table Tools" above the ribbon?
Do you see "Analyze" and "Design" ribbons? (they are usually the last 2 ribbons)
If all answers are yes, what do you see in the Analyze ribbon?
Hi Guys
I am trying to do the following and am quite new to SSIS.
I have to select a dataset from a database on server A, check if it exists on server B and perform an Update or Insert dependant on the existence.
I have created a SQL task to do the Select from server A with the results set passed to a variable of Vendors. I have added a ForEach Loop container with an enumerator of Foreach ADO Enumerator and the source variable is set to Vendors.
I have created 2 variables in the Foreach Loop called Code and Supplier - both as strings - as there are 2 fields from the initial Select that need to be passed to the final Update/ Insert.
I have then created another SQL task insert the Foreach which will perform the Update/Insert.
obviously when I run it at the moment it performs the Update/ Insert but just adds the rows with both Code and Supplier as NULL.
having looked at a couple of examples in books I have i know i need to add something in the Expressions of the Update/Insert SQL task but it is here i get a bit lost.
Which of the properties from the drop down do i need to use to map the variables against?
Any help would be massively appreciated asI am tearing my hair out!
Thanks
Scott
Hi Scott,
We're all still learning SSIS. 
It sounds like you're most of the way there.
There are a couple ways to approach this solution. The simplest way, from what I understand from your post, is to use placeholders and parameters in your Update/Insert statements. If you already have the Code and Supplier variables defined, you could perform an insert using an Execute SQL Task with something similar to the following code:
Code Snippet
INSERT INTO Vendors
(Code, Supplier)
VALUES(?, ?)
You could then supply Parameters:
Code Snippet
VariableName Direction DataType ParameterName ParameterSize
User::Code Input Int 0 -1
User::Supplier Input VarChar 1 -1
This would substitute the question marks in the SQL Statement property with the values contained in your variables.
Hope this helps,
Andy
|||Scott,
Any special reason for not using a dataflow with a lookup transform to detect if the rows exists(update) or not (insert). That is by far a pretty common practice in these scenarios.
|||Hi Rafael
Still new to this (and database stuff as a whole) and am going on someone elses advice!
I have looked at your suggestion and have got as far as the following:
OLEDB Source with a SQL select statement to return the data required
Look Up transform to look up the 2 columns from the Select against the destination table
After that I am a bit lost. I guess i have to add a OLEDB destination but do I do it to a table or a SQL Command?
thanks again
Scott
|||I think you are on the right track. I would add an OLE DB Destination against the destination table.
Keep in mind you have to tweak the lookup to 'redirect' errors. Lookup will treat the no matches as errors; hence will be send to the error output of the component (red arrow). Then you have to connect the error output of the Lup to the input of the destination.
Now the updates; every row going to the green output of the L.up is an existing/to-updated row. Here you have 2 options; use an OLE DB Commnad to update the row in the destination table; or send those rows to an estiging table (yes a seconf OLE DB Destination) and then back in control flow use an Execute SQl task to do a 1 time update. The advantage of the second method is performance. the Update runs 1 time updating all the required rows. The First one will perform an update for every row passing trhough; wich depending on the volume of data can be performance killer; the good thing is that you don't need a second table.
This thread has some examples
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1
I am trying to use the Foreach File Enumerator and map it to a user variable.
I select a folder in the colletion.
I use *.* for file name.
I left the default value for retrive file name.
I assigned it to my user variable ( data type string)
I am get the following error "The element cannot be found in a collection"
I am sure that I have at lest 10 file in the folder.
Can anyone tell me why I get the error?
I found out what my problem was
I had to assign a value to the variable.
The Foreach File Enumerator did not set the value when i mapped it to the variable.
I had to type in a value for the variable after I created it.
I'm trying to select 5 rows of data from a query. Sometimes there is less than 5 rows of data in the result set.
Is there a way to FORCE a return of 5 rows - even if they don't exist? For example, returning some text such as "No Data" or NULL in the result set?
What I'm doing to return 5 rows of data:
Select top 5 *
From MyTable
I need help modifying this query to make sure I always get 5 rows of data.
Thanks!
There is no pre-defined settings available but you do something below,
Code Snippet
Create table #Data(
Id int,
Name varchar(100)
)
Insert Into #Data Values(1,100)
Insert Into #Data Values(2,100)
Insert Into #Data Values(3,100)
Select Top 5 * From
(
Select Id, Name from #Data
Union ALL
Select NULL, NULL
Union ALL
Select NULL, NULL
Union ALL
Select NULL, NULL
Union ALL
Select NULL, NULL
Union ALL
Select NULL, NULL
)
as Data
Order By Case When Id is NULL Then 1 Else 0 End , ID
|||
Code Snippet
CREATE TABLE #temp (test int)
INSERT INTO #temp SELECT 1
INSERT INTO #temp SELECT 2
INSERT INTO #temp SELECT 3
DECLARE @.counter as int
set @.counter = (SELECT COUNT(*) from #temp)
SELECT * FROM #temp
WHILE @.counter < 5
BEGIN
INSERT INTO #temp SELECT NULL
SET @.counter = @.counter + 1
END
SELECT * FROM #temp
DROP TABLE #temp
Adamus
|||Thanks for the prompt replies - both of these replies were helpful and answered my question!Hi,
I have just run the 2 queries from the BOL to test the forceseek option:
USE AdventureWorks;
GO
SELECT*
FROM Sales.SalesOrderHeader AS h
INNERJOIN Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.TotalDue > 100
AND(d.OrderQty > 5 OR d.LineTotal < 1000.00);
GO
SELECT*
FROM Sales.SalesOrderHeader AS h
INNERJOIN Sales.SalesOrderDetail AS d WITH(FORCESEEK)
ON h.SalesOrderID = d.SalesOrderID
WHERE h.TotalDue > 100
AND(d.OrderQty > 5 OR d.LineTotal < 1000.00);
GO
When I take a look at the execution plan, the first query has a cost of 20% and the forceseek take 80% of the total cost.
Also the number of logical read jump from 1238 to 66194!!!
So the forceseek option has a bad impact on the query.
The BOL says if there is a lot of IO, using the forceseek can provide better performance... but in this case its not so clear...
can you explain more in detail when its better to use the forceseek?
Thanks.
The cost increases,but if you look, the index change of clustered index scan to clustered index seek and if you compare the estimated I/O cust and estimated CPU Cost you can see the differences.The number reduce....
|||Actually this is expected behavior. We have approx 30k rows of Order Header with approx 120k with order lines. What you tell SQL Server in the latter query, is to ignore the fact that we are retrieving all the rows, and still use a lookup instead of a scan. So what SQL Server does (on your demand) is to take each and every order one by one and lookup the corresponding order lines. If you looked carefully at the execution plan, you'ld see that the merge join was replaced with a inner loop join. The latter is more efficient if you lookup only a few values, but when you lookup all the values a merge join is way much faster.
As I understand the FORCESEEK its a way of forcing seeking in the index the few times that SQL Server believes it has to read more or less the whole table, while it actually is going to retrieve just a few rows.
Anybody is free to correct me if I'm wrong on this one.
Edit: That said, when I run a trace it actually seems that the last query is the faster, I would guess that is since all the data already are in memory.
Hints are there to be used when the optimizer produces a less than optimal plan and, despite over two decades of effort, query optimizers still can't get the 100% best plan 100% of the time.
So what is FORCESEEK intended to deal with? The optimizer is always working with imperfect data about the values in the table. And when you start doing a join using multiple predicates that include operators such as "<" and ">" its ability to accurately estimate the number of rows that will be touched declines. Particularly true if you don't have up to date statistics on some of the columns, or if you encounter one of the rare instances where the histograms don't capture enough information to do accurate estimates. Historically (dating back to the System R research project) if you don't have statistics that indicate otherwise then an operator such as ">" is considered to be true for 1/3 of the data. Thus ORing two such conditions would lead to an assumption that 2/3 of the table was going to be retrieved which clearly would lead to a scan over a seek. This is a grand oversimplification of how things work in this day and age, but the basics help you understand why the optimizer might choose the wrong plan. It might think that 2/3 of the rows must be touched while you know that based on the actual data it is more like .01%. The bottom line being, there are cases where the optimizer will think that a scan offers the best performance but where human knowledge of the data makes it clear that a seek is the better alternative.
Using the optimizer's own statistics to see if FORCESEEK is better or not is a mistake. The reason the optimizer didn't choose the seek strategy on its own is that it estimated it would take a lot more I/O and rejected that strategy. So when you use FORCESEEK and look at the estimates what you see are the (incorrect) estimates that the optimizer saw when it considered this strategy on its own. You just can't use that data, you need to look at the actual performance of the query.
Hal
sqlHi,
I have just run the 2 queries from the BOL to test the forceseek option:
USE AdventureWorks;
GO
SELECT*
FROM Sales.SalesOrderHeader AS h
INNERJOIN Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.TotalDue > 100
AND(d.OrderQty > 5 OR d.LineTotal < 1000.00);
GO
SELECT*
FROM Sales.SalesOrderHeader AS h
INNERJOIN Sales.SalesOrderDetail AS d WITH(FORCESEEK)
ON h.SalesOrderID = d.SalesOrderID
WHERE h.TotalDue > 100
AND(d.OrderQty > 5 OR d.LineTotal < 1000.00);
GO
When I take a look at the execution plan, the first query has a cost of 20% and the forceseek take 80% of the total cost.
Also the number of logical read jump from 1238 to 66194!!!
So the forceseek option has a bad impact on the query.
The BOL says if there is a lot of IO, using the forceseek can provide better performance... but in this case its not so clear...
can you explain more in detail when its better to use the forceseek?
Thanks.
The cost increases,but if you look, the index change of clustered index scan to clustered index seek and if you compare the estimated I/O cust and estimated CPU Cost you can see the differences.The number reduce....
|||Actually this is expected behavior. We have approx 30k rows of Order Header with approx 120k with order lines. What you tell SQL Server in the latter query, is to ignore the fact that we are retrieving all the rows, and still use a lookup instead of a scan. So what SQL Server does (on your demand) is to take each and every order one by one and lookup the corresponding order lines. If you looked carefully at the execution plan, you'ld see that the merge join was replaced with a inner loop join. The latter is more efficient if you lookup only a few values, but when you lookup all the values a merge join is way much faster.
As I understand the FORCESEEK its a way of forcing seeking in the index the few times that SQL Server believes it has to read more or less the whole table, while it actually is going to retrieve just a few rows.
Anybody is free to correct me if I'm wrong on this one.
Edit: That said, when I run a trace it actually seems that the last query is the faster, I would guess that is since all the data already are in memory.
Hints are there to be used when the optimizer produces a less than optimal plan and, despite over two decades of effort, query optimizers still can't get the 100% best plan 100% of the time.
So what is FORCESEEK intended to deal with? The optimizer is always working with imperfect data about the values in the table. And when you start doing a join using multiple predicates that include operators such as "<" and ">" its ability to accurately estimate the number of rows that will be touched declines. Particularly true if you don't have up to date statistics on some of the columns, or if you encounter one of the rare instances where the histograms don't capture enough information to do accurate estimates. Historically (dating back to the System R research project) if you don't have statistics that indicate otherwise then an operator such as ">" is considered to be true for 1/3 of the data. Thus ORing two such conditions would lead to an assumption that 2/3 of the table was going to be retrieved which clearly would lead to a scan over a seek. This is a grand oversimplification of how things work in this day and age, but the basics help you understand why the optimizer might choose the wrong plan. It might think that 2/3 of the rows must be touched while you know that based on the actual data it is more like .01%. The bottom line being, there are cases where the optimizer will think that a scan offers the best performance but where human knowledge of the data makes it clear that a seek is the better alternative.
Using the optimizer's own statistics to see if FORCESEEK is better or not is a mistake. The reason the optimizer didn't choose the seek strategy on its own is that it estimated it would take a lot more I/O and rejected that strategy. So when you use FORCESEEK and look at the estimates what you see are the (incorrect) estimates that the optimizer saw when it considered this strategy on its own. You just can't use that data, you need to look at the actual performance of the query.
Hal
My sql :
SELECT....., date_ of_ shipment,.....
FROM......
WHERE.....
ORDER BY....
After that it will export to excel.
My problem: Some times I get a column of mixing date format MMDDYY and number.?
I have to format the whole excel column to get all date format.
My question: How can I write sql so it force the the result to be date format MMDDYY in excel? Where this clause in sql (where clause ?)
Thanks
Daniel
Code Snippet
select convert(varchar(8), getdate(), 1)
OR
select replace(convert(varchar(8), getdate(), 1), '/', '')
Depending on whether you want the slash separators or not.
Use code 101 instead of 1 in the Convert if you want 4 digit years.
|||If my column name is date_of _shipment
Select convert (varchar(8), date_of_shipment,1) ?
Thnks
Daniel
|||Right.
It will also lose it's name, so you may want to re-alias it.
Code Snippet
Select convert (varchar(8), date_of_shipment,1) AS date_of_shipment
|||Thanks a millions. Look like it work great for what ever my excel column format.
Daniel
|||I may need hour: min: second too
How about MMDDYYYYHHMMSS ?
Thanks
Daniel
select a.accountid , (select street, city, state, zip from account b
where a.accountid =b.accountid for xml auto, elements) as xmldata
from account a
The idea is to return 2 columns:
accountid
xmldata (address as xml)
Assuming the fields are correct, any ideas on what the problem might be?(jonathaneggert@.hotmail.com) writes:
Quote:
Originally Posted by
The following seems to work in SQL Server 2005, but I'm getting the
error 'Incorrect syntax near xml' when I run it in SQL Server 2000.
>
>
select a.accountid , (select street, city, state, zip from account b
where a.accountid =b.accountid for xml auto, elements) as xmldata
from account a
>
The idea is to return 2 columns:
accountid
xmldata (address as xml)
>
Assuming the fields are correct, any ideas on what the problem might be?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Well thanks for such a detailed explanation of the answer.
Why can't this be done in SQL Server 2000? It is a sub-query which I
have used extensively in SQL Server 2000--why the problem with FOR XML?
Erland Sommarskog wrote:
Quote:
Originally Posted by
(jonathaneggert@.hotmail.com) writes:
Quote:
Originally Posted by
The following seems to work in SQL Server 2005, but I'm getting the
error 'Incorrect syntax near xml' when I run it in SQL Server 2000.
select a.accountid , (select street, city, state, zip from account b
where a.accountid =b.accountid for xml auto, elements) as xmldata
from account a
The idea is to return 2 columns:
accountid
xmldata (address as xml)
Assuming the fields are correct, any ideas on what the problem might be?
Quote:
Originally Posted by
Well thanks for such a detailed explanation of the answer.
>
Why can't this be done in SQL Server 2000? It is a sub-query which I
have used extensively in SQL Server 2000--why the problem with FOR XML?
This is all different in SQL 2005, where XML is a first-class citizen.
See also Books Online, the topic
XML and Internet Support ->
Retrieving and Writing XML Data ->
Retrieving XML Documents Using FOR XML ->
Guidelines for Using the FOR XML Clause
this topic lists a number of limitations with FOR XML.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
