Indirect References
Oct 11, 2007
Normally, you establish referential integrity so that foreign key in one table points to a primary in another. Here is a composite key:
(A_ref, B_ref) => (A, B)
Consider a situation where A is a primary key in table T1. It is refered by T2, which has A,B as it's prmary key. An example of this situation would be a table of printers and table of batches a printer has printed at specific date. The batches are identified by Printer, Date, BatchNo within the date. Now, we create a temporary table T3, which addresses "today" batches. The "today" reference is taken from a date is taken from some record in the DB and, combined with the Printer ID and BatchNo, must point to a record in T2. Is it possible to specify such a complex relationship to ensure the referential integrity?
The advantages of the integirty are:
-- the referred records are pinned down from removal
-- it is not possible to refer unexisting object; thus, the referee is ensured to refer an existing one.
Thanks.
View 6 Replies
ADVERTISEMENT
Jan 18, 2007
Hi,
I have a package that uses a Configuration of type SQL Server where the property values are held.
This runs successfully using this direct configurations.
When I use an Indirect configuration using an environment variable to point to this SQL Server configuration type the package won't even validate.
The Indirect Configuration is:
[EHC-SQLD-01.].[SSISConfigsDEV];[dbo].[SSIS Configurations DEV];pkgLRD CED Import;
which follows the standard of : db connections, config table, filter
This works by the way on my client but on the dev server the error is:
Error: The connection "[EHC-SQLD-01].SSISConfigsDEV" is not found. This error is thrown by Connections collection when the specific connection element is not found.
I've tried every combination for the env variable using quotes, full computer name etc (I thought it was the hyphens in the name), but can't seem to get it to work.
I've also tried as both user and system env variables but made no difference (which one should we use for SSIS anyway as BOL doesn't state this ?)
Appreciate any help. I'm trying to deploy this on the Dev server for testing.
Thanks
P R W.
View 13 Replies
View Related
Oct 7, 2014
I got the following problem to solve in TSQL. I don't want to use a cursor. But with the set based solution I am stuck.
Here is my problem:
DECLARE @tmp TABLE (CustomerID INT, CustomerLink INT, PRIMARY KEY(CustomerID))
INSERT @tmp
VALUES(100001,0)
,(100002,100001)
,(100003,100001)
,(100004,100003)
,(100005,100006)
[Code] ....
Desired result:
[CustomerID of a group member],[smallest CustomerID per Group]
|(GroupID)|CustomerID|CustomerLink|
|100001 |100001 |100001 |
|100001 |100002 |100001 |
|100001 |100003 |100001 |
|100001 |100004 |100001 |
|100005 |100005 |100005 |
[Code] .....
doesn't work for crossreferences :.(
RESULTSET:
CustomerID CustomerLink
100001 100001
100002 100001
100003 100001
100004 100001
100005 100005
100006 100006 --wrong
[Code] .....
View 9 Replies
View Related
Mar 8, 2007
I am trying to get a FK that is derived based on 2 values one of which is a date. But the problem is the corresponding date value is a between meaning the date is between 2 dates.
So translating to SQL it would be:
select * from table 1 join table 2
where table1.key1 = table2.key1 and table1.date between table2.effective_date and table2.expired_date
So is this possible within the SSIS data flow controls?
Thanks.
View 1 Replies
View Related
Jan 8, 2008
I have a series of packages in a project, all have 3-4 configuration files located on my c: drive. I need them to be in a different path on the server. I added another configuration type, but instead of specifying xml file, sql server, etc, I chose the radio button at the bottom that says 'Configuration location is stored in an environment variable'. I created a system variable on my machine, typed it into that box, then saved the configuration. I put the path to my dtsconfig files in that variable. I created the same system variable on the server, called it the same thing, and put the path to my dtsconfig files on the server in that one, but I must be missing something. The packages don't appear to find the config files on the server.
The package configuration organizer has 3 entries of type 'XML Configuration File', followed by one of type 'Indirect XML Configuration File'.
Is that the intent of this - to set a system variable that directs the packages to the appropriate directory where the config files are located? Is there something else I need to do? Does SSIS just 'know' that if there is an indirect config file to use that when looking for dtsconfig files?
Thanks
View 7 Replies
View Related
Jul 21, 2006
Hi,
I need to configure my package using SSIS Configurations. When I opened SSIS Configuration wizard, press ADD and selected Xml Configuration, I found two options.
One being : Specify configurations directly.
next : config location is stored in env variable.
How to use the second option?[ env variable]. Say I want to configure my connection string for my destination database [ OLEDB Destination Component]. How to do that using second option.
Thanks
Kishan
View 9 Replies
View Related
May 23, 2008
Hello,
I have some basic understanding of SSIS and now I am looking to take the next step. Especially since I created all my packages with EncryptWithUserKey (I didn't know better) . My assumption is that I place into each of my packages an identically named Environment Variable. Then if I move my configuration files I just need to reassign the value to that variable. Do I have that correct?
For example, I have an XML Configuration file sitting in c:PackConfigs. It is called MyConfig.dtsConfig. How do I create an Environment Variable to point to this configuration file? I assume I create the variable and assign it the value the path of the file (c:PackConfigsMyConfig.dtsConfig). I sort of get that.
But what if I have multiple XML configuration files in my package? And exactly how do I change the variable's value if I move my packages?
I've read some great posts in this forum and at Jamie Thomson's blog http://blogs.conchango.com/jamiethomson/archive/2005/11/02/SSIS_3A00_-Indirect-configurations-ROCK_2100_.aspx
but I haven't been able to get to the nitty-gritty that I think I need.
Thank you for the help.
-Gumbatman
View 20 Replies
View Related
May 11, 2006
Hi Jamie,
You wrote In your blog which I pasted below:
"I have built my packages in such a way that different packages can use the same configuration file. For example I have a "Master" configuration file that stores information that all of my packages will need (e.g. connection strings for my log files, warehouse database and metadata database). Our project makes use of many source systems so I also have a configuration file for each source system as well - that way every package that accesses a certain source system can use the appropriate configuration file. .."
How do you create a configuration for each source? I thought the concept of environmental variable is to have one configuration file in a location and defined the path to it in your system environmental variable value?
I have only one config file file for all my 9 packages and I have been having problems.
Maybe I am doing something wrong but what? Can you be more specific about this great idirect configuration?
Thanks
Omon
View 3 Replies
View Related
Sep 17, 2007
I'm having a problem with indirect configurations on my client's machine.
I've created a package that uses an xml configuration file (.dtsconfig file) that is pointed to by an environment variable. I have this package running on my machine using Windows XP and on my test machine that uses Windows Server 2003. When I set it up on my client's test machine also running Windows Server 2003 the package doesn't recognize the configuration and tries to use the values that it was originally programmed with on my development machine.
I installed the packages the same on both test servers including adding the appropriate environment variables. Is it possible there is something wrong with the environment? I haven't checked the various service pack levels on the two machines. I know that I keep my test machine up to date. I'm not sure about my client. Are there any known issues like this under specific service packs? Or am I just probably missing something glaringly obvious?
Thanks,
Jeff
View 3 Replies
View Related
Apr 16, 2008
I have a set of packages that use an Indirect Configuration to a XML config that gives each package a connection string to the Configuration database. The configuration database has all the connection strings for every connection I use.
I just moved my dbs to a new server. I updated the XML config file to point to the new server and updated the connections in the configuration table. I am having two problems. When I open packages in BIDS, I am receiving errors because the connection strings embedded in the packages were pointing to the old server. I updated the Data Sources in the solution and that didn't fix it. Why is it not using the configurations?
Also, on the production box (64bit), I am having the same problem. It is not reading in the connections from the new SQL configuration table. All my connections that are producing errors are OLEDB for SQL.
Any ideas?
Thanks,
Abe
View 1 Replies
View Related
Nov 18, 2007
Is it possible to create an indirect Package Configuration by using the /CONF switch when running a package via dtexec.
Here is what I would like to do:
1. Use an Indirect SQL Server configuration type
2. Instead of storing the connection string in an env. variable, I want to specify it at run time using the /conf switch
In other words, are there any alternatives to storing the configration connection string on the server?
View 13 Replies
View Related
May 25, 2007
Greetings All;
I am having trouble getting an indirect configuration to work. Can someone offer a hand?
Here's the deal....
I have a package the currently has an OLE DB Connection manager. I have an XML configuration file set up to acquire the connection string. That works fine. I have a package variable that contains the absolute path to the .dtsconfig file.
How can I get the connection manager to read the package variable (which has the path to the *.dtsconfig file) instead of the hard coded value?
Thanks in advance.
View 7 Replies
View Related
Mar 22, 2006
Hi all
I am trying to create indirect package configurations so that:
1) My configuration data is stored in a SQL Server table, and
2) The location of that table is stored in an environment variable.
Achieving #1 is easy, but when I hit the "Configuration location is stored in an environment variable", I strike problems. I can specify a environment variable no problems, but there doesn't seem to be anywhere in the documentation to describe the syntax of WHAT should go in that environment variable.
HELP!!!!
Thanks in advance
Jays
View 3 Replies
View Related
Sep 29, 2006
If your XML configuration files will be in the same location on your Development, UAT and PROD servers, is there any merit to making your configurations indirect?
I am modifying the connection string with the XML. My strategy is to set up an XML configuration for each database that we have. The Dev XML config will point to Development connection, UAT to UAT etc..
My thought is that by using the direct configuration it will eliminate the need for environment variables and also allow me to add configs without having to reboot the servers, which you would need to do in order to get server to recongize the EV.
Thanks
View 5 Replies
View Related
Aug 31, 2006
Whats the definitoin. I've been digging for a while, but cannot locate.
TIA, cfr
View 4 Replies
View Related
May 4, 2007
Hi All -
I have come across the need to use a cube in AS 2005 for an application. Unfortunately this will be the first time writting an ASP.Net application which uses one. I was wondering if any of you had any good books or references that I could look at to get an idea of how I should approach this issue.
Thanks in advance.
View 2 Replies
View Related
Oct 22, 2007
Hi,
I'm doing a web application that will get some information from an ERP.
At this moment I have 2 databases:
1) The aspnetdb, where I have the tables for Merbership and Role
2) The ERP database
I need to put my web application tables on one of these two DB's. This tables will reference the users from the membership and some products from the ERP DB.
I will store products requests that will store both UserID (from aspnetdb) and ProductID (from ERP DB). I'm thinking to put these tables on the aspnetdb, so that all web application tables stick together. But, I will loose tha ability to make joins with the ERP database, right?
Do you think this will work? Can someone make some comments about this situation, and give me some tips?
Thank you!
View 6 Replies
View Related
Feb 18, 2004
I am currently developing a stored procedure that includes a number of computed fields. Is it possible to reference a computed value, (eg. FLdA), or do I need to CREATE a temp file and then reference the FldA and FldB values. I have simplified my code, it is much more extensive in that there are numerous WHEN clauses attached to each FldA and FldB computation.
SELECT FldA = CASE
WHEN .... THEN CurQty * 1.5
WHEN .... THEN CurQty * 1.75 ELSE 0 END),
FldB = CASE .....
NewValue = CASE
WHEN .... THEN FldA * CurValue
WHEN .... THEN FldB * CurValue
etc.
View 9 Replies
View Related
Jul 20, 2005
HelloSuppose a database Db1 with tables tl1 and tl2 and a second database db2with tables tl3 et tl4.Is it possible to make a join between tables of the two databases ?As for example, Select * from tl1 INNER JOIN tl3 where tl1.Field1 =tl3.Field3Thank for any helpThierry
View 1 Replies
View Related
Jul 20, 2005
I have a task to where I need to move a column from one table toanother. I want to be sure I update any view, stored procedure,trigger, etc. that references the column. I simply want a query thatwill report the related objects and then I will update them manuallybut before I go and try and figure out how to do this by querying thesys tables is there an sp_sproc that will do this?
View 1 Replies
View Related
Jul 10, 2007
I have a data list that will grow over time. The values are listed vertically in a column; most recent value at the bottom. I am trying to figure out how to setup a formula to figure out the standard deviation on the most recent 30 values automatically. For instance if the column contains 30 values and I add the 31st value, I'd like to have to have the standard deviation displayed in a cell and automatically shift from calculating on values 1-30 to values 2-31. Is this possible?
Jeff.
View 1 Replies
View Related
Feb 13, 2008
I've been through a number of tutorials on how to enable row-level security based on a userID, but my problem is more complicated and I do not have sufficient understanding of report models to guess.
My security information is defined in a table within my database. It contains a username and an account mask. An account mask maps to 1 or more account codes contained in the other data tables in my report model. A user may have more than one account mask defined for his account.
I understand the concept of directly mapping the logged-in user to a field containing a matching username. Is it possible to do a two-step mapping, so that based on the user ID I can get the account mask(s) and then evaluate which account codes match the mask(s)?
Or is there a different/better way to set this up? Defining SQL roles/groups is not an option, because of some compatibility issues with external systems.
So far, because my security table has no defined relationship with the data tables, I have not even been able to get it into my report model (Would love any suggestions on that one, too.)
I'd appreciate any ideas or suggestions - even if only something to investigate.
Thanks,
Sarah
View 1 Replies
View Related
Apr 27, 1999
We installed SQL7 over the weekend. Everything was working peachy through yesterday. This morning SQL server is requiring all of the queries to require references to the username in the from clause...
For example
"Select * from mytable" used to work fine. Not it requires "select * from username.mytable"
I'm logged in as the same username, which is the DB Owner as well. Any idea why this is happening all the sudden?
TIA
Kevin
View 1 Replies
View Related
May 15, 2008
Hey.
I'm trying to create some tables in my database but I'm getting some errors... The one which is causing the most trouble is Msg 1767, Level 16, State 0, Line 38
Foreign key 'ten_fk' references invalid table 'Tenant'.
I'm not sure why it's complaining... can anyone help me out here?
Cheers!
-- Mitch Curtis
-- A2create.sql
-- Set the active database to KWEA.
USE KWEA;
-- Drop existing tables (if any).
DROP TABLE Ownership;
DROP TABLE Tenant;
DROP TABLE Staff;
DROP TABLE Property;
DROP TABLE Property_Status_Report;
DROP TABLE Property_Owner;
DROP TABLE Placement_Record;
DROP TABLE Candidate_Tenant;
DROP TABLE Waiting_List;
-- Create new tables.
CREATE TABLE Waiting_List
(
waiting# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
candidate_name VARCHAR(20) NOT NULL,
anticipated_start_date SMALLDATETIME NULL,
anticipated_end_date SMALLDATETIME NULL,
max_affordable_rent SMALLMONEY NOT NULL
);
CREATE TABLE Candidate_Tenant
(
candidate# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
waiting# INT NULL,
name VARCHAR(20) NOT NULL,
phone_number INT NOT NULL,
required_property_type VARCHAR(10) NOT NULL,
CONSTRAINT w_fk FOREIGN KEY(waiting#) REFERENCES Waiting_List(waiting#)
);
CREATE TABLE Placement_Record
(
opening# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
tenant# INT NOT NULL,
start_date SMALLDATETIME NOT NULL,
end_date SMALLDATETIME NOT NULL,
total_bonds SMALLMONEY NOT NULL,
weekly_rent SMALLMONEY NOT NULL,
CONSTRAINT ten_fk FOREIGN KEY(tenant#) REFERENCES Tenant(tenant#)
);
CREATE TABLE Property_Owner
(
owner# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
name VARCHAR(20) NOT NULL,
phone_number INT NOT NULL
);
CREATE TABLE Property_Status_Report
(
address VARCHAR(30) NOT NULL,
report_date SMALLDATETIME NOT NULL,
weekly_rent SMALLMONEY NOT NULL,
month_rent_start_date SMALLDATETIME NOT NULL,
month_rent_end_date SMALLDATETIME NOT NULL,
maintenance_fee SMALLMONEY NOT NULL,
month_inspection_history VARCHAR(30) NULL,
CONSTRAINT ar_pk PRIMARY KEY(address, report_date),
FOREIGN KEY(address) REFERENCES Property(address)
);
CREATE TABLE Property
(
address VARCHAR(30) PRIMARY KEY NOT NULL,
staff# INT IDENTITY(1,1) NOT NULL,
type VARCHAR NOT NULL,
occupant_limit INT NOT NULL,
comments VARCHAR(30) NULL,
FOREIGN KEY(staff#) REFERENCES Staff(staff#)
);
CREATE TABLE Staff
(
staff# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
manager# INT NOT NULL,
name VARCHAR(20) NOT NULL,
FOREIGN KEY(manager#) REFERENCES Staff(staff#)
);
CREATE TABLE Tenant
(
tenant# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
staff# INT NOT NULL,
property_address VARCHAR(30) NOT NULL,
name VARCHAR(20) NOT NULL,
phone_number INT NOT NULL,
street VARCHAR(20) NOT NULL,
city VARCHAR(20) NOT NULL,
postcode INT NOT NULL,
category VARCHAR(10) NOT NULL,
comments VARCHAR(30) NULL,
FOREIGN KEY(staff#) REFERENCES Staff(staff#),
FOREIGN KEY(property_address) REFERENCES Property(address)
);
CREATE TABLE Ownership
(
address VARCHAR(30) NOT NULL,
owner# INT NOT NULL,
CONSTRAINT ao_pk PRIMARY KEY(address, owner#),
FOREIGN KEY(address) REFERENCES Property(address),
FOREIGN KEY(owner#) REFERENCES Property_Owner(owner#)
);
-- Display tables.
SELECT * FROM Waiting_List;
SELECT * FROM Candidate_Tenant;
SELECT * FROM Placement_Record;
SELECT * FROM Property_Owner;
SELECT * FROM Property_Status_Report;
SELECT * FROM Property;
SELECT * FROM Staff;
SELECT * FROM Tenant;
SELECT * FROM Ownership;
Errors:
Msg 3701, Level 11, State 5, Line 8
Cannot drop the table 'Ownership', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 9
Cannot drop the table 'Tenant', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 10
Cannot drop the table 'Staff', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 11
Cannot drop the table 'Property', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 12
Cannot drop the table 'Property_Status_Report', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 13
Cannot drop the table 'Property_Owner', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 14
Cannot drop the table 'Placement_Record', because it does not exist or you do not have permission.
Msg 1767, Level 16, State 0, Line 38
Foreign key 'ten_fk' references invalid table 'Tenant'.
Msg 1750, Level 16, State 0, Line 38
Could not create constraint. See previous errors.
View 4 Replies
View Related
Sep 24, 2006
How do you add a reference if the assembly you want to reference does not live in C:WindowsMicrosoft.NETFrameworkv2.0.50727? It doesn't seem to make sense that you cannot access the connections the AquireConnection() method serves since the interfaces are defined in Microsoft.SQLServer.DTSRuntimeWrap.dll (in C:Program FilesMicrosoft SQL Server90SDKAssemblies).
For example the code below works fine, in order to make it work I had to copy DTSRuntimeWrap.dll to v2.0.50727, surely any assembly which is been added to the GAC should be available as a referece?
Public Sub Main()
Dim cmgr As Microsoft.SqlServer.Dts.Runtime.ConnectionManager = Dts.Connections("FTP Connection Manager")
Dim cn As Object = cmgr.AcquireConnection(Nothing)
Dim ftpConnection As IDTSFtpClientConnection90
ftpConnection = TryCast(cn, IDTSFtpClientConnection90)
ftpConnection.Connect()
Dim folders As String()
Dim files As String()
ftpConnection.GetListing(folders, files)
ftpConnection.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
View 4 Replies
View Related
May 30, 2006
Hi everyone,
When you've got -for example- a derived column task linked with a Flat file and then you change any field and come back to derived column task again you have select that field
with two possibilities:
1-€˜Leave as invalid column reference€™ /
2-€˜Delete invalid column reference€™
It€™s easy when just one is affected but when you have eight or ten is very tedious do the same one by one. Any way for to do same but selecting more than one?
Thanks for any input,
View 3 Replies
View Related
Mar 19, 2008
Hi All,
I'm pretty new with SSIS. I have written some custom component which will read from flat file and write it into database. Between those processes, there are other processes which will analyse and transform the data being transfered. Everytime, I remove a column from upstream component, I have to go through each component to fix up column reference mapping using Invalid Column References Editor. There are lots of clicking involves just to remove a column from upstream component. My package has about 15 components. I am just wondering, is there anyway for us to prevent that editor to pop up, instead fixing invalid column reference programatically in ReInitializeMetadata?
Thanks,
Will
View 7 Replies
View Related
Jan 28, 2007
Hi all,
Would anyone know of any references (online or books) that make it easier for experienced Oracle people to Learn SQL Server. The type of things I'd like to know for example are
The environment for SQL server on a PC (e.g. where the
datafiles are, what's the replacement for tnslistener - general architecture info)
Any significant differences that I'd need to know for creating tables and applications, like what do I use instead of varchar2 for example.
that should get me started.
tia,
Dave
View 1 Replies
View Related
Sep 8, 2015
I need to join 2 tables but the join needs to account for 2 seperate columns.
for example:
select
a. type
a. prod_code
a. prod_type
b. division
from table1 a
left join table2 b
on a. prod_code = b. prod_code
and a. prod_type = b. prod_type
The issue is that you may have only the prod_code or prod_type and null value for the other in table1.
Ideally I want it to check for both then if 1 isn't available then it draws the division of the available. having both or one or the other determines the division it falls under.
View 5 Replies
View Related
Oct 12, 2015
I have a database of 900+ tables with around 3000 SPs, and views. Manually I reviewed few tables and found that tables are not referenced with FK and I applied few. There are lots of tables and SPs using them in join statement, Is there any way with which I can get each tables missing references, any DMV or other manual script which tells about this?
View 2 Replies
View Related
Sep 11, 2007
Does anyone know of any good references (books or web sites) that provide examples of querying AD from SQL Server? I have the database link setup and have done two very simple queries against AD but I would like to see more in-depth examples.
Thanks.
View 1 Replies
View Related
Oct 4, 2007
Hello and thank you for the help in advance.
I know this has to be possible maybe I am just missing somthing.
I am creating a matrix report which will compare year by year quotes to orders The issue is quotes and orders each have their own dataset. I will be pivoting on JobType which is in both datasets and spelled the same. Is there a way to do this or will I have to figure out how to union the tables? If not possible why does it allow you to name the dataset in the expression?
Thanks, Leo
View 1 Replies
View Related
