ETL From Multiple Access Databases -SSIS?
Jun 13, 2006
Howdy Folks,
I have an ETL project that I want to ask a question about. I want to loop through a folder containing several Access databases, and extract and load each one into a single SQL Server 2005 database. The table layout is the same for all the Access dbs, as well as the SQL db. The number of Access dbs in the folder, their records, and their names will change. Is there a way i can use the SSIS Foreach tool to move through the folder and set the connection string inside the loop?
Thanks for your reply,
Chris
View 6 Replies
ADVERTISEMENT
Jul 30, 1998
I try to fill a recordset with a query in an asp page to an SQL Server through ODBC connection :
The ODBC connection (eg : DB1Cnnection) is done to one Database (eg : DB1).
and I need to link to another Database (eg : DB2)
it `s like :
set rs=DB1Connection.Execute("Select DB1.xxx FROM TABLE1, TABLE2 WHERE ...")
Please, How do I `tell` to ODBC that TABLE2 is in DB2 ???
HELP !
View 2 Replies
View Related
Jan 21, 2008
I’ve been asked to migrate many Access databases to SQL Server. There are no reports, queries or forms in these databases. There are two different database structures. Each has many copies with different data. For example, 80 people each have a copy of abc.mdb. Each copy has the same tables, but different data. The databases cannot be merged without modifying the structure of the databases and the programs that access them. There are over 100 databases ranging in size from 2 MB to 60 MB.
What’s the best approach to do this? I don’t want to manually run each through the upsize wizard.
Should I use a local SQL Server Express or a central SQL Server?
Will there be any benefits to doing this? Will the performance improve?
Personally I’d rather redesign the databases, merge the data and modify the applications that use the data. But it’s not my decision.
View 2 Replies
View Related
Dec 10, 2007
Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
View 3 Replies
View Related
Jul 24, 2015
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',
@profile_name =
'SQL SMTP',
[code]....
View 3 Replies
View Related
Sep 13, 2007
Hi,
I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.
I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.
I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!
Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):
SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLOACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a
I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC
Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)
Thanks
View 8 Replies
View Related
Nov 15, 2006
Hi!
I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.
I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.
I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)
Any pointer most welcome!
best regards and thanks
Thibaut
View 1 Replies
View Related
Aug 14, 2012
I am trying to restore multiple .bak backup SQL database files onto a new server. However, I have found that it will not allow me to restore multiple databases at once. Is there a way to do this so that I do not have to manually upload one at a time? I tried adding all the .bak files at once to the backup device window but it only did the first one listed. It would be so much easier to restore them all at once so that I do not have to continue this manual process. I am restoring them via device.
View 13 Replies
View Related
May 23, 2008
Hello,
I am in the progress of designing a new section of my database and was thinking of creating a hole new database instead of just creating tables inside the database. My question is can you JOIN multiple tables in an SQL Statement from multiple databases. Ie, In the Management program I have a database called 'Convention' and another one called 'Services', inside the two databases there are many tables. Can I link say tblRegister from Convention to tblUser in Services?
Thanks
View 3 Replies
View Related
Jul 8, 2004
Is it possible to retrieve Data from SQL Database #1 and insert it into SQL Database #2 using a Stored Procedure? Thanks. If so Can you show example. Thanks
View 12 Replies
View Related
May 18, 2007
Hi All
I have a question about having one or multiple databases for a large project. What are the main differences between designing my database as one or breaking in to multiple databases. I should mention that there are many relationships between tables in different DBs in the multiple DBs state?
regards
sasan
View 5 Replies
View Related
Aug 10, 2015
Here is my requirement, How to handle using SSIS.
My flatfile will have multiple columns like :
ID key1 key2 key3 key 4
I have SP which accept 3 parameters ID, Key, Date
NOTE: Key is the coulm name from the Excel. So my sp call look like
sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date
View 7 Replies
View Related
Jun 16, 2015
I have a requirement where in i have around 15 different flat files , filenames are fixed but folder path can be changed(i think i should use a variable for folder path). These 15 files data should go to their respective tables in the database.
Whether I need to create separate data flow task for each file or separate package? In addition to these, example : while importing product data into product table, if product ID already exists, we need to ignore it and upload only the new records.
View 4 Replies
View Related
Aug 25, 2015
I have an excel file that has multiple sheets and I need to import data from each separate sheet to a separate table using SSIS.
E.g. Sheet A data should go to Table A and Sheet B data should go to Table B and so on. Is it possible to do this with out using script task.
View 6 Replies
View Related
Jun 2, 2004
I've read afew articles about .Net and they mentioned not using Access when developing with VS.Net, but the articles didn't go into why. Between the two, is one better/easier to use than the other? Is there a cost difference? Or does it depend on what you are doing?
Any input would be appreciated. Thanks.
View 26 Replies
View Related
Sep 20, 2005
Hi I am using Visual Studio.Net 2003 and SQL 2000 Web applications run using the ASPNET user account.
I have to set up this account and grant it permissions before my Web application will have access to a SQL database. How do I grant permissions ?.
View 1 Replies
View Related
Jun 27, 2006
I have a couple of hundred flat files to import into database tables using SSIS.
The files can be divided into groups by the format they use. I understand that I could import each group of files that have a common format at the same time using a Foreach Loop Container.
However, the example for the Foreach Loop Container has multiple files all being imported into the same database table. In my case, each file needs to be imported into a different database table.
Is it possible to import each set of files with the same format into different tables in a simple loop? I can't see a way to make a Data Flow Destination item accept its table name dynamically, which seems to prevent me doing this.
I suppose I could make a different Data Flow Destination item for each file, in the Data Flow. Would that be a reasonable solution, or is there a simpler solution, or should I just resign myself to making a separate Data Flow for every single file?
View 9 Replies
View Related
Feb 7, 2007
Hey there!
I'm not sure how to explain this but here goes...
I'm a bit stuck, a new project that's come about "requires" me to query multiple databases as if they were tables.
1 Oracle database which stores information on our staff (this database is a part of some MIS software and can't be changed in any way)
1 SQL Server 2005 database which stores information on staff sickness
Basically say theres 1 table in each database
Oracle DB
People
Person_Code
Forename
Surname
SQL DB
Sickness
Person_Code
Daydate
Lets say the query I want to perform is to select all the records from sickness where person code is 22334 and also get their name from the other db, so the output may look like
22334 Dann Rees 01/01/2007
22334 Dann Rees 03/01/2007
22334 Dann Rees 10/02/2007
Now I realise I can write a quick function to pull the information but this is just a basic example. Effectivley what I "need" is to be able to query sickness while sub querying people to get the names, or some kind of pass through query?
Please remember this is just a very simple example and the "actual" queries will be far more complicated, for instance finding all the employees of a certain department who is male and was sick in January. All the data for that example is stored in people (oracle) except for the dates which is stores in sick (SQL 2005). Now these are easy enough if they were tables in 1 database....but their not, their tables in 2 databases, and theres nothing I can do to change that :(
All help appreciated as this is becoming very urgent.
Many thanks
Dann
(I couldnt post this in the General data access forum for some reason)
View 1 Replies
View Related
Jul 16, 2007
Hi all:I am trying to create a website which will search and return results that are from multiple tables in different databases. I have the code to search a singular database right now, and I tried to make a connection to a second one, which worked, but I don't know how to send the proper sql commands to the second database or how to link them in the code. Does anyone have any pointers on creating a site which will search different tables in different databases with a known connection between databases? Any help would be really really really really appreciated.-Shrey
View 3 Replies
View Related
Nov 11, 2007
How can I include tables and views from database A when building a view in database B, if possible?
Same for stored procedures.
View 1 Replies
View Related
Dec 23, 2005
so, I am trying to write an sql that requires information across two
different database. It's under the same sql server. However, the
location of the tables are from different database.
Any links I can read about that can show me how to write the queries?
Thanks in advance, and merry X'mas.
View 1 Replies
View Related
Jun 14, 2001
We have a number of databases(on sqlserver 7.0) which has the same structure but with data for different users. We would like to change the structure of the databases with minimum effort and with minimum down time.
Is there any method to automate the change so that the change is reflected in all the databases when a single database structure is changed?
Any suggestions in this regard are welcome and urgent
View 1 Replies
View Related
Nov 1, 2000
Okay this is a test...actually I am still learning SQL and need some help. Does anyone have any information on being able to move indexes from one database to another. My scenario is I have 3 databases, Development, QA and Production. I want to move/copy indexes I created in Development to the QA database. I have many indexes so I do not want to have to recreated them if I can avoid it. Any suggestions?
Thanks!
View 1 Replies
View Related
Apr 5, 2004
Hi,
Currently developping a c# database based application, which will be used in different establishments worldwide. I'm currently a bit confused by the ms sql features offered. Because the huge data amount which will be fetched during application use from the database, the best option is probably to put a ms sql server in each establishment to realise quick data fething. Problem is that the servers, which are all running on the same database design have to be synchronised real-time. I read about datareplication with the merge option which I thought was a nice solution. The subscribers can only have read-only rights on these subscriber databases, because the merging doesn't work appropriate i heard, so this is no good solution. Then I read about distributing transaction coordination. Seems a good solution as well, but what happens if a server system crashes for one day and then comes back online? It won't be up to date anymore... Right now I think about a combination of these 2 features. DTC in combination with merge replication, but there must be a better solution. Fact is that a lot of users will be editing data worldwide non stop, and everyone has to be up to date. Because of huge data fetching I don't think it's a good solution to let everyone work worldwide with one single database. Is there a possibility to realise this with ms sql server? Because I'm getting more and more confused... Thx in advance.
View 2 Replies
View Related
Feb 5, 2002
I need some guidance! We are running SQL Server 2000. We have 4 or 5 databases with the same schema, and I need to make sure that their structures all stay synched. We will have more soon. I also need certain tables to have the same data across all the databases.
I need a way to manage changes on the multiple databases! Right now I generate a script in Enterprise Manager, then run it in Query Analyzer on each database, manually. There's too much room for error here.
Any suggestions are appreciated! I am currently evaluating Embarcadero's products, but they are complicated and confusing - I'm not sure yet which of their tools is meant for this purpose.
Thanks,
Avi
View 4 Replies
View Related
Jun 25, 2002
I have a problem like this. I have an application which shud be internet hosted. All transactions should be updated to the central server of the net as well as an intranet DB server. (I'll have a DB server - SQL Server 2000 in each intranet). Is this possible.. i'm planning to use ASP to develop the net based application.
Thank you in advance..
Geetha R
View 2 Replies
View Related
Nov 9, 2007
Ok, so right now we have a bunch of queries similar to the following:
Code:
use db001 select co#=(001), fieldvalue from tablename where fieldid = '1'
use db002 select co#=(002), fieldvalue from tablename where fieldid = '1'
what i need to find is if there are duplicates of fieldvalue and have it display all in one.
Basically right now we run all these there are 25 databases and we export everything to a file and then open it in excel. we then format it it so its just co# and fieldvalue and then look for duplicates.
i would like to avoid the whole excel part and just run a query that will display any duplicates of fieldvalue across all 25 databases in "tablename"
hope that makes sense. - Thanks
View 6 Replies
View Related
Aug 14, 2007
Could anyone possibly help me out with an issue I am having...
I need to copy all my SQL2005 databases from Server1 to Server2. How can I, using SSIS, copy all the databases and not just 1 specific one (database transfer task)???
My actual task is much deeper than this, but this is the main problem I seem to be encountering and I cannot find this anywhere on the web...
Over and above the what I have mentioned, is there a way to also specify a name of the target DB the copy will go through to?
e.g. Server 1 has 3 instances, each one with a different copy of the same DB.
I would like to copy all 3 copies to Server 2 running 1 SQL instance, but to target DB's like DB_dev, DB_prod and DB_test.
The latter section is not so crucial at the moment, but the first part is really an issues I am struggling with at the moment...
Any help would be greatly appreciated.
Thanking You in advance!!!!!
View 1 Replies
View Related
Jan 21, 2008
I'm very new to SQL, so you'll have to excuse the nubishness of this question. I've got my trusty O'Reilly book here!
We're using Microsoft SQL Server 2005 on our web server. I'm currently working on a project to get a few MS Access Databases available on the web page.
The web part is actually working fine, with the DBs exported into SQL for performance reasons. (From what I've read pulling data from the .mdb file could cause me problems in the future, so I'm tossing it all onto the SQL server from the start.)
Before I get into the project full-tilt, though, I'm curious about how best to organize all the data?
I'm using the Upsizing Wizard in Access for the conversions, and every new DB gets added under Databases. If this gets approved I may be looking at well over a hundred DBs which will eventually be converted and made web accessible.
I can keep everything neat and tidy on the web server by just tossing things in a few directories, but the menu on the SQL Server Management Studio is going to become a mess really quickly if I let it just keep adding to +Databases.
Is there a way to sort everything into a series of folder/groups, like how System Databases has it?
Thanks in advance,
-Lamune
View 2 Replies
View Related
Jun 20, 2006
I need to design a system which represents multiple "projects" in SQLServer. Each project has the same data model, but is independent of allothers. My inclination is to use one database to store all projects.Looking at the numbers involved, however, I wonder if I would getbetter performance by storing each project in its own database.Suppose I have 50 projects, each with two users and 10,000 rows; itseems to me I'd rather have 50 x 2 users working in a table with 10,000rows than 1 x 100 users working in a table with 500,000 rows.On the other hand, the single database approach seems more elegant froma design perspective. I wouldn't be creating multiple copies of anidentical data model, and I wouldn't be creating new databases as abusiness procedure, every time a new project is required.Here are my questions:1. For the scenario described above, am I correct to assume I will getbetter performance by using multiple databases, or does SQL Server havesome clever way of achieving the same performance in a single database?2. Is the multiple database approach common? If anyone has tried it,please tell me about how it works in practice.-TC
View 9 Replies
View Related
Sep 21, 2007
Hello
we have SQL instance which contains databases (A, B, C...). When ever i configured LS with Standby option, and i want complete restore of database A (Primary) to stand by A (Secondary), then logs will always going to restore...
but SQL server is taking a copy of the Backup like "GJ_Temp_data, GJ_Temp_log" as the default file names, for restore the DB Backups.
if i want to set up for the database B, it is taking "GJ_Temp_data, GJ_Temp_log" again for the database B, and giving error as "the files are already in use".
If i script the Log shipping setup while enabling for Database A, and make sure that i will enabling it for remaining databases B, C, D..
Please advice, and give us best experienceS for enabling LS FOR Multiple databases on the single SQL Instance.
Thanks in advance.
View 1 Replies
View Related
Sep 21, 2006
Hello,
This is probably a simple solution but I'm a bit confused about which flows to use.
I have two databases on different servers that contain the same information but for the appropriate server. I want to retrieve data from each of the servers using a "select" statement.
Would I use a ForLoop and change the connection in a Script Task and then execute the SQL statement? Or can a ForEach Loop be used, but I'm not sure which type of enumerator.
Thanks for any help you can give.
View 5 Replies
View Related
Aug 22, 2006
Hi,
I'm a newbie to SQL Everywhere. I have gon e through the examples and created a C# application to create and a manipulate database on desktop. SFSG. For my application, though, I see the need to work around the 4GB limit. So I will need to segment my data into multiple databases.
Does SQL Server Everywhere supports joins across mutiple SQL/e databases on the same machine ? I have not found any examples that guide to address this issue. can anyone point me to something/help me write queries that join D1.T1 and D2.T2.
View 5 Replies
View Related
