The Problems Puzzled Me A Lot
Dec 1, 2006
HI, i'm new to SSIS,i got some problems puzzled me a lot, when i drag a OLEDB destination in the data flow task, i select a table or view as the destination or write a sql like "select * from destination_table", my purpose is only to get the fields to map for, why SSIS retrive all data from the destination table,the consequence of that is the great consumption of RAM,if the number of records of the target table exceed 1.000.000,SSIS always terminated with a 'out of memory' info,my laptop has 2GB ram,i have already solve the problem by writing the sql like "select * from destination_table where 1=0" in oledb destination,but i still want to know the cause why SSIS doing that?
And the other problem i encountered is the efficiency of inserting data,the normal speed is about 10.000 rows per minute,you know it is very slow,anyone who knows how to perform well,my destination database is oracle.
Thanks a lot!
View 1 Replies
Jul 20, 2005
I'm hoping somebody can explain exactly what's going on here - I can'tfind it documented anywhere.Go to the Northwind database, and run the following SQL:create index IX_UnitPrice on [order details](unitprice)Now, turn on SHOWPLAN (either graphical or text, it doesn't matter),and run the following query:select * from [order details]where unitprice = 2Output:StmtText|--Index Seek(OBJECT: ([Northwind].[dbo].[OrderDetails].[IX_UnitPrice]), SEEK: ([OrderDetails].[UnitPrice]=Convert([@1])) ORDERED FORWARD)Now, alter the SARG slightly by making it a float:select unitprice from [order details]where unitprice = 2.000Output:StmtText|--Nested Loops(Inner Join, OUTER REFERENCES: ([Expr1003], [Expr1004],[Expr1005]))|--Compute Scalar(DEFINE: ([Expr1003]=Convert(Convert([@1]))-1.00,[Expr1004]=Convert(Convert([@1]))+1.00, [Expr1005]=If(Convert(Convert([@1]))-1.00=NULL) then 0 else 6|If(Convert(Convert([@1]))+1.00=NULL) then 0 else 10))| |--Constant Scan|--Index Seek(OBJECT: ([Northwind].[dbo].[OrderDetails].[IX_UnitPrice]), SEEK: ([Order Details].[UnitPrice] >[Expr1003] AND [Order Details].[UnitPrice] < [Expr1004]), WHERE:(Convert([Order Details].[UnitPrice])=Convert([@1])) ORDERED FORWARD)Right. I understand that in both cases the SARG datatype is differentfrom the column datatype (which is money), and that in the firstexample the SARG constant gets implicitly converted from int -> money(following the datatype hierarchy rules), and so the index can stillbe used.In the second example, the datatype hierarchy dictates that money islower than float, so the table column gets implicitly converted frommoney -> float, which strictly speaking disallows the use of the indexon that column.What I DON'T understand is what exactly all that gubbins about theexpressions (especially the definition of [Expr1005] is all about; howdoes that statement decide whether Expr1005 is going to be NULL, 6, or10?I'm soon going to be giving some worked tutorials on index selectionand use of Showplan to our developers, and being a bolshi lot they'rebound to want to know exactly what all that output means. I'd ratherbe able to tell them than to say I don't actually know!How about it someone?Thanks,Phil
View 4 Replies
View Related
Jul 12, 2006
I created a package, created an XML Config file, created a deployment utility and then deployed to server. I scheduled it with agent and I get this error (changed the package name in the error to IMPORT to make it more readable):
Description: The configuration file name "C:Documents and SettingschrisMy DocumentsVisual Studio 2005ProjectsIMPORTIMPORTIMPORT.dtsConfig" is not valid. Check the configuration file name. End Warning Warning: 2006-07-12 13:11:39.96 Code: 0x80012059 Source: IMPORT Description: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed. End Warning Progress: 2006-07-12 13:11:40.56 Source: Import Data Validating: 0% complete End Progress Progress: 2006-07-12 13:11:40.56 Source:... Process Exit Code 1. The step failed.
So basically its trying to point to a config file on my dev workstation. When I created the deploy utility it created a config file in the Deployment directory with the Manifest and the package. I installed from the deployment directory. When installing it asked where I wanted to install and I left it the default of "C:Program FilesMicrosoft SQL Server90DTSPackagesIMPORT" and if I look in that folder the IMPORT.dtsconfig is there.
Ideas of where I went wrong? I want to make sure I understand the whole configuration file notion correctly as I am starting to roll a bunch of packages with config files out to production. I've done the tutorial and thought it was straightforward and made sense. I can fix it by editing the xml of the package and removing the line pointing to my dev box - just want to know what I am doing wrong :)
View 1 Replies
View Related
May 6, 2006
I have a puzzle in my mind here. I will thank anyone who can solve my puzzle.
I am not familiar with SQL and its theories behind, so please bear with me if I am asking a stupid newbie question.
My puzzle is generally a problem of generating sequence numbers. The following SQL is only a stripped down version - it fetches the max number, add 1 to it and updates the table with the new number.
DECLARE @max int
SELECT @max = MAX(next_number) + 1 from sequence_numbers
UPDATE sequence_numbers SET next_number = @max WHERE next_number = @max
Now if user1 gets 100 and user2 also gets 100 and they both try to update the table, what would happen? I fear that the result would be 101.
One of my coworker thinks that adding 'WHERE next_number = @max' can solve the conflict - user2 will fail. His reasoning is like this:
After user1 updates the table, next_number would be 101 and user2's update will fail because his WHERE criteria is still 100.
But I think user2 still sees the old data (100) and still succeeds and thus both users update the table with number 101.
Thanks.
View 10 Replies
View Related
Nov 5, 2006
I have tried installed several times the SQL Server 2005 Express (Standard or Advanced Services) on my XP SP2 system and all failed.
The error messages are all about the Secure Socket Layer certificate and provider, either cannot find, create valid SSL certificate or the authentication provider is not trust (actually I don't know any of this and where to find them). I am learning at home the SQL Server and not going to do a website. Why I need a SSL? Or if it is really necessary, how do I obtained one?
I installed IIS (supposedly 5.0 since I have XP SP2), is this related? How to install without IIS?
Or do I need a Server OS?
View 3 Replies
View Related
