Determining Runtime Or Design Time During Validate - Workarounds?
Sep 4, 2007
I've seen a couple of posts in this forum on this subject. If anyone knows of a workaround it would be great to hear.
The problem is this. I'm writing a component that looks a bit like an OLE DB destination: it writes to something that looks like a table. During design time I want the component to update the list of available destination columns if they change - so I want Validate to return VS_NEEDSNEWMETADATA if it detects a change. However during runtime I only want to validate that the component will run ok. So I still want to check what the destination looks like but if, say, someone has just added a column then my determination is it is ok to procede with the execution.
ValidateExternalMetadata doesn't help in this case because I still want to validate against the destination. I just don't want to raise VS_NEEDSNEWMETADATA during runtime because it aborts execution and I can determine that although there is a change to the destination it is not one that will cause the component to fail.
Any thoughts and experience on this would be great to hear!
Martin
View 4 Replies
ADVERTISEMENT
Nov 15, 2006
It appears that some of the methods in a custom source data flow component are called (repeatedly) at both design time and run time?
Is there a way to determine that "I'm in runtime" vs "I'm in design time"?
View 2 Replies
View Related
Dec 11, 2007
Does the validation time of DTSX package depend on the amount of data?
I have a data transfer task which contains a oledb source data from a SQL Server 2005 view. Then the data go through 3 Lookup transfromations before going into another view, also on SQL Server 2005 but a different database.
The purpose of this package is to load fact data, so it has to deal with few million of rows. Before setting DelayValidation to true, it takes few minutes just to open the package in BI Studio. Now with DelayValidation set to false, I can open the package without any problem. But it takes more than 5 minutes during Validation, Prepare for Execution and Pre-execution Phase. During the time the memory usage and cpu time on SQL Server goes up significantly. The CPU doesn't hit 100% though. My client machine doesn't have any significant activity.
I have similar packages ( Oledb view -> 3 or 4 lookup Transformations -> Oledb Table) but dealing with dimension data. With DelayValidaion set to False, those packages can be opened in BI Studio within few seconds. They also take only few seconds during those 3 phases before starting actual execution phase.
So I have an impression that the validation time depends on the amount of data in the database. Shouldn't it just depends on the metadata?
View 5 Replies
View Related
May 28, 2002
I am trying to find a global way of when the last time a row in one of my tables was updated or data inserted. I say global because I don't want to drill down through each table looking for modified rows.
I am a DBA of several hundred databases and want to retire those no longer being used.
Is there a column of a system table that has this info.
View 1 Replies
View Related
Jul 23, 2005
Hello All,I am getting the following error when attemping to open a table inSQL2kSP3a.________________________________________SQL Server Enterprise ManagerDatabase Server: Microsoft SQL ServerVersion: 08.00.0760Runtime Error: [Microsoft][ODBC SQL Server Driver]Invalid time format_________________________________________I cannot find it in sysmessages, or on the web.Any ideas about how to resolve this? And how it occured...Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Sep 26, 2006
hello friends!!
i want to transfer my base tables from one DB to another DB with all views and SPs and table with constraints
i generated one script for it but i want it run by dynamically..i.e attaching my base objects to newly created DB
what can i do??
I want stored procedure for it
T.I.A
View 1 Replies
View Related
Nov 26, 2002
All our SQL 7 and 2000 servers here run SQLMail for alerting and notifications (via jobs and DTS packages) and we are increasingly having the issue where MAPI32 goes to 100% CPU utilisation and the server needs rebooting.
We are getting the response from Microsoft that "this happens" - hmmm
Anyone else having this problem come up with a solution or workaround at all?
Regards
Jason
View 2 Replies
View Related
Jul 3, 2007
I have been looking over the forums looking for answers to the question that nails us all: how do you dynamically resize column widths? MS says: you don't. However, I have heard many people say they have found ways around it. I wonder if anyone might share those with me (and everyone else). Share the wisdom you have gained with those less experienced!
View 1 Replies
View Related
May 11, 2005
Hi all,
I m desiging the database first time, so how can i design the database,how i set the datatype of Datafield.(means which creteria i use for giving data type).
how can i manage the Relationship.....
plz discuss in detail.....
thanx in advance
Sajjad Rizvi
View 8 Replies
View Related
Sep 23, 2005
I have copied a long dataflow from one package to another (I wish I could reuse it without copying & pasting it, and thereupon being required to maintain two copies).
View 1 Replies
View Related
Sep 4, 2005
Hi
Could anyone tell me how can I set auto increment to a field in MSSQL at design time? So that whenever I insert a new record from VB, it will insert that new record incrementing the field which is not a primary key automatically.
Thank you very much for everyone who are constantly helping me...
View 3 Replies
View Related
Dec 20, 2007
Im trying to design my time dimension and need to add a field tohandle null dates in the fact. So if at the time of ETL the date isntknown, referential integrity will be preserved. Kimball suggestsinsterting a record in the time dimension to handle this with adescription of 'Date not available' or something like that. Howeverif users are doing inner joins on the dimension they will obvisouslybe pulling the datetime field..what should be in the datetime fieldfor this particular record?
View 4 Replies
View Related
Jul 20, 2005
Im designing a database where a user enters the date and the number ofhours and minutes he worked for the day..now i can do thisWorkdate small dateHours integerMinutes integerBut then would I have to have the front end know that when adding upthe hours and minutes for the week that 60 minutes = 1 hour or isthere some way to do this in the database?thanks-Jim
View 3 Replies
View Related
Aug 30, 2007
My SSIS package has 19 XML Source inputs constructed of 4 different (XSD) Schemas. I'm trying to find a convenient way to refresh the underlying metadata at design time so that I don't have to open each XML Source, change the XSD reference, click on the columns display, change the XSD back to the original but updated XSD just to get say a new default column width for string data. Does anyone have a quicker way to force an SSIS package to pick up changes in a referenced XSD?
View 1 Replies
View Related
Dec 22, 2006
Dear all,
i have a question concerning the design time support when accessing different oracle database schemas with SSIS.
I'm using an OLEDB-Connection, and in general everything is working fine. I would like to access two different schemas. One for production and one testing. So i have stored my connection properties in a configuration file, which looks different for prod and test.
I'm not allowed to connect to the oracle db with the user in who's schema the tables are located.
(e.g. schema=prod, my user=readonly)
Normally i must type SQL like: "select * from prod.MyTable" for my production datasource and "select * from test.MyTable" for my test datasource.
In order to clean up my sql i use the oracle "alter session" command. (alter session set current_schema=prod) The command is stored with the connection properties within my configuration file.
All of my packages start with this sql statement for the oracle datasource. (The connection property RetainSameConnection is true and the package property DelayValidation is set.)
So the solution is working fine.
But during the development i have a problem. When crating a datasource i can't retrieve the metadata infromation or build sql with the provided tools, because of "Table of View not found" (ok, the alter session statement is missing)
So does anyone know how to define a alter session for the design time conneciton?
Maybe something with the "Extended Properties" of the connection?
Thanks for your help!
Sven
View 1 Replies
View Related
Jun 13, 2007
I had to create an invoice report, where there is a stub on the buttom of the last page.
I need a report footer and RS does not have it.
I talked to Microsoft about it, here is the full emails between me and Microsoft.
hope this helps
Read from buttom to top
--------------------------------
Hi Ronnie
Sorry I was late in getting back to you as I was out of the office in the last couple of days
I made some checks and it seems the problem that you have is a genuine one.
The workarounds that you specified are the only solutions available now.
I checked with the development team and even though they are aware of the problem, they told me that there won€™t be changes to fix it in SQL 2008
So you have to decide on one of the workarounds that you specified
Best Regards,
Hi Tamer,
I wonder if you had any updates on our issue with RS2005.
Thanks,
Thanks for your help on this issue, I am going to more research on your solution (moving the footer to the body).
Meanwhile, I be looking forward for your sample as well.
Thanks again for all your efforts.
Ronnie Peretz
Hi neil
I think there is a way to implement your need in Reporting Services
Reporting Services has a free form designer which means that whatever you put on the report will appear in the same order in the final view. This is why we don€™t have a report footer.
Instead of putting the stub in the footer, just put it after the last item.
You can use a list dataregion to make sure that the stub will always appear at the bottom of the page in the expected location
Ronnie, I hope that solves your problem
Contact me directly if you need more info on how to implement this
Best Regards,
Tamer Farag
Here are all the facts I learned about Reporting Services "Report Footer" limitations and workaround I have tried.
Objective: On the last page of an invoice at the bottom of the last page have a stub of the invoice:
Issue: Reporting Services does not support Report Footer only Page Footer. (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1439673&SiteID=1)
Work around that I have tried:
Work around 1:
I have created a page footer with a rectangle and set the Visibility expression to =Globals.PageNumber < Globals.TotalPages
Results: The page footer did show up only in the last page, but created a block of space in the first pages.
I was trying to see if there is a way to suppress the spaces from the first pages, and found out it's not supported in Reporting Services
This behavior where the report body would consume the space of the empty page header & footer is currently not supported.
-- Robert (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=340650&SiteID=1)
Work around 2:
I tried to fit all the footer objects in the rectangle in a very small height range, hoping it will auto grow at run time. That did not work as well.
The textboxes did auto grow, but the Rectangle object or the Page Footer itself does not auto grow, cause to cut the footer information.
Work around 3:
Set the property on the page footer PrintOnFirstPage to False, and PrintOnLastPage = True, Reporting services does print the footer in pages in between.
works great for reports that have only 2 pages, but not not more then 2 pages
Best workaround:
Instead of having a space block at the bottom of each page, have a marketing text on every page except the last page
I had overlap a textbox on top of the rectangle (in the page footer) and having this expression on the Visibility =Globals.PageNumber = Globals.TotalPages.
For most of the invoices which are one page, that text will not show up and the Invoice stub will show up
For Invoices which are two pages or more, the marketing text will show up in the first pages only and in the last page the Invoice stub will show.
note: this workaround cause a warning saying "Overlapping report items are not supported in all renderers."
but it is supported for PDF.
View 1 Replies
View Related
Apr 25, 2008
Having a difficult time setting up a development environment and a set of standards for SSIS package development.
First of all, you can't run the dataflow object "SQL Server Destination" in BIDS because BULKCOPY can only be run from the actual server. So how do you test/debug a package with this object in it?
Second of all, if you create an SSIS package on a developer computer in BIDS, and then import it into the SSIS package store on your development SQL server, you can't run the package from Management Studio on the developer PC. You get the error "DTS_E_PRODUCTLEVELTOLOW" when it tries to run any of the SSIS. Do I have to have SSIS installed on the developer client machine? How do I do that without installing a full server instance on each client machine (not to mention the licsense issues)?
Lastly, what protection level would you suggest using for production? We are having issues with ODBC connection passwords being decrypted and thus package steps failing in using "EncryptSensitiveWithUserKey". What exactly does this protection level do? Our network is physically very locked down, so we arent worried about SSIS package security too much, just looking for a way for them to work reliably without having to setup complicated security scenarios.
thanks for your time.
View 4 Replies
View Related
Mar 2, 2008
1) if our .net app (oltp) is changed to ask sql for encryption (rather than thru .net) on server A, can server B where our wh resides, thru a linked server run an sp remotely on A and get A to provide decryption thru the keys defined there?
2)can the encryption keys etc defined on one server be imported to another server and still behave exactly the same way on the 2nd server as they do on the first? ie decrypt data originally encrypted by the first successfully.
View 4 Replies
View Related
Oct 20, 2006
Can someone verify that an assembly containing an interface with an event definition, such as...
public interface A {
event EventHandler Foo;
}
... can never be loaded under SAFE or EXTERNAL_ACCESS ?
It appears that the compiler-generated add_xxx and remove_xxx have the MethodImpl(MethodImplOptions.Synchronized) attribute defined by default, and "Explicit synchronization is not allowed". The same limitation also applies to classes by default, although technically one is able to define the implementation directly (clearly not ideal).
View 1 Replies
View Related
Oct 26, 2007
Hi Everyone,
I'm attempting to design a schema for a project I working on. The idea is that I can store a list of documents and associate meta data with them.
The problem is that I don't know what that meta data will be at design time. The user will create the meta data fields in the software. For the first design I just had a table called meta field that contained the meta field defintion, and another table called itemdata that contained the document primary key, the metafield primary key and a value encoded as a varchar. This works fine while the list of documents is small.
Another idea I've thought of is to have seperate tables for all of the metafields. This would allow me to be very specific about the values that can be stored in that column etc. It also turns out that for the kind of queries I'm doing its about 4 times as fast. The only problem is that the software will need to create these tables at run time.
What I would like to know is, is this a really bad idea? If so why?. Also are there any other ways I can store this kind of data?
The kind of queries I am doing are find all the documents where metafield 1 is A and metafield 2 is B and metafield 3 is C and metafield 4 is D etc.
Cheers,
Mark
View 4 Replies
View Related
Feb 1, 2007
Hi all,
I have run into a problem! Im developing a SSIS package programmatically using C#. But when i create and add a container (foreachloop and sequence) the container is not becommming visible in design time in my intergration services designer (when i open the .dtsx package afterwards). Does anyone have a solution to this problem? It is only a problem with containers i create myself (it is working when im adding e.g. dataflow tasks to existing containers).
Sincerely
Bryan
View 10 Replies
View Related
Mar 9, 2007
Hello, we have some tables and views that are not assigned to the standard schema of dbo.Whenever I try to use a design time control and/or SQLDataSource connection control, it does not like objects other than those using the dbo. schema.Any ideas on how to resolve this issue?For example, her are a couple of views:dbo.vwCustomersdbo.vwCustomerDetailsord.vwOrdersord.vwOrderDetailsthe views with dbo.* I have no problem with.the views with ord. it tells me the view(s) do not exist.Thanks in advance!
View 1 Replies
View Related
Feb 26, 2007
Frank writes "Any suggestion on the best design to store data for resource management so that data can be displayed afterwards in something like a gantt chart.
For example, you have to asign resources, say personID 1 to 100 to fill positions A, B, C, D, E and F
A position must always be filled. A person can only fill one position at a time. You want to be able to detect overlaps when you do your planning.
So if person 2 is in position B from 1 Jan to 15 March, he is currently unavailable, but he should be availabe in my planning for any position from 16 March onwards etc.
Possible queries -
1) list all positions not filled during period XXXX to YYYY
2) List all persons available to fill a certain postion during period XXXX to YYYY
3) List any overlaps where a person is assigned to different postions during overlapping time frames..."
View 1 Replies
View Related
Aug 3, 2015
With Sql Server Management Studio, while creating/modifying a table I want to specify one of its columns to store values in lower case only. Can it be done through the designer or by some other means?
N.B.: Using SQL Server 2005.
View 22 Replies
View Related
Oct 2, 2007
Hi, all experts,
Is there a way in SSIS dev environment that will let you convert/cast data type at desing time?
Under data flow mappings, there are so many things you can do, but why I did not find any easy way to convert?
I got this validation warning about not supportng converting type DT_STR to DT_I4, or something like that on many occassions..
I am using OLEDB Source as source and SQL Server Destination as destination.
Am I missing something obvious?
Thanks!
View 6 Replies
View Related
May 14, 2008
When I'm designing a merge join in BIDs, in particular a large one with say a couple hundred columns on the left part of the join (and only 1-2 columns on the right side), it takes 2-3 minutes to close and I notice it pegs the CPU on the computer during that time. I'm developing on a quad dual core 64 bit machine with 32 Gb of RAM.
What makes this problematic is if you have a several merge joins in the pipe, and you do something like add a column. Then you have to re-open every merge join to include that column, waiting 2-3 minutes for it to close. So adding a column to a pipe with 10 merger joins will take 20-30 minutes, most of which is just waiting for edit boxes to close. I've tried the options of "work offline" and setting the ValidateExternalData to false in the merge join property but those don't help. Anyone encounter this problem?
Thanks,
Lee
View 6 Replies
View Related
Sep 16, 2015
How to design at database level such a way so that when I implement a SQL query that returns one hundred thousand rows only display 25 rows at the client (Web page at a time). How can I accomplish this?
Once I display first 25 rows then how do I bring next 25 rows and so on. Can it be done via paging or there are other techniques. However I asked to design this in the database level. I am using MS SQL Server 2008. Front end Visual Studio 2010. I use C# for coding.Â
View 14 Replies
View Related
Sep 14, 2015
BEGIN TRAN;
INSERT INTO [dbo].[QuestManualProcess]
      ([ProcessFromDate]
      ,[LastProcessedFileDateStamp]
      ,[ProcessedOnDate]
     Â
[code]....
Conversion failed when converting date and/or time from character string/
View 4 Replies
View Related
Nov 26, 2007
hi
i have aques tion about mining model and time series after predicting
how can i show the result of the prediction in the c# form
and why i cant use the all of the prediction function in the mining
model prediction for time series and how i can see the script of the
building mining model in the model because i can see just the wizard
of the model and how can i undrestand othe about time series formula
in the chart
thanks alot if answer me
View 1 Replies
View Related
Apr 18, 2008
Hi,
I am having a Data flow task in For each loop which will gets 100 sourcetable names and 100 target table names...
am having a simpleData flow task which trasferes from OLEDBSource to OLEDBDestination.
I am repeating the Dataflow task which transfers from sourcetablename extracted from for loop to a destination table var.
The problem am gettting is for the first table it is able to transfer correcly because I did mapping for those tables at design time...but for the next coming sourcetable-desttable (which r having different no of cols,datatypes) its giving Validation failed...and...needs to refresh metadata....
is there any way to refresh the metadata of Data flow task (I set the property of OLEDBSource validate external meta to false then also same error is coming)
Thanks
Radhika
View 4 Replies
View Related
Oct 11, 2006
Hi There,This is related to a ms access database but since I use the SqlDataSource control I thought I should post here.I have a project that I was working on with this ms access db and using sql controls, everything was working just finesince one day I started getting "Object reference not set to an instance of an object" messages when I try to designa query or retrieve a schema, nothing works at design time anymore but at runtime everything is perfect, its a lotof work for me now to create columns,schemas and everything manually, I've tried reinstalling visualstudio, ado componentsbut nothing seems to fix it, did this ever happen to any of you guys?any tip is really appreciated thanks a lot
View 2 Replies
View Related
Jul 20, 2015
Working on a new database where the Date and Time are stored in a Date Time Field.
Then working on an OLDER database file within the same SQL Database contains these 2 items as integers:
transDate = "71615" (July 16, 2015)
transTime = "12345" (01:23:45 AM)
How do we convert both of them into a single SQL DateTime field such as "2015-07-16 01:23:45.000" so that it can be used in a join restricting to a date time in a different SQL File that properly has the DateTime in it?
This works well for converting the transDate Part in the select statement:
  dbo.IntegerToDate(at.transDate) as transDate
  * That returns: "2015-07-16 00:00:00.000"
* The resulting data must work directly in a Microsoft SQL Server Management Studio Query using either using the "on" statement or part of the "where" clause. In other words, NOT as a stored procedure!
Also must be able to be used as a date difference calculation when comparing the 2 files Within say + or - 5 seconds.
View 3 Replies
View Related
Jun 24, 2007
I am new to this type of programming and and have read all articles on adding an image to the database and it seems they all use sql queries to add an image but I want to add an image at design time. I am using Visual Basic 2005. I am also using Visual Basic 2005 Express Edition to try the same thing. I am trying to build a Translator program for english to Brazilian Portuguese and the reason I want to add the images is so that when I translate the word cat from english to Portuguese, I can also show an image of a cat. Can anyone please help me
View 3 Replies
View Related
