Can Queries Interfere With My Custom Etl?
Oct 15, 2007
I've developed some custom etl in an sp that "trickles" data from an "intake" table to an "archive". The sp will be controlled by windows task scheduler and it's run frequency will depend on user need case by case.
The intake table is clustered on an identity column that is a pk. It also has an index on an "occurence" date.
The archive is clustered on the occurence date and it has all intake fields except the identity column.
The etl uses set based commands that insert chunks of intake to archive, delete those chunks from intake and then commit. If my "chunks" are defined as 100 rows and I have 40000 rows to ship, I insert/delete/commit 400 times. I'm purposely not using a cursor and purposely not using a truncate.
There may be some union type queries (between intake and archive tables) running infrequently, possibly while this etl is running. Can those queries cause the etl to fail? Can the etl cause the queries to fail? Can either cause the arrival of new rows into the intake to fail and vice versa? I have no explicit locking set in the sp at this time.
View 4 Replies
ADVERTISEMENT
Jan 17, 2007
Currently I am running MSDE on a webserver. The MSDE was installed by a software package for web survey's. I am wondering if SQL express will interfere with this installation? Would I be better to install MySQL instead?
The problem I am trying to solve is simply giving a web developer some database space so that he can connect his web app to store some data. I'm kind of a n00b at this so I thought SQL express would be the best route. If I'm headed in the wrong direction can someone point me the right way?
Cheers!
View 1 Replies
View Related
Feb 24, 2007
Hi -
I may not sound desparate, but I really am. I have spent many days now trying to install and re-install Microsoft SQL 2005 Express. I still cannot connect to the database! I have read this forum and tried various suggestions, like setting the remote connections and TCP/IP + Named Pipes. Still doesnt work!
The environment is Dell Dimension 24000 (I86) with WinXPPro.
Out of desparation, I downloaded and installed Oracle 10g XE. It works just fine !
My question is: Does the Oracle database server interfere with connecting to the Microsoft SQL 2005 Express server ?
Also, I notice that there are an awful lot of questions posted on this Forum concerning just getting your software up and running. Are there that many stupid folks out there like myself, or has this application been released prematurely ?
View 3 Replies
View Related
Mar 8, 2007
Hi,
I am creating a custom transformation component, and a custom user interface for that component.
In
my custom UI, I want to show the custom properties, and allow users to
edit these properties similar to how the advanced editor shows the
properties.
I know in my UI I need to create a "Property Grid".
In
the properties of this grid, I can select the object I want to display
data for, however, the only objects that appear are the objects that I
have already created within this UI, and not the actual component
object with the custom properties.
How do I go about getting the properties for my transformation component listed in this property grid?
I am writing in C#.
View 5 Replies
View Related
Aug 14, 2007
Hi,
I've created a Custom Data Flow Component and added some Custom Properties.
I want the user to set the contents using an expression. I did some research and come up with the folowing:
Code Snippet
IDTSCustomProperty90 SourceTableProperty = ComponentMetaData.CustomPropertyCollection.New();
SourceTableProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
SourceTableProperty.Name = "SourceTable";
But it doesn't work, if I enter @[System:ackageName] in the field. It comes out "@[System:ackageName]" instead of the actual package name.
I'm also unable to find how I can tell the designer to show the Expression editor. I would like to see the elipses (...) next to my field.
Any help would be greatly appreciated!
Thank you
View 6 Replies
View Related
Apr 2, 2007
Hi,
I'm trying to enable Expression for a custom property in my custom data flow component.
Here is the code I wrote to declare the custom property:
public override void ProvideComponentProperties()
{
ComponentMetaData.RuntimeConnectionCollection.RemoveAll();
RemoveAllInputsOutputsAndCustomProperties();
IDTSCustomProperty90 prop = ComponentMetaData.CustomPropertyCollection.New();
prop.Name = "MyProperty";
prop.Description = "My property description";
prop.Value = string.Empty;
prop.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
...
}
In design mode, I can assign an expression to my custom property, but it get evaluated in design mode and not in runtime
Here is my expression (a file name based on a date contained in a user variable):
"DB" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + "\" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + ".VER"
@[User::varCurrentDate] is a DateTime variable and is assign to 0 at design time
So the expression is evaluated as: "DB189912189912.VER".
My package contains 2 data flow.
At runtime,
The first one is responsible to set a valid date in @[User::varCurrentDate] variable. (the date is 2007-01-15)
The second one contains my custom data flow component with my custom property that was set to an expression at design time
When my component get executed, my custom property value is still "DB189912189912.VER" and I expected "DB200701200701.VER"
Any idea ?
View 5 Replies
View Related
Aug 17, 2005
What I want to accomplish is that at design time the designer can enter a value for some custom property on my custom task and that this value is accessed at executing time.
View 10 Replies
View Related
Aug 16, 2006
I am writing a custom task that has some custom properties. I would like to parameterize these properties i.e. read from a varaible, so I can change these variables from a config file during runtime.
I read the documentation and it says if we set the ExpressionType to CPET_NOTIFY, it should work, but it does not seem to work. Not sure if I am missing anything. Can someone please help me?
This is what I did in the custom task
customProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
In the Editor of my custom task, under custom properties section, I expected a button with 3 dots, to click & pop-up so we can specify the expression or at least so it evaluates the variables if we give @[User::VaraibleName]
Any help on this will be very much appreciated.
Thanks
View 3 Replies
View Related
Jul 20, 2005
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
View 1 Replies
View Related
Apr 21, 2006
1) We are writing a custome Source component for Oracle with OCI calls, Could some one please let me know how to Enable Error Handling for the Same,
2) Is it possible to write Custome Error Handeling Component for SSIS? if yes could you please help me on how to write it.
Thanks in advance.
View 1 Replies
View Related
Oct 23, 2007
Hi,I have a sql statement: SELECT [ItemName], [Startprice], [Percentreduction], [Quantityavailable], [PhotoURL], [proID] FROM [items] WHERE ([featured] = @featured) but I would like to add in 2 more where clauses. One is AND (aswell as) the current one, soWHERE ([featured] = @featured) AND ([Quantityavailable > @Quantityavailable) ?(@Quantity available value set to 0)(is that right?)and also I want another AND which is taken from another column. i.e:WHERE ([featured] = @featured) AND ([Quantityavailable > @Quantityavailable) AND ([numberclickedin< *the number from the numtaken column*]) So I guess my 2 questions are:1. is the format right for my custom sql statements.2. how do I get the number from the numtaken column to dynamically enter into the third statement? Thanks,Jon
View 7 Replies
View Related
Jun 14, 2007
I have a custom DLL in C# which does a lot of calculations.
I need to use it in my DataFlowTask. I wanted to add it as a reference in ScriptComponent and use it from there, but I do not see my dll there.
The dll is sign but does not have Post-build event command filled in the properties of the project and my dll is deployed and installed to assembly.
What do I do wrong?
How can I use my dll in ScripComponent? Any other options? What am I missing?
Thanks.
View 8 Replies
View Related
Jun 9, 2007
Hi,
i used the custom sqlsitemapprovider which is in this web : http://msdn.microsoft.com/msdnmag/issues/06/02/wickedcode/default.aspx?loc=&fig=true#fig5 but in this sample they used sqlsitemapprovider as a default sitemapprovider i dont wanna use it as a default and i wanna send a paramater which is used to filter datas to that class. I modified that class and i can take a sitemapprovider back and i wanna show it a sitemappath data source programmaticaly. i wanna create an instance object of sitemappath datasource and i wanna bind it to that class and then i wanna add a treeview control to the page then i wanna bind it to the sitemapdatasource programmaticaly too..
Thanks for your attetion.
Regards...
View 2 Replies
View Related
Jun 19, 2007
If I hard code the select statement everything works fine.
<asp:SqlDataSource ID="SqlDataSourceZip" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
SelectCommand="SELECT * FROM [Station] WHERE ([ZipCode] = '80523' OR [ZipCode] = '80521' OR [ZipCode] = '80553' OR [ZipCode] = '80522' OR [ZipCode] = '80526' OR [ZipCode] = '80527' OR [ZipCode] = '80525' OR [ZipCode] = '80524' OR [ZipCode] = '80547' OR [ZipCode] = '80535' OR [ZipCode] = '80538' OR [ZipCode] = '80551' OR [ZipCode] = '80549' OR [ZipCode] = '80550' OR [ZipCode] = '80546' OR [ZipCode] = '80539' OR [ZipCode] = '80512' OR [ZipCode] = '80537' OR [ZipCode] = '80541' OR [ZipCode] = '80650' OR [ZipCode] = '80515' OR [ZipCode] = '80513' OR [ZipCode] = '80610' OR [ZipCode] = '80534' OR [ZipCode] = '80536' OR [ZipCode] = '80634' OR [ZipCode] = '80543' OR [ZipCode] = '80532' OR [ZipCode] = '80638' OR [ZipCode] = '80615' OR [ZipCode] = '80646' OR [ZipCode] = '80648' OR [ZipCode] = '80612' OR [ZipCode] = '80631' OR [ZipCode] = '80528')"> </asp:SqlDataSource>
Want to use a label control to return data, but can't find anything that works.
<asp:Label ID="zipLabel" runat="server"></asp:Label>
[ZipCode] = '80523' OR [ZipCode] = '80521' OR [ZipCode] = '80553' OR [ZipCode] = '80522' OR [ZipCode] = '80526' OR [ZipCode] = '80527' OR [ZipCode] = '80525' OR [ZipCode] = '80524' OR [ZipCode] = '80547' OR [ZipCode] = '80535' OR [ZipCode] = '80538' OR [ZipCode] = '80551' OR [ZipCode] = '80549' OR [ZipCode] = '80550' OR [ZipCode] = '80546' OR [ZipCode] = '80539' OR [ZipCode] = '80512' OR [ZipCode] = '80537' OR [ZipCode] = '80541' OR [ZipCode] = '80650' OR [ZipCode] = '80515' OR [ZipCode] = '80513' OR [ZipCode] = '80610' OR [ZipCode] = '80534' OR [ZipCode] = '80536' OR [ZipCode] = '80634' OR [ZipCode] = '80543' OR [ZipCode] = '80532' OR [ZipCode] = '80638' OR [ZipCode] = '80615' OR [ZipCode] = '80646' OR [ZipCode] = '80648' OR [ZipCode] = '80612' OR [ZipCode] = '80631' OR [ZipCode] = '80528'
something like this, but it don't work.
SelectCommand="SELECT * FROM [Station] WHERE (<%=zipLabel.Text%>)"> </asp:SqlDataSource>
View 3 Replies
View Related
May 23, 2005
Im in the process of trying to teach myself SqlServer, comming from Oracle. How the heck do I get the equivlent of %ROWNUM pseudo-column in SqlServer? Top just isn't doing it for me.
Oracle Example wrote:
Select * from foo where foo%ROWNUM > 10 and foo%ROWNUM <20;
View 12 Replies
View Related
Apr 4, 2006
Not sure where to post this as it may be a form operation but here goes...I want to generate a custom unique identifier for a table similar to the Northwind Customers database (ie ALFKI, ANTON, etc...) which can be used as a descriptive identifier as well. For example, I have a Products table and I want to have my keys look like: CHR-0001-05."CHR" is my abbreviation for Chairs"0001" is the auto-incrementing number (in this case the first record of the table)"05" is the last 2 digits of the year recieved (2005)Can somebody please point me in the right direction? I'm having problems finding resources on the web relating to this topic (prolly not using the correct keywords for my searches)Cheers =)
View 1 Replies
View Related
Oct 5, 2000
Would anyone be aware of anyplace I could find some good information on creating DTS custom tasks? I've come across a couple of articles from SQL Server Magazine, but nothing too substantive...
Better yet, if anyone has any success (great or small), I'd like to hear from you and hear some of the things you did, what your custom task does, difficulty.
I'm just trying to get an idea of how much work I have ahead of me....
Thank You...
View 2 Replies
View Related
Jul 11, 2005
Hi guys.
I want to create a top 20 product list from a few thousand products. I want the rest of the products to be grouped into 'others'...
I also want the products to be ordered by the facts in the cube. Thus the product dimension would dynamically change depending on the Time dimension thats being selected.
is this plausible ?
Thanks
Tom
View 1 Replies
View Related
Jul 5, 2002
I've created a custom task in VB6 and compiled the DLL. wHen I run the task in DTS designer as a step it runs ok, however when I try to exectue the whole package nothing happens??
View 1 Replies
View Related
Jun 4, 2007
Hi All!
I was thinking of creating a custom generated IDs for my table. I would like the ID to be something like "HR001" or "IT001", the two letter prefix would indicate the dept it belongs to. Initially I thought of having a table that will hold all the seed values for the IDs but I realize that this could have some concurrency problems if there will be a multiple number of users are creating a record at the same time. So now I have totally no idea on how to deal with the concurrency problem.
I will not be using this as a primary key because I already have the Identity field to be my primary key, though of course this field would definitely be unique. I will just be using this to display in the UI.
Any inputs would be greatly appreciated.
Thanks!
View 16 Replies
View Related
Jan 18, 2007
Hi,
I am showing my report in the web application using Report Viewer control.In the report i have start date and End date parameters.While running the report if i give startdate as '45/66/20007' it is displaying a message which is not user friendly.Is there is any way we can handle this type of errors and display our own custom error messages.
Thanks in advance
View 8 Replies
View Related
Oct 24, 2005
I am trying to design a Component which can be configured by a UI. After Configuration from the Design pane the Data should be passed to the back-end of the Component.
View 10 Replies
View Related
May 17, 2007
I am trying to use custom code on a report which is:
Public Function Percentage(decValueOne As Double, decValueTwo As Double) As Object
If (decValueOne = 0 Or decValueTwo = 0) Then
Percentage = 0
Else
Percentage = (decValueOne - decValueTwo)
End If
End Function
In my textbox I have:
=Code.Percentage(SUM(Fields!PreviousYTDExpenseAmount.Value, "Template_OutputData_Sales"), SUM(Fields!PreviousYTDExpenseAmount.Value, "Template_OutputData_CGS"))
When I run the report in design mode, I get the "#Error" on that report field.
Any help with why this is happening would be greatly appreciated!
View 2 Replies
View Related
Jun 22, 2007
Hi All, I have a sample Flat File, need to get a custom record layout out of it
Sample FlatFile Input:
============
RecordA.....
RecordB.....
OwnerAB.....
RecordC.....
RecordD.....
OwnerCD.....
......
Custom Output:
============
OwnerAB RecordA ............RecordB.....
OwberCD RecordC ..........RecordD..............
............
TIA
View 1 Replies
View Related
Jul 18, 2006
I'm wondering if there is any way in RS to create what Crystal reports calls Custom or Named Groups. This functionality allows you to define groups based on the values found in one of the fields on your report. For example, if I were grouping on a person's last name and I want 3 groups based on the first letter: A-G, H-P, Q-Z. I would want to keep it contained in one table.
View 6 Replies
View Related
Jan 9, 2006
Friends,
First off, congrats and thank you to everyone at Microsoft for all of the hard work they have put into Sql Server 2005 and .NET 2.0 - it is simply amazing technology.
On that note, I was wondering if it was possible to create my own custom exceptions that I can throw in my stored procedures and then catch in my application code?
For example, say I wanted to create a Custom Sql Exception called "DuplicateEmailInSqlDatabaseTableException" and then, within a stored procedure where data is being attempted to be inserted, I could check for a duplicate email record and then throw the exception. At that point, I would like to be able to catch that exception in my C# data layer and work from there.
Is this possible? I feel like it could be but am unsure where to start.
Shaun C McDonnell
View 5 Replies
View Related
Aug 8, 2006
Hi All,Is it possible to create SSIS custom component with ATL COM dll with MFC support?I'm not familar with C# & VB# languages :-(Regards,Svilen Varbanov
View 2 Replies
View Related
Sep 3, 2007
i have this action.jumpto url
="javascript:void window.open('http://bklc-crm01:5555/cs/cases/edit.aspx?id={" & cstr(Fields!incidentid.Value) & "}','_self')"
for some reason when i use this field (incidentid.value) there is no hyperlink generated, however when i change the value to another field, the hyperlink works, what shall i be looking for, incident value is a set of number with dashes!
View 1 Replies
View Related
Jun 27, 2006
I have created custom form without borders , i have written functions to minimize, maximize, exit and even for moving the window, now i need to write function to resize the window , is there any way resize the window...
View 1 Replies
View Related
Oct 25, 2007
Hi,
I am using a matrix to report, and there is 2 rowgroups and onde column group. It looks like this:
T1 T2 T3 T4
type1 A 9 3 2 4
B 6
type2 A 1 2 3
B 2 4 1 7
But I need to sum only 2 columns of the report (columns T2 and T3):
T1 T2 T3 T4 T2+T3
type1 A 9 3 2 4 5
B 6 6
C
type2 A 1 2 3 2
B 2 4 1 7 5
C 8
And I do not know how to create this sum column, only the default sobtotal column of a matrix.
I tried to do this sum in my datasource, but with no success too.
I also tried to use a table, but the columns must be create automatically from database data and I couldn't do it.
Another issue is: how to make line C appear even with only null or zero numbers?
Any ideas?
I am new with reporting services, so please be clear and detailed.
Thanks a lot!
View 1 Replies
View Related
Mar 28, 2006
Hi,
If I implement a custom editor by inheriting from UITypeEditor, Can I invoke it at RUN TIME from a standard control other than PropertyGrid, say a comboBox or a button ?
If it is possible, could you please let me know how to do it or could you please direct me to some URL which shows how to do it.
Do I have to create a custom control that inherits from control for doing this ?
Thank you.
regards.
S
View 1 Replies
View Related
Jan 22, 2008
Hi again, All!!
I'm having a problem with problems with some custom components I built. I have a custom Connection Manager and Data Flow Source that I have built.
My local PC is 64-bit, but I followed the MS instructions on building and deploying custom components. They run fine inside of VS2005, as long as I have 'Run64BitRuntime" set to 'False'. When I try to run packages using the custom components in 64-bit mode, I get:
Error: 0xC0014005 at : The connection type "<MyConnectionManagerType>" specified for connection manager "PRI" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
I suspect I need to either change the way I build or deploy the custom components. Can someone shed some light on this?
Thanks,
Frank
View 1 Replies
View Related
Aug 9, 2007
I have a product purchased which has it's own sp_adduser stored procedure to add users to a table (seperate from sql server).
When the program tries to run sp_adduser, it executes the SQL 2005 sp_adduser and this is not what I want.
Is there a way to turn off sp_adduser for a certain user, or force it to use the local copy?
This program is compiled asp.net code and I cannot change the name of the procedure it calls.
Thanks,
Jimmy Cartrette
View 4 Replies
View Related
