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.
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.
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.
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
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
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
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
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
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
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?
Hi,
View 9 Replies View RelatedGreetings 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.
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
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
We did some "at scale" fuzzy lookup tests today and were rather disappointed with the performance. I'm wanting to know your experience so I can set my performance expectations appropriately.
We were doing a fuzzy lookup against a lookup table with 25 million rows. Each row has 11 columns used in the fuzzy lookup, each between 10-100 chars. We set CopyReferenceTable=0 and MatchIndexOptions=GenerateAndPersistNewIndex and WarmCaches=true. It took about 60 minutes to build that index table, during which, dtexec got up to 4.5GB memory usage. (Is there a way to tell what % of the index table got cached in memory? Memory kept rising as each "Finished building X% of fuzzy index" progress event scrolled by all the way up to 100% progress when it peaked at 4.5GB.) The MaxMemoryUsage setting we left blank so it would use as much as possible on this 64-bit box with 16GB of memory (but only about 4GB was available for SSIS).
After it got done building the index table, it started flowing data through the pipeline. We saw the first buffer of ~9,000 rows get passed from the source to the fuzzy lookup transform. Six hours later it had not finished doing the fuzzy lookup on that first buffer!!! Running profiler showed us it was firing off lots of singelton SQL queries doing lookups as expected. So it was making progress, just very, very slowly.
We had set MinSimilarity=0.45 and Exhaustive=False. Those seemed to be reasonable settings for smaller datasets.
Does that performance seem inline with expectations? Any thoughts to improve performance?
I'm working with an existing package that uses the fuzzy lookup transform. The package is currently working; however, I need to add some columns to the lookup columns from the reference table that is being used.
It seems that I am hitting a memory threshold of some sort, as when I add 3 or 4 columns, the package works, but when I add 5 columns, the fuzzy lookup transform fails pre-execute:
Pre-Execute
Taking a snapshot of the reference table
Taking a snapshot of the reference table
Building Fuzzy Match Index
component "Fuzzy Lookup Existing Member" (8351) failed the pre-execute phase and returned error code 0x8007007A.
These errors occur regardless of what columns I am attempting to add to the lookup list.
I have tried setting the MaxMemoryUsage custom property of the transform to 0, and to explicit values that should be much more than enough to hold the fuzzy match index (the reference table is only about 3000 rows, and the entire table is stored in less than 2MB of disk space.
Any ideas on what else could be causing this?
Say I want to lookup a value in another dataset, but there is a grouping that requires you to know what the values for each level is in order to get to the correct detail record. Can you still use the lookup function with more than one field to compare against? So for example
Department
\___SalesPerson
\___Measure
I want to be able to add a new row at the Measure level, but lookup each field from another dataset. In order to do that I will need the Department AND SalesPerson values to do the lookup, but I dont think the Lookup function will let us do that will.
Hi All,
Actually this is in regard to SCD Type 2 Dimension, Scenario is like that I am moving Fact table from some old source and I have dimensionA description value in fact which I want to replace with appropriate id from Dimension Table and that Dimension table is SCD Type 2 based on StartDate and EndDate and Fact Table doesn't contains direct date value rather there is timeId in Fact so to update the value in Fact table I have to Join Time Dimension table and other Dimension Table to replace fact Description with proper Id.
Lets assume DimensionA Structure
id
Description
StartDate
EndDate
Fact Table
id
measure1
measure2
TimeId
Description
Time Dimension
TimeId
Date
Day
Hour ...
I am doing a lookup that requires mapping 2 columns in the column mapping section. When I do this, I get the error "Row yielded no match during lookup" . The SQL that I captured in SQL profiler does find the record when I run it in Management Studio. I have already tried trimming everything to no avail.
Why is this happening?
I tried enabling memory restrictions but then I my package hangs and I get a SQLDUMPER_ERRORLOG.log file with the following logged:
07/24/07 13:35:48, ERROR , SQLDUMPER_UNKNOWN_APP.EXE, AdjustTokenPrivileges () failed (00000514)
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters: 4 supplied
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID = 5952
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags = 0x0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr = 0x0100C5D0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExceptionRecordPtr = 0x00000000
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr = 0x00000000
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 15 not used
07/24/07 13:35:49, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7 not used
07/24/07 13:35:49, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump completed: C:Program FilesMicrosoft SQL Server90SharedErrorDumpsSQLDmpr0033.mdmp
07/24/07 13:35:49, ACTION, DtsDebugHost.exe, Watson Invoke: No
Why am I getting this error with "Enable Memory Restriction"?
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
Hi all,
I don't understand what's happening here.
I have a Conditional Split with 3 outputs. On the first output I have a lookup, when I execute the package I have 56 rows going through the Conditional Split, all rows are then going to the 2nd and 3rd output but the lookup on the first output generates an error "Row yielded no match during lookup".
I don't understand why the lookup is generating an error while there is no row going through it.
Any idea ?
Sébastien.
I am designing a ssis package,This is intends to mine text data(Data extracted from websites).
Term lookup/Term extraction has been used as tools for mining.
I have lookup terms defined with me for reference table,but the main problem lie in extracting the nearby text/number/charcters to these lookup terms during mining.
For example :
I found noun "Email" 200 (frequency score) times in my text,Now I want to extract nearby email address(this is also true for PhoneNumber,Address attributes also).so how can I achieve this with SSIS.
If u have some idea/suggestion to carry out this challenge with or without Term Extraction/Term Lookup,plz do write here.
Deployed Report having SSIS package as source do not work when Indirect Package configuration is used in ETL package. It seems ETL package when called/executed from Report manager does not recognize environment variable to pick up the dtsconfig file.
The Report works when Direct package configuration is used to same dtsconfig file.
What could be the reason? Any solution for this? This will cause our build/deployment to QA and Prod very difficult.
Is there a good step by step guide to setting up an indirect configuration? I've followed the steps in Kirk Haselden's 'Keep your packages in the dark' article 3 or 4 times this afternoon and nothing seems to work. I cannot even get the XML configuration file to work by accessing it directly. I'm using the wizard to create the file.
Just wondering if there was something else out there....
Thanks in advance for any help.
MarkA
I want to lookup values from a database into another database both of which are in the same sql server 2000. One databases is called GamingCommissiondb the other is called LicensingActions I need some of the tables to communicate with each other, to look values from one another. Example I need the Termination table to look up values from the Revocations table. Would using LinkedServers suffice??
View 5 Replies View RelatedHi,
Can anybody provide me a Lookup UDF? I need to supply columnname,Tablename and condition dynamically and I need the scalar value in return.
Any help will be greatly appreciated...
Hi,
I am transferring the xml data from an xml file into sql server table using ssis.
To avoid any duplicate import via this ssis package, I would like to first check if data exists in the sql server table for what is about to be imported. If so then delete the existing data and then import.
Question:
How do I get the field value say ID field from the file and then take this id and delete these from the table in sql server first.
Is this to do with lookup or is there an easier way to do this please?
thanks
Hi
I am trying to use lookup to see if a item esists in my table ( 3 key fields ). If the lookup fails I want to insert the records. If it succeeds I have put a recordcount to catch the items that are not required. I don't think that I understand the settings for failed rows. I have tried setting the Configure Error Output to redirect, but this does not seem to work. I have the below errors.
[SQL Server Destination [151]] Error: Unable to prepare the SSIS bulk insert for data insertion.
[DTS.Pipeline] Error: component "SQL Server Destination" (151) failed the pre-execute phase and returned error code 0xC0202071.
Can someone please advise me how to set up this component to work for my application
Thanks
ADG
Hi,
I am using SCD and Lookup for Incremantal ETL load. But it is very interesting that
my 3000 recs is becoming 2500 when I put SCD and lookup. if I replace dummy multicast then
it is 3000.
thanks
Hi,
Before inserting records of a recordset I would like to check that each one of these records doesn't exist in the destination table 1. If the records exist, they should be redirect to other table 2.
For this purpose I use a lookup task. The referende table is the destination table 1. The green line is linked to table 2 and the red line is linked to table 1.
The first execution works properly and copies de records to table 1, but on the next executions the records are copied to table 1 instead of being copied to table 2.
Any idea??
Thanks.
say i have a customer.aspx that allows a user to enter in customer data.
on customer.aspx, i have dropdownSalesRep which allows the user to associate a sales rep with the customerbut some customers come to directly, and not thru a sales rep, so I want the user to be able to specify "none"
Is it best to have a dummy record in my SalesReps table called "none" with an ID of say "999", or is there some other better way to deal with this?
Hi Guys,
I really need to know if it is possible to look in a Transaction Log for a specific transaction, it is for audit purposes. In the affirmative case, could you teach me how to do it or at least point me in the right direction.
I'll really appreciate your help ;-)
Regards.
I have a DTS package that calls a LOOKUP objects in an activeX script,
I did not create this package and I am trying to figure out what it does,
How do I view the actual LOOKUP object? I know what the LOOKUP object name is but cannot seem to find where it is actually stored?