How To Transfer To Multiple Tables Based On Environment
Mar 26, 2007
How is it possible to set data to flow through development,test,and production environment???????
I have created one package.Right now i am transferring data to tables in the development environment.
I want to use the same package for several diffrent distinations two based on environment.
Is there a way that the destination componnent sets itself automatically by passing destination tables through registry or is there anything i could or do i need to change manually everytime which ever i want to..
View 9 Replies
ADVERTISEMENT
Jan 14, 2008
Hi,
I am new to SQL Server 2005 SSIS Packages. I want to transfer data from multiple tables from sql server to oracle database. I cannot use export wizard as it creates new tables in the destination (oracle) DB. I already have tables created in the destination DB. When I created an SSIS package, it allowed me create package to tranfer data for only for one table from source to destination. I have created DTS packages in 2000, where you have source db and destination db and just add links for multiple tables. Is there a way I can do it in SSIS. Please let me know.
View 3 Replies
View Related
Nov 21, 2007
I have a form to assign JOB SITES to previously created PROJECT. The JOB SITES appear in the DataList as it varies based on customer. It can be 3 to 50 JOB SITES per PROJECT.
I have "PROJECT" table with all necessary fields for project information and "JOBSITES" table for job sites. I also created a new table called "PROJECTSITES" which has only 2 columns: "ProjectId" and "SiteId".
What I am trying to do is to insert multiple rows into that "PROJECTSITES" table based on which checkbox was checked. The checkbox is located next to each site and I want to be able to select only the ones I need. Btw the Datalist is located inside of a formview and has it's own datasource which already distincts which JOBSITES to display.
Sample:
ProjectId - SiteId
1 - 5
1 - 9
1 - 16
1 - 18
1 - 20
1 - 27
1 - 31
ProjectId stays the same, only values for SiteId are being different.
I hope I explaining it right. Do I have to use some sort of loop to go through the automatically populated DataList records and how do I make a multiple inserts to database table? We use SQL Server 2005 and VB for code behind. Please ask if I missed on some information. Thank you in advance.
View 10 Replies
View Related
Jan 15, 2015
I am trying to create a query that outputs the following data from multiple tables. Here is an example of the raw data right now
Date | MachineNumber | TestName
---------------------------------------
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 510 | NewTest
1/1/2015 | 510 | NewTest
1/1/2015 | 510 | NewTest
1/1/2015 | 620 | Test#1
Here is the desired counted output, I would like to pull distinct Date, MachineNumber, TestName and then count how many times they occur in the raw data form.I do need to perform a case on the date because right now its in a datetime format and I only need the date.
Date | MachineNumber | TestName | TestOccuranceCount
-----------------------------------------------------------------
1/1/2015 | 500 | Something | 4
1/1/2015 | 510 | NewTest | 3
1/1/2015 | 620 | Test#555 | 1
I am pulling three columns with the same names from 8 different tables. What I need to display the date, machine & test name and count how many times a test was run on a machine for that date. I have a feeling this can be handled by SSAS but haven't built an analysis cube yet because I am unfamiliar with how they work. I was wondering if this is possible in a simple query. I tried to set something up in a #Temp table. Problem is the query takes forever to run because I am dealing with 1.7 Million rows. Doing an insert into #temp select columnA, columnB, columnC from 8 different tables takes a bit.
View 9 Replies
View Related
Oct 7, 2006
I would like to transfer selected data from an ODBC-based table to a OLEDB-based table. However, there isn't a data flow source on the Data Flow Design screen to accomodate such an action. Please help!
View 1 Replies
View Related
Jan 20, 2007
I was wondering if you guys might give me some advice on how best to handle a particular scenario i'm struggling with.
I have a client that basically wants web-based-update access to their sql server database. Specifically, for a group of users to be able to access a page where they select a record for editing. the caveat is that no two users should be able to pull up the same record at the same time. Originally I would have thought there was some easier record-locking-mechanism I could exploit within sql server or ado.net itself, but I haven't been able to come up with anything.. so this is my current approach:
The page they use starts-out with basically a blank form. there are custom-built paging controls at the bottom of the screen. they click page-forward to begin and a stored procedure is ran to select a record and update a field on that record to indicate "in-process". when they finish editing the record - or page on to the next record without updating - another stored procedure is ran - updating/resetting the status field on the record appropriately.
The entire page is encapsulated within an ajax.net updatepanel.
The entire page has caching disabled. This works well in conjunction with the first page being blank. if they get out of the app and try to get back in by clicking the back button - all they can do is get to the first (blank) page.
A piece of javascript window.onunload clicks a button on the page that releases the record they currently have selected in the event of a re-direct, clicking back, etc.. it appears to work with everything except a window close. in that case, i have a stored procedure running periodically on the server that checks how long a record has been selected - and if it exceeds the time indicated - resets the record as to allow it to be re-selected later.
In the event of session timeout, they are redirected to another page that tells them their session has timed-out (and since the window.onunload fires - it takes care of releasing the record if they have one on the screen).
The concept seemed to be working well until I started multi-user testing. Now it seems as if two users time it perfectly - they are actually able to both select the same record. it happens pretty rarely, but it does seem to happen. I'm guessing this has to do with how my stored procedure is structured - possibly allowing a tiny-enough window between the record being selected for editing - and the update running to actually status the record as in-process (2 separate sql statements within the one stored procedure).
I believe I also have a found a second quirk in my approach where something is causing the window.onunload event to fire twice in some strange situations.. but that's more annoying/confusing from a logging standpoint than anything..
I've read where people say to ensure you dont update a record that's already been updated - that you should compare the fields before you actually perform the update and ensure they haven't changed since you selected it.. but to me that doesn't solve anything.. if two people select the same record and both spend time working on it - the person that tries to update last has just wasted their time.
I've also toyed with the idea of maintaining a separate table in the database to hold the keys to the currently selected records and use that to keep multiple people from selecting the same record - but honestly i dont know if that approach is any better than what i'm doing now.
anyway, I was just curious if you guys had any advice in regards to how you'd handle a request like this.. or if you see any obvious problems/fixes with my current approach..
I would greatly appreciate any info you could provide-
thanks-
View 3 Replies
View Related
Mar 21, 2008
Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are:
1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup.
1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types.
1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data.
When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately.
I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that.
Thanks in advance for any help/suggestions,
Bob
View 1 Replies
View Related
Sep 11, 2006
Hi All..
We are planning to build a cluster environment on MS SQL 2005. We want to install multiple named instances in a single database server and use cluster for that database server.
Is this option possible ? Any relevant documentation would be helpful
Thanks in advance
Regards
Madhav
View 2 Replies
View Related
Feb 6, 2004
Hi,
I am developing reporting application (access project) which will be used in multi user environment.
Here is what I have:
1 SQLServer database for many users
Each report will be based on:
stored procedure which creates a table filtered for specific dates predefined views will use the newly generated table to show results to the client. However, if more than 1 person runs reports results will not be accurate if each person specified different dates because they will look at the same table and results will match only for a user who called the stored procedure last.
what can you recommend - how to report in multi user environment?
Many thanks
View 2 Replies
View Related
Nov 16, 2005
I have finished a change request from our client. I need to update clients' database with the one in developments.Here is the changes i made to database:Added/Changed some tablesAdded/Changed some stored proceduresAdded data to some dictionary tableThe data in clients' current database MUST be kept. So how can I merge the changed information to clients' database?
View 3 Replies
View Related
Jan 11, 2008
We are trying to move data from 190 tables from one db to another (same tables, different structure) using ssis. The package comprises of 190 data transfers (is there an easier way to do this??).
The package works correctly except for one table. All of the tables have a column called disabled which is defined as bit datatype. However, in the table which has a problem the Disabled column has been incorrectly defined as Int. The data stored within the column is still either 1 or 0. When we run the package it executes ok and we don't get any errors but looking at the destination tables, the one with the disbaled column defined as Int contains -1's instead of 1's.
I had a look at the xml for the package and noticed that SSIS has used the same definition of the Disabled column for all source tables. This means that it has defined it as BOOL (which is correct on all the other tables) even though it should be i4 on our problem table. I assume this has something to do with the problem but I'm a bit stumped and out of time.
If anyone can shed any light on this I would be most grateful.
Thanks.
View 4 Replies
View Related
Jun 16, 2004
Transfer tables from one server to the other on a regular basis. I'm doin this using the DTS import wizard (saved it as a package and scheduled a job). But i noticed that if the table structure changes on the soruce tables, the DTS package will fail.
I want to make a DTS package that imports 6 tables, now how do i make it such that if the structure of the tables changes it shud still work. ie, everytime the tables are tranferred, the old tables on the target server must be dropped and re-created with the source server table definition. I hope this is doable.
Plz help if ne one has done this b4.
Thx
View 4 Replies
View Related
Jan 27, 2008
Hi!
I have 2 tables (both have the same structure):
ID -> bigint (identity, not for replication, primary key)
Url -> nvarchar(1000)
MainUrl -> nvarchar(1000)
Tbl1 cantains about 0,5 mln records, and tbl2 - 1 mln.
What I need, is to copy records from tbl2 to tbl1. But records in tbl1 are unique, and it can't change. (Unique must be only "Url"; (and ID, but it's automatic)). How can I do this in fast way? Now I'm using SELECT for each record in tbl2 to see if it exist in tbl1. But it's a bit slow... Is there any faster method? (One thing: I'm beginner in databeses, so I'm wrote VB application to transfer records. How can I do it using only Microsoft Sql server?)
--------------
I'm forgot to write, I'm using MsSql 2005.
View 7 Replies
View Related
Apr 28, 2004
Hi,
I am have a two tables. I want to transfer data from Table1 to Table2 but depending on BRK_TITLE. For example, if BRK_TITLE is "Testing" then table1.BRK_QUANT would go to Table2.Qty1, if BRK_Title ="My Testing" then it would go to table2.qty2 and so on...
but problem is every time BRK_TITLE changes. these are not constant titles(e.g. title may be 'abc', '134' etc)
If data belongs to title3 then it would go to the specific quantity i.e. table2.qty3
Table1:
DB_CONTRACT
ITEM_NO
BRK_TITLE
BRK_QUANT
----
table2:
DB_CONTRACT
ITEM_NO
BRK_TITLE
qty1,
qty2,
qty3,
qty4,
qty5,
qty6,
qty7,
qty8
I hope i convey my point of view?
I'll really appreciate your help
View 1 Replies
View Related
Aug 24, 2006
Hi, I'm working on Sql Server 2005 Express and I'm trying to transfer over some of my tables with the primary and foreign keys relations as well as the data in them from one asp.net 2.0 website to another.But I can't seem to find the Import/Export option in Sql Server 2005 Express Edition, does anyone know how I can do this?Thanks in advanced.
View 1 Replies
View Related
Jun 14, 2000
Hello:
I need to develop an off-line database that replicates/copies some key tables for reporting. The info is fairly static (codes. etc.)and does not need to be real-time.
Is it feasible that I can set up a scheduled task on SQL 6.5 using Transfer Manager to copy & append data to this off-line db on a schedule.
I may not be able to set-up replication on our production server.
So, I am considering alternatives. Appreciate any input. Thank you :)
View 3 Replies
View Related
Apr 2, 2008
Hi,
I have two tables named Tab1 and Tab2. Both are identical in structure. The only diff is Tab2 has two more additional fields (FromDate and ToDate).
The structure is like below :
Col1
Col2 (Date field)
Col3
Col4
Also Tab 2 have
Col5 (From Date)
Col6 (To Date)
Now I want to transfer some set of reocrds from Tab1 to Tab2. The additional Tab2 field (Col5 and Col6) values should be the minimum and maximum values of Tab1 date field for the current set.
How to accomplish this? Kindly help me in this regard.
Thanks
Somu
View 1 Replies
View Related
Jul 20, 2005
I have a table in mssql7 that has 7000000 record and I want to take100000 records out of it and place them into the new machine withmssql2000. The new machine will also have the same table name, so Iwant to append the 100000 records into that table.Thanks,Royal344--Direct access to this group with http://web2news.comhttp://web2news.com/?comp.databases.ms-sqlserver
View 1 Replies
View Related
Feb 19, 2008
hi iam new to writting stored procedures
so anyone please help me out.
i have to transfer data from one table to three other new tables.
and if there are any duplicates in original table i have to send them to
duplicates table.the remaining data should be send to three other tables.
so can anyone help writting stored procedure for this.
thanks for your suggestions and answers
regards
ramya.
View 20 Replies
View Related
Mar 20, 2008
Hi All,
i have mutiple text file. let us say,a1.txtb1.txtc1.txt
i have to port this text file data into the table (SqlServer Database) which have the same file structure.(i.e)x1 (SqlServer table)y2 (SqlServer table)z3 (SqlServer table)
now i have to transfer a1.txt file data ----to--- x1b1.txt file data ----to--- y2c1.txt file data ----to--- z3
using SSIS. like that, i have to transfer more than 250 files at a time.manually binding 250 files into the package is very cumbersome and time consuming process.
so, can any one give ur valuable sugession to solve this issue.
View 2 Replies
View Related
Jul 12, 2006
Hello, I like to know how to transfer current data displayed in my datagrid into a table in sql server. Any help is much appreciated.Thanks!
View 1 Replies
View Related
Jan 5, 2001
Hello,
I am transferring data from one SQL table to another. The first table has a PK on the unique id only, the second table has PK on five fields (the idea being to reject duplicate records etc. etc.). I am using a DTS package to do this, but when run it will fail when it hits a PK violation. How do I getround this??????? What simple thing am i missing??
TIA DVNC
View 4 Replies
View Related
Jun 22, 1999
When executing an inline insert into command from isqw/w the command executes perfectly. Yet when I try to create a stored procedure with exactly the same insert into statement it will not create. It does not like the database.owner.table prefix on the selected rows. Any suggestions ??
Thanks.... Tom
View 1 Replies
View Related
Jul 4, 2015
Transfer tables with Data from one database to another one on a same server?
View 10 Replies
View Related
May 7, 2008
Hi all,
Currently, my (small) intranet site is storing it's data on a remote SQL server. The danger with this, as has happened several times now, is that the application is twice as vulnerable; if either the webserver or the dataserver malfunctions or is unreachable, the application won't work.
I only recently discovered the possibility to use local database files (MDF files), and this seems like a much better solution for my site. But now I want to transfer the tables that are residing on the dataserver, to the MDF file. The database only contains tables. How do I handle this? I do not have access to the dataserver, only to a few databases that are residing on it. Is this possible using Visual Studio 2008? I have read about a "Bulk Copy Program" (bcp) which is included with SQL Server, but I cannot find a download for just that application.
Or is this totally not the way to go? I've discovered MDF files are a bit more problematic with concurrent connections; having tables open in Visual Studio results in "Site offline" or "Cannot open database" error messages on the website. Problems I've never had to deal with using SQL Server, but they are only minor problems.
Thanks,
Peter
View 3 Replies
View Related
Jan 17, 2012
I have few issues regarding the transfer of the tables from one file group to another file group in SQL 2008 and also How can we backup and restore the particular database based on file group level.
Let’s say I have a tables stored within the different FG. such as
Tables
File group Dimension tables Primary Fact tables
FG1 ...
FG2…
zzz_tables DEFAULT_FG
dim.table1 DEFAULT_FG
dim.table2 DEFAULT_FG
… ….
Here all I want to transfer the dim.table1 ,dim.table2 from DEFAULT_FG to the Primary File group .So is there simple methods for transfer the dim.table1,2 from one FG to another .I have tried somewhat but I couldn’t get the exact way.Secondly after moving those dim.table1 ,dim.table2 from DEFAULT_FG to Primary ,All I want to backup and restore the database only containing the Primary and FG1,FG2… not a DEFAULT_FG.Is it possible or not.?
View 15 Replies
View Related
Jul 23, 2007
Hello,
I created ssis package to transfer tables from one DB to another.
However, I don't find the option where I can make that the transfer with the indexes and pks of the tables. in sql2000 I had this option.
appriciate your help.
Kubyustus
View 9 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
Sep 8, 2006
I have just taken over the job of sorting out a rather poorly designed database. It looks like it was 'upsized' from an access database to the SQL server. The SQL server is the 2000 version.
Now I am trying to generate a report of what the students in the database are owing by referencing the Receipt table and then all the available payment methods and allocations. I was wondering if there was anyway to work out data being displayed twice (Let me demonstrate)
Note1: All the tables are linked by a key of ReceiptNo. From what I can see there is a table for every payment type and allocation but no link between the two other then the receipt number.
Using the query:
SELECT T_Receipt.ReceiptNo, T_cheque.Amount AS Chq_Amount, T_credit.Amount AS Cre_Amount, StandingOrder.Amount AS Stn_Amount,
T_BankTransfer.amount AS Bnk_Amount, T_cash.TotalAmount AS Cas_Amount, T_RentPayment.AmountPayed AS Ren_Paid,
T_AdminPayment.AmountPaid AS Adm_Paid, T_InternetBilling.Total AS Int_Paid, T_Utilities.AmountPaid AS Util_Amount,
T_InvoicePayment.amountPaid AS Inv_Paid, T_OtherPayments.paymentAmount AS Oth_Paid, T_parkingBill.paymentAmount AS Prk_Paid,
T_TelephoneBills.TelephoneCredit AS Tel_Paid, T_DepositPayment.[Deposit payment] AS Dep_Amount, T_Receipt.cancelled AS Canceled,
T_Receipt.RemittanceReceiptNo AS Rec_Ref, T_Receipt.Student
FROM T_Receipt INNER JOIN
T_DepositPayment ON T_Receipt.ReceiptNo = T_DepositPayment.receiptNo LEFT OUTER JOIN
T_RentPayment ON T_Receipt.ReceiptNo = T_RentPayment.RentPaymentNo LEFT OUTER JOIN
StandingOrder ON T_Receipt.ReceiptNo = StandingOrder.ReceiptNo LEFT OUTER JOIN
T_TelephoneBills ON T_Receipt.ReceiptNo = T_TelephoneBills.ReceiptNo LEFT OUTER JOIN
T_parkingBill ON T_Receipt.ReceiptNo = T_parkingBill.ReceiptNo LEFT OUTER JOIN
T_OtherPayments ON T_Receipt.ReceiptNo = T_OtherPayments.ReceiptNo LEFT OUTER JOIN
T_InvoicePayment ON T_Receipt.ReceiptNo = T_InvoicePayment.receiptNo LEFT OUTER JOIN
T_cash ON T_Receipt.ReceiptNo = T_cash.ReceiptNo LEFT OUTER JOIN
T_AdminPayment ON T_Receipt.ReceiptNo = T_AdminPayment.ReceiptNo LEFT OUTER JOIN
T_BankTransfer ON T_Receipt.ReceiptNo = T_BankTransfer.receiptNo LEFT OUTER JOIN
T_Utilities ON T_Receipt.ReceiptNo = T_Utilities.receiptNo LEFT OUTER JOIN
T_credit ON T_Receipt.ReceiptNo = T_credit.ReceiptNo LEFT OUTER JOIN
T_cheque ON T_Receipt.ReceiptNo = T_cheque.ReceiptNo LEFT OUTER JOIN
T_InternetBilling ON T_Receipt.ReceiptNo = T_InternetBilling.ReceiptNo
GROUP BY T_Receipt.Student, T_Receipt.ReceiptNo, T_cheque.Amount, T_credit.Amount, StandingOrder.Amount, T_BankTransfer.amount, T_cash.TotalAmount,
T_AdminPayment.AmountPaid, T_InternetBilling.Total, T_Utilities.AmountPaid, T_InvoicePayment.amountPaid, T_OtherPayments.paymentAmount,
T_parkingBill.paymentAmount, T_TelephoneBills.TelephoneCredit, T_Receipt.cancelled, T_Receipt.RemittanceReceiptNo,
T_DepositPayment.[Deposit payment], T_RentPayment.AmountPayed, T_Receipt.Student
HAVING (T_Receipt.Student LIKE N'06%')
Which gives a result of:
RecNo.
30429
Cheque
250
Deposit
250
30429
679.98
250
This is fine but when I do analysis on this it appears as though the student has paid two deposit payments. I was wondering with out querying each table independently from an application if there was a criteria to specify that I only get one deposit result.
So as such say, give me all the payments but I only want one result from the other tables. I though about discrete but that wouldn't work here.
View 3 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
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 22, 2005
Hello
I have a production database that i need to refresh to our test environment daily. The database size is 700 MB.
I do not need to transfer the stored procedures and triggers , users and logins.
Would a DTS package that runs every night be the best and the easiest solution to implement or should i look into log shipping and snapshot replication.
thanks
View 2 Replies
View Related
