How To Save &&"New&&" / &&"Updated&&" Records Only In To Destination Tables
Hi
I have a requirement like, i need to save all the records from my Flat File on Monthly basis to my database table. In the next month, the flat file may be added with 10-20 records and also some updates happened to my old records. Though the faltfile is same for each month, the changes are occured in some records and also added with few records.
So when i am loading this data in to Database table, i need to just update the changed records and also needs to add the new records. I don't need to touch the remaining records.
How can i do that in SSIS 2005 using different data flow tasks ?
Thanks
Kumaran
View Complete Forum Thread with Replies
Related Forum Messages:
Linked Tables - After Path Updated Can't Add Records
Hello-I created a MS Access 2002 database with linked tables on a SQL Serverdatabase by way of a File DSN, A. I have created a form which points toone of the linked tables. After I finished testing it in thedevelopment environment I updated the path for all the linked tables toa new File DSN, B. After this action, I opened the form to test addinga new record and the "add new record" navigation button has beendisabled. Can anyone help? I need to be able to add new recordsthrough the form and can't figure out why I am now NOT able to add arecord. PLEASE HELP!!Thank you,nosenia--noseniaPosted via http://dbforums.com
View Replies !
Save Updated Date When Row Is Updated
Hi,I want to save the last modification date when the row is updated. I have a column called "LastModification" in the table, every time the row is update I want to set the value of this column to the current date. So far all I know is that I need to use a trigger and the GetDate() function, but could any body help me with how to set the value of the column to getdate()? thanks for your help.
View Replies !
Get Last Updated Records?
If I update a recordset a group of records using dynamic SQL where I update the TOP n records, is it possible to get the set of records that was updated? CREATE PROCEDURE usp_Structural_ScheduleComponent @cProject char(7), @cComponentID char(10), @iPour int, @iQuantity int, @iAvailable int OUTPUT, @dtCast datetime OUTPUT AS SET @dtCast = convert(char(10), getdate(), 120) DECLARE @cSql varchar(500) SET @cSql = 'UPDATE tbStructuralComponentSchedule SET PourNumber = ' + CAST (@iPour AS VARCHAR) + ', ScheduledDate = ' + '''' + CAST(@dtCast AS VARCHAR) + '''' + ' WHERE EntryID IN ( SELECT TOP ' + CAST(@iQuantity AS VARCHAR) + ' FROM tbStructuralComponentSchedule ' + ' WHERE fkProjectNumber = ' + '''' + @cProject + '''' + ' AND fkComponentID = ' + '''' + @cComponentID + '''' + ' AND IssueDate IS NOT NULL' + ' AND ScheduledDate IS NULL' + ' ORDER BY EntryID DESC)' EXEC(@cSql) IF(@@ERROR <> 0 OR @@ROWCOUNT < = 0) RAISERROR('Failed to add components to pour!',16,1) SELECT @iAvailable = SUM(CASE WHEN IssueDate IS NOT NULL AND ScheduledDate IS NULL THEN 1 ELSE 0 END) FROM tbStructuralComponentSchedule WHERE fkProjectNumber = @cProject AND fkComponentID = @cComponentID GO -- Is there a way to return the recordset that were modified in the update? Mike B
View Replies !
Big Records Not Always Updated
In our database we have a memo field that sometimes coud be over 9 MBytes. We are using the following code (VB) to update it lReg = Len(fielText) lOffset = 0 Do While lOffset < lReg sChunk = Left(Right(fieldText, lReg - lOffset), 32768) rs("document").AppendChunk sChunk lOffset = lOffset + 32768 DoEvents Loop rs.Update The problem is that sometimes the record is NOT updated and there is NO errors returned by SQLServer. Do you have any suggestion? Is there another way to update records with big fields?
View Replies !
Need To Track Updated Records
Hi all, I have a data of applicants. Everyday we dump this data into SQL server. Now I need to generate reports everyday so that we can track how many records get updated everyday. Now the thing is that the applicants are in various stages. So my reports need to track the how many applicants changed from stage "abc" to "pqr" and how many changed from "pqr" to "xyz". Now it is not necessary that all the records change stages everyday. thanks in advance, Rohit
View Replies !
How Can I Report On Entered And Updated Records
My database has many table, each table has a DateEntered (datetime), EnteredBy (nvarchar(50), LastUpdate (datetime), and LastUpdateBy (nvarachar(50). Is there an easy (ha) way to pull a list of the records that were entered and/or updated for a date range. Hopefully without a select for each table. Maybe a tool someone knows of?
View Replies !
Protecting Records From Being Updated And Deleted
Hi I am using sql server 2005 express and would like to keep all my fields from being both updated and deleted. In other words, once I create a new record, I would like to have it protected from being deleted and I dont want the field values to be updated/changed from the values initially entered. Is there a way to this without running triggers or changing database permissions and user roles? I tried making the database read-only, but then of course i cant add new records. Thanks
View Replies !
Updated Records From A Table Insert Into Another
Hi: I need to create a trigger to save every record which has been updated in a table (e.g. "Contacts") into another (e.g. "Shadow_contacts). So basically something like a versioning on database level. I have no experience with it and any precise hints/solutions would be very appreciated selmahan
View Replies !
UPDATE Stored Proc -- Updated Over All Of My Records
I wrote a stored proc to be implemented in a datagrid. I went and used it on a record to update some information and it updated THE WHOLE DATABASE WITH THAT ONE RECORD.. IF anyone could shead some light on what I'm doing wrong that would be great. Here is the syntax for my stored proc. CREATE PROC updateResults2 @id int, @address1 nvarchar (50), @address2 nvarchar (50), @CITY nvarchar (33), @ST nvarchar (10), @ZIP_OUT nvarchar (5), @ZIP4_OUT nvarchar (4), @home_phone nvarchar (22), @NEWPhone nvarchar (20) AS UPDATE Results SET address1 = @address1, address2 = @address2, CITY = @CITY, ST = @ST, ZIP_OUT = @ZIP_OUT, ZIP4_OUT = @ZIP4_OUT, home_phone = @home_phone, NEWPhone = @NEWPhone GO As said previously it ran but it updated the WHOLE DATABASE with the same change (WHICH I DIDNT WANT IT TO DO)!! Thanks in advance. RB
View Replies !
Modifying Inserted Or Updated Records Without Recursion?
I am looking for a way to update the information in the "inserted" logical record without having to call something like this: UPDATE tblX SET ValueY = ValueA/100 FROM tblX INNER JOIN inserted ON tblX.ID = inserted.ID because this may result in the update trigger firing (again). I'd like to avoid this. As a better example, if I have a table of transactions with cost and price info, plus a flag indicating whether the transaction has been merged to AR or GL, I want to be able to update cost or price directly, which will clear the flag and indicate that I have a batch out of balance. This I can do easily with Update(Cost) Or Update(Price). But, I also need to be able to change the supplier in the transaction record. If I do this, I want the trigger to fetch the new cost and price for me. If the cost or price change, I still want to update the flag. To get there I can call UPDATE tbl SET Cost = @EffectiveCost, Price = @EffectivePrice FROM tbl INNER JOIN inserted ON tbl.ID = inserted.ID which is recursive. I can't say "UPDATE inserted" because it's a logical table. Is there any way to avoid this and to set values in the middle of an insert/update?
View Replies !
Can I Export Tables So That Existing Tables In Destination Database Will Be Modified?
I'm working on an ASP.Net project where I want to test code on a localmachine using a local database as a back-end, and then export it tothe production machine where it uses the hosting provider's SQL Serverdatabase on the back-end. Is there a way to export tables from oneSQL Server database to another in such a way that if a table alreadyexists in the destination database, it will be updated to reflect thechanges to the local table, without existing data in the destinationtable being lost? e.g. suppose I change some tables in my localdatabase by adding new fields. Can I "export" these changes to thedestination database so that the new fields will be added to thedestination tables (and filled in with default values), without losingdata in the destination tables?If I run the DTS Import/Export Wizard that comes with SQL Server andchoose "Copy table(s) and view(s) from the source database" and choosethe tables I want to copy, there is apparently no option *not* to copythe data, and since I don't want to copy the data, that choice doesn'twork. If instead of "Copy table(s) and view(s) from the sourcedatabase", I choose "Copy objects and data between SQL Serverdatabases", then on the following options I can uncheck the "CopyData" box to prevent data being copied. But for the "CreateDestination Objects" choices, I have to uncheck "Drop destinationobjects first" since I don't want to lose the existing data. But whenI uncheck that and try to do the copy, I get collisions between theproperties of the local table and the existing destination table,e.g.:"Table 'wbuser' already has a primary key defined on it."Is there no way to do what I want using the DTS Import/Export Wizard?Can it be done some other way?-Bennett
View Replies !
Log All Updated Tables
We have a third party process that runs and updated several SQL tables. Is there any way to find out what tables are being updated and store it in another table?
View Replies !
Deleting Records To Save DISK Space...
Hi, In my data archiving process , I would end up deleting hunders records from the production databases but would that help me save some DISK space immediately??? Should I run some DBCC command to get some disk space ? if SO ..!! What should I do after deleting the records..???? Thanks Cheriyan.
View Replies !
Finding Updated Tables
I have a front end inteface that adds data to tables in a SQL database.My question is:Is there an easy way of finding out what tables are affected by the update? Is there a sp_ or DBCC command that would give me the update or altered tables?
View Replies !
How To Insert Only New Records (not Already In Destination)
Greetings from a SSIS newbie still on the learning curve... I have a SQL query OLE DB Source that yields a result set that I'd like to put into a SQL Server Destination, but only those records that don't already exist in the destination. Is there a recommended (read: easy) way to accomplish this? Perhaps a handy transformation? I have tried to incorporate a subquery in my source query along the lines of: SELECT fields FROM table1 WHERE keyfield NOT IN (SELECT keyfield from table2) which works in design time but fails at the server with a cryptic: "Error: Unable to prepare the SSIS bulk insert for data insertion." and "Error: component "<component>" (16) failed the pre-execute phase and returned error code 0xC0202071." Don't mean for that to cloud the issue, though. I would appreciate any help given. Thanks! Paul
View Replies !
How To Keep Tables Updated That Are Imported From Other Database
Dear All, I am making web application using Asp.net C#(Visual Studio2005). And Sql server 2000 as a back End. Actually I am fetching data from other database(named as retailexcel but in same server). I have wriiten a view (in retailexcel database) to get required data from four table of other database. My problem is when I fetch those data(about 40000 to 50000 record) It taking to much time. I tried catch feature of asp.net2.0. Time reduced but not significantly. So I am trying to import those tables to my database (named Inventory) from that retailexcel. Now problem is every day or two, record of those tables changes. How can I keep update my tables (which are importing from retailexcel) Please suggest me some idea. Thanks
View Replies !
Keep Remote SQL Data Tables Updated
Hello, I need some guidance in the best method to accomplish this task. I have a network with a SBS 2003 server and a SQL 2000 member server in the SBS domain. I have a remotely hosted website at discountasp.net with a SQL 2000 database. The website will host a modified e-commerce kit where corporate clients can order parts. I need to keep the products table on the remotely hosted site as updated as possible. The website orders are placed via email from the commerce kit and the fulfillment department proccesses the order against the SQL server in the SBS domain. Any ideas on how to keep the data updated on the website?
View Replies !
Insert/Updated SP From Multiple Tables
How do I insert unrelated statistical data from three tables into another table that already exist with data using an insert or update stored procedure? OR... How do I write an insert/Update stored procedure that has multiple select and a where something = something statements? This is what I have so far and it do and insert and does work and I have no idea where to begin to do an update stored procedure like this.... CREATE PROCEDURE AddDrawStats AS INSERT Drawing (WinnersWon,TicketsPlayed,Players,RegisterPlayers) SELECT WinnersWon = (SELECT Count(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing WHERE W.DrawingID = DS.CurrentDrawing), TicketsPlayed = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.Active = 1), Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.AccountID = S.AccountID ), RegisterPlayers = (SELECT Count(*) FROM Student S WHERE S.AccountID = S.AccountID ) FROM DrawSetting DS INNER JOIN Drawing D ON DS.CurrentDrawing = D.DrawingID WHERE D.DrawingID = DS.CurrentDrawing GO
View Replies !
How To Find The Last Updated Tables In A Database
Hi, I am trying to create a data dictionary for a huge application which has aroung 300 tables in the database....when i perform any operation in the application some tables are updated.... can you help me to find out how can we find out the last updated tables in the database ??
View Replies !
Missing Records In The Replication Destination DB
Hi Gurus, We have a SQL 2000 DB1 ( publisher) which is replicated using transactional replcation onto the secondary server DB2 ( subscriber). we have identity columns on the DB1 and we created those tables with 'not for replication' clause. we skipped the following errors in the replication profile '2601:2627:8102:20598'. on DB2 ( the subscriber ) some records are missing in some tables . ( I verified this with record count differences for some tables in both the servers DB1 & DB2. How to find out what are all the records missing & the cause. I have listed the msrepl_errors table on DB1 , distribution databases all I'm seeing there are error code 8102 , unable to update identity column. Any Idea is appreciated. - Thanks Jay
View Replies !
Delete Records That Don Exist In The Destination
Hi all, I am developing an ETL wherein the requirement is to do an incremental load and at the same time, if there is a record that got deleted in the Source delete it from the destination too, makes sense. The approach am doing is, pick data from the SRC and Destination, pass it onto a Merge join component, do a Full Outer join, then pass the rows to a conditional split. Newly Added records and updated records I can handle, how do I handle the Deleted records? Am I correct in the way I am doing or there is something better to handle this? Thanks in advance
View Replies !
Update Records: Source And Destination Are The Same
All, I am having a brain-fart or something. I need help!!! I'm developing a transform that selects data from a SQL script, then transforms that data, and then needs to update the data back into the same source table. The transform is working great, but I can't figure out the update table part. I've got it adding the transformed records to the table, but that's not what I need to do. I need to update the table with the changed data. Any sample code or blog links would be greatly appreciated! Thanks for the help! -Scott
View Replies !
Delete Records In Destination Table
Short Question: How do I delete all records from a destination table prior to appending new data to that table? I am working with a SQL database that was migrated from MS Access. All relationships, primary keys, and identity columns have been set identically to the MS Access database values. The MS Access database is still being used as the database of record until the SQL database is fully functional with front-end, etc. I want to delete the information stored in all the SQL tables, and then append the MS Access values to the SQL tables. I was able to write delete and append queries in MS Access to correctly transfer data to the SQL tables. However, I would prefer doing this through SSIS because I have several other sources of data to move to a SQL Server database and most of those other sources are not a MS Access database. Due to relational entegrity settings, I need to delete the records from 8 tables in a specific order. I have tried independent control objects for each of the 8 tables with data flow objects of either "OLE DB Command" or "OLE DB Source" with the SQL command as "Delete From TableName". Results of the debug indicate everything is "green" but no records were deleted fromt the tables.
View Replies !
Propagation Of Event Notification When Tables Are Updated.
I would like to propagate an event signal to an external applicationwhen a table in my MSSql2000 server is updated.Prog A; I have an external application adding records to a table.Prog B; I have another external application using this table as well.When Prog A creates a new record in the Table, how can I have Prog B benotified of the event without polling the table or creating a linkbetween Prog A and Prog B.Thanks.
View Replies !
Replacing Tables In SQL Express By An Updated Table Of The Same Name Through VB2008 Code
Hello, I have an application written in Visual Basic 2008, using a SQL Express 2008 database. This application runs in several locations on stand-alone PC's and there is no network connecting them. I have to maintain a SQL database in a central location an would like to be able to distribute updated tables from that central location by using an USB stick and replace the tables of the same name in the different locations with the updated ones on my USB stick. This I would like to do using code within my VB2008 application. Can someone please help me on the way to achieve this. Is it possible to give some concrete code examples, as I am a newbie as a SQL Server user. Many thanks and greetings, Michel
View Replies !
How Can You Save Data In Tables But Not In The Log File?
Hi All, I'm developing a credit card application, and would like to store some sensitive data in a table temporarily, but be absolutely positive it either never goes into the log file in the first place, or is securely deleted from the log file when I delete the data. There's millions of other bits of data in the log, so truncating or otherwise disposing of the whole log is not an option. In particular, I'm capturing the credit card mag stripe at the beginning of a transaction, and saving it while the work is done to complete the transaction. (ie, a pilot walks in, says "Top off the G4 with JetA, here's my card, have it ready when I get back.") We want to save the swipe info for maybe a couple of hours (it is encrypted during this time), use it for the authorization, then throw it away as per credit card industry rules. Is there any way I can save the encrypted text string in a normal MSSQL field (Varchar(200)), have it stay around for a few hours as the user logs in and out of the database, then delete the record and be sure it's nowhere in the log? TIA, George Lehmann Horizon Business Concepts, Inc. Broken Arrow, OK
View Replies !
Insert / Update In Master Table And Also Save A History Of Changed Records : Using Data Flow/simple Sql Queries
Hi, My scenario: I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to 1) separate the master (static) data from the flat files, 2) check whether that data is present in the master table, if not then insert that data into the master table 3) If data present then move that existing record to an history table and then update the main master table. All the 7 fields need to be checked to uniquely identify a single record in the master table. How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this. Thanks in advance for your help. Regards, $wapnil
View Replies !
Garbage In Destination Tables With DTS!
I am setting up DTS to transfer all the object excluding the users. Will there be any problem in transfering the text datatypes?I heard that it will populate garbage for some datatypes!? Thanks for any answer. SRR
View Replies !
PROBLEM: Tables Are Locking Up When Attempting To Save Data
Over the weekend, one of our out-of-house programmers ran an update to our three main tables. I know these are kind of broadstrokes, but basically he compared the data and updated certain fields when it met certain conditions (lots of rules basically). The three tables are one-to-one and contain a little over a million records. The comparison file contained around 400k records. The scripts made it through 250k records from the comparison file before he had to stop it for the weekend. When I came in to test the data yesterday - I was met with problems on my front end application - it would lock up on the write back to the database. I went into EP and experienced the same thing after making any changes to a record, it would just lock up. This only appears to be a problem on the 2 bigger tables of the 3. I currently have 12 gigs or so free on that box and I have already shrunk the log and data files. I tried removing and re-adding the indexes, but I am freezing up everytime I try to either change or delete the Clustered Index on the Primary Key. I don't know why, but I thought maybe that was my issue. I know this is pretty broad, but even if someone could give me ideas as to why SQL would lock up like that when trying to just save the data, it would be most helpful. NOTE: There were NO structure changes in the update process and my restored data from Friday works perfect. If you need more info, just ask. Thanks in advance for the help. Don elitecobra2000@yahoo.com
View Replies !
Linked Reports Not Being Updated When Master Report Is Updated
Since updating to SQL Server 2005 SP2 I've noticed two things about Linked Reports. 1. I do a lot of 'Snapshot' reports. With SP-1 if I updated a master report and made any changes to the Parameter List - it undid all my custom parameter changes on linked versions (restored to the Master Reports Defaults). While this is no longer happening with SP2 - it is still 'unhiding' the parameters. 2. With SP-1 if I added/deleted columns or made other changes to the report structure - the linked reports would pick up on the changes with their next refresh. With SP-2 I'm finding that I have to 'Re-link' the linked report back to the master report before the changes are refreshed. This is very time consuming especially with each report having 8 or more Snapshot reports pre-set up. Am I missing something - or is this a 'bug'... Any help would be appreciated...
View Replies !
Regarding The Query To Save The Ids Of The Edited Names From Different Tables Into A Single Table.
Here with the below query iam binding my gridview with industry name,company name,Plant Name,Group Name related to the IDs in Audit table.Select Aud.Ad_ID_PK,Aud.Audit_Name,Ind.Industry_Name,Cmp.Company_Name,Pla.Plant_Name,Gr.Groups_Name,Aud.Audit_Started_On,Aud.Audit_Scheduledto,Aud.Audit_Created_On from Industry Ind, Company Cmp, Plant Pla, Groups Gr, Audits Audwhere Ind.Ind_Id_PK =Aud.Audit_Industry and Cmp.Cmp_ID_PK =Aud.Audit_Company and Pla.Pl_ID_PK =Aud.Audit_Plant and Gr.G_ID_PK =Aud.Audit_Group and Ad_ID_PK in (select Ad_ID_PK from Audits) Now i want to edit these names. when i click on edit in gridview these names will be filled into textboxes and when i change the names it should compare the name with particular tables and should get the Id of that and store in Audits table. For example: i have this data in my audits table: Commercial83312 2 2 2 1 Here Commercial83312 is ID of that Audit and 2,2,2,1 are the Industry,Company,Plant and group Ids for that particular audit.In the front end i can see the names of this particular IDs. when i edit the industry name in the UI it must check the name with industry table and get the ID of the changed name and store it in audit table. so the data may be changed in audits table as : Commercial83312 4 2 2 1 so here the industry ID is changed I need the stored procedure for this. please help me,its very urgent...
View Replies !
Insert Records From Foxpro Tables To SQL Server Tables
Hi, Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables: 1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables. 2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables. I only know the following ways to import Foxpro data into SQL Server: #1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables #2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables #3. DTS Foxpro records directly to SQL Server tables I'm thinking whether the following choices will be better than the current way: 1st choice: Change step 1 to use #2 instead of #1 2nd choice: Change step 1 to use #3 instead of #1 3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2 Thank you for any suggestion.
View Replies !
How To Print Or Save The Structure And Attributes Of All Tables And Columns In A Database (simply)
Hi. A newbie question. I am tearing my hair out trying to work out how in Sql Server 2005 to get a printout (or even better a file I can save that i could incroporate in a wrod document), or both, which shows the structure of all the tables in my database. I want to list all tables (or selected tables perhaps) , and all columns in those tables, with the attributes of each column (nvarchar(2) etc or decimal(18,5) etc). Just a simple listing of all tables and their columns and the attributes of those columns. Surely this must be possible with a simple one click operation in Sql Server 2005. I have created a database diagram which gives me part of what I want, but that just shows the tables, relationships, and column names, not the attributes of each column which is what I need as well. I don't want to have to start installing third party products to do this, and I have no great script writing capabilities. Surely such a basic function is easily acheivable with one or two clicks in Sql Server 2005 from a menu somewhere in sql server mangaement studio? Thanks in Advance for your help. Chris M
View Replies !
1 Flat File To 2 Destination Tables With Different Mappings :Please Help
Here is the Scenario. I have a flat file with name "Employee.txt " (Full url: C:Employee.txt) .The File content is like this Anil,Engineering,1997 Sunil,Sales,1981 Kumar,Inventory,1991 Rajesh,Engineering,1992 (Note: Items are Comma Seperated) Now, i have a SQL Server database called "EmployeeDB" which has 2 tables "TblEmp1", "TblEmp2". The Table is like this. TblEmp1 : Columns EmpName EmpDept EmpjoinDate TblEmp2 : Columns EName EDate Edept using integration services (SSIS) i need code(vb.net or c#) to Create a dtsx package so that i can push the flat file content to these 2 tables. And the condition is : After Executing the package Data loaded in TblEmp1 should be like this EmpName EmpDept EmpjoinDate Anil Engineering 1997 Sunil Sales 1981 Kumar Inventory 1991 Rajesh Engineering 1992 (No change in order compared to source) And Data loaded inTBLEmp2 should be like this EName EDate Edept Anil 1997 Engineering Sunil 1981 Sales Kumar 1991 Inventory Rajesh 1992 Engineering Now, i know that we need to do like this in wizard 1) Create a flat file source component. 2) Create flat file connection and set the properties of flat file (delimeters and other things) 3) Create a Multicast Component. 4) Create a Path between Flat file source and Multicast. 5) Create 2 destination component(each for a table). 6) Create path from multicast to 2 destination components 7) Create a OledbConnection and set table names for 2 destination components.. 7) Now,i have to do mapping for destination1.8) Now, i have to do mapping for destination2( this mapping will be different from mapping done for destination1 because iam not inserting the data in the same order in which iam doing for TBLEmp1. I have done it in wizard.I need to do it through code and i know that its not complicated.Please find the attached file with this mail.i have attached a screen shot of how i have done in wizard.The main problem is Mapping differently for 2 destinations from source.for 1st one we can have a forloop for mapping.but for 2nd one iam confused!!
View Replies !
Should You Save Results Of Intermediate Data Flow Steps To Temporary Tables Or Raw Files?
Hi, I'm just starting off in SSIS and have a question that I can't find an answer to... I'm loading in a number of files (in separate Data Flows) and performing some transformations on them before merging them back together. What I'm not sure about is what I should be doing with the data at the end of each of my "Import Data From XXXX Flat File" Data Flows. Am I better off using OLE DB Destinations (or SQL Server Destinations) and saving this intermediate data to temporary tables, or am I better off using a Raw File Destinations and saving this intermediate data to files? Or is there, perhaps, a better option that I'm currently unaware of? If the Raw File Destination is the way to go, then isn't there a maintenance issue with cleaning up all the files created? And will there not be a management issue to ensure that there is sufficient disc space available on the drive you are saving to? I'm a bit confused and overwhelmed by SSIS at the moment, so any help would be much appreciated! Thanks in advance, Lawrie.
View Replies !
Importing Data From One Source In Two Destination Tables Linked By A Foreign Key
Hi, I have a new problem when I import data from an xml source file in two destination tables. The two tables are linked by a foreign key... for example : table MOTHER (MOTHER_ID, MOTHER_NAME) table CHILD (CHILD_ID, MOTHER_ID, MOTHER_NAME) After a lot of transformations data are inserted into MOTHER table and I want to insert other fields of the data flow in CHILD table. To do this, I need the MOTHER_ID field that is auto incremented in MOTHER table. My problem is to chain the insertion in CHILD table after the insertion in MOTHER table to be sure that the relative row in MOTHER table is really inserted. I haven't find any solution to chain another transformation task after my flow destination "Insert into MOTHER table". The only solution I have found is to create a new flow control to insert data in CHILD table, using a lookup transformation task to bind with MOTHER table... But with this solution all my flow control transforms are made two times... Is there a solution to chain two insertions with a foreign key constraint in a data flow? Thanks Regards, Arnaud Gervais.
View Replies !
Combining Multiple Tables Into A Single Flat File Destination
Hi, I want to combine a series of outputs from tsql queries into a single flat file destination using SSIS. Does anyone have any inkling into how I would do this. I know that I can configure a flat file connection manager to accept the output from the first oledb source, but am having difficulty with subsequent queries. e.g. output personID, personForename, personSurname, 1, pf, langan ***Roles roleID, roleName 1, developer 2, architect 3, business analyst 4, project manager 5, general manager 6, ceo ***joinPersonRoles personID,roleID 1,1 1,2 1,3 1,4 1,5 1,6
View Replies !
SQL Server 2005 - Save Tran Save Point Name Case Sensitive?
Hello:I didn't find any documentation that notes save point names are casesensitive, but I guess they are...Stored Proc to reproduce:/* START CODE SNIPPET */If Exists (Select * From sysobjects Where Type = 'P' and Name ='TestSaveTran')Drop Procedure dbo.TestSaveTranGoCreate Procedure dbo.TestSaveTranAsBeginDeclare@tranCount int--Transaction HandlingSelect @tranCount = @@TRANCOUNTIf (@tranCount=0)Begin Tran localtranElseSave Tran localtranBegin Try--Simulate Error While ProcessingRAISERROR('Something bad happened', 16, 1)/*If this proc started transaction then commit it,otherwise return and let caller handle transaction*/IF (@tranCount=0)Commit Tran localtranEnd TryBegin Catch--Rollback to save pointRollback Tran LOCALTRAN --<< NOTE case change--Log Error--Reraise ErrorEnd CatchEndGo--Execute Stored ProcExec dbo.TestSaveTran/*Should receive the following message:Cannot roll back LOCALTRAN. No transaction or savepoint of that namewas found.*//* END CODE SNIPPET */What is really strange, if there is a transaction open, then no erroris thrown. So if you execute as so:/* START CODE SNIPPET */Begin Tran--Execute Stored ProcExec dbo.TestSaveTran/* END CODE SNIPPET */There is no "Cannot roll back LOCALTRAN...." message.Questions:1-)Can someone confirm save point names are case sensitve and this isnot happening because of a server setting?2-)Is this a logic error that I am not seeing in the example codeabove?We have changed our code to store the save point name in a variable,which will hopefully mitigate this "problem".Thx.
View Replies !
Problem When Applying A Snapshot When Tables Have Been Updated During Snapshot Generation
Hi I seem to have a strange problem when applying a snapshot when the tables in the publication have been updated while the snapshot was being generated. Say for example there is a table called RMAReplacedItem in the publication. When the snapshot starts being applied to the subscriber, a stored procedure called sp_MSins_RMAReplacedItem_msrepl_css gets created that handles an insert if the row already exists (ie it updates the row rather than inserting it). However, after all the data has been loaded into the tables, instead of calling this procedure, it tries to call one called sp_MSins_RMAReplacedIte_msrepl_cssm - it takes the last letter of the table name and adds it to the end of the procedure name. The worst part is that this causes the application of the snapshot to fail, but it doesnt report what the error is, and instead it just tries applying the snapshot again. The only way i have managed to find which call is failing is to run profiler against the subscriber while the snapshot is being applied and see what errors. I have run sp_broswereplcmds and the data in there is what is applied to the subscriber - ie the wrong procedure name. All the servers involved are running sql 2005 service pack 2. The publisher and subscriber were both upgraded from sql 2000, but the distribution server is a fresh install of sql 2005.
View Replies !
Flat File Destination - Don't Create File If 0 Records
Hello friends, I have the following (simplified): 1. Flat File Source 2. Conditional Split, Case Good = !ISNULL(KEY) Case Error = ISNULL(KEY) 3. Case Good -> Writes to Good Flat File (with timestamp in the title) 4. Case Error -> Writes to Error Flat File (with timestamp in the title) Most job runs have no errors but the error file is created as a zero byte file anyway. If there are no error records I don't want the error file created. How might I accomplish this? Thanks
View Replies !
|
TRACKER
