Importing Data In Datatable Using SSIS Package Trigger On Insert Is Not Firing On That Table
Hi
I am Importing data in datatable using SSIS package . I made trigger on that table on insert. The trigger on insert is not firing on that table
Please help
Thanks
CP
View Complete Forum Thread with Replies
Related Forum Messages:
Insert Trigger Sometimes Not Firing
hi all i have an issue with an insert trigger sometimes not firing. here is the trigger CREATE TRIGGER Insert_tPABillToAddr ON [dbo].[tPA00175] FOR INSERT AS INSERT into tPABillToAddr ( chrJobNumber ) SELECT chrJobNumber FROM inserted when the user enters a new this table is to insert one column into another table. the thing is, sometimes it does not do the insert. any ideas as to why? it is a very uncommon thing, lets say once out of every 20 inserts does it fail. but it is crucial that it never fails. thanks
View Replies !
AFTER INSERT Trigger Not Firing In SQL 2005
Nothing fancy; just a trigger on a sharepoint table that supposed towrite a record to another SQL table.set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER TRIGGER [TV_UpdateFileSyncProgress]ON [dbo].[Docs]AFTER INSERTASBEGINSET NOCOUNT ON;BEGINIF EXISTS (SELECT null FROM inserted WHERE DirName like'csm/%/Shared Documents')BEGINIF NOT EXISTS (SELECT null FROM inserted INNER JOINTV_FileSyncProgress fp ON LOWER(RTRIM(fp.LeafName)) =LOWER(RTRIM(Replace(Replace(inserted.DirName,'csm/',''),'/SharedDocuments','') + '' + inserted.LeafName)))BEGININSERT INTO TV_FileSyncProgress (InternalOrigin, ExternalOrigin,ChangeType, SiteId, DirName, LeafName, FlagForDelete)SELECT0,1,1,SiteId,'F:commonExtranet',Replace(Replace (DirName,'csm/',''),'/SharedDocuments','') + '' + LeafName,0 FROM insertedENDENDENDEND
View Replies !
Trigger Not Firing On Cascade Delete Table
I have three tables: BulkMemberHeader - which has a cascade delete on BulkMemberDetail of any related records BulkMemberDetail €“ which has a DELETE trigger which gets the member ID from deleted and deletes the member record from the member table Member This issue: > When I delete a record from BulkMemberDetail the trigger fires and deletes the record from the Member table as it should > If I delete a record from the BulkMemberHeader, all corresponding records in BulkMemberDetail are deleted, but the trigger to delete the record in the Member table does not seem to fire Is it a limitation on SQLServer 2000 that does not allow triggers to fire in a scenario like this? Any suggestions or comments would be great. Thanks, Mike
View Replies !
Importing Table And Data From Access To SQL Server 7.0 Using DTS Package
Hi I have some Access DB files(.Mdb files) in the local Directory...I need to Create a DTS Package which takes Source Files as the local MDB files..destination server is SQL Server 7.0 and i need the DTS Package to Create all the tables and import all data from MDB files to SQL server 7.0. Once the Import of table and Data is done from Access to SQL Server 7.0 using Package i need to have this package run everytime during the Installation process so i am Planning to have this Package run from VB Script. Please Advice me in doing this.... Thanks in Advance Kamalesh D
View Replies !
Update/Insert DataTable Into SQL Table
I'm importing XML file into DataTable and need to Insert Data into SQL Table. I'm not sure if its posible to take a DataTable with Data and insert into DataAdapter. From there i wanted to update SQL using TableAdapter? Any Tips? Thanks,
View Replies !
Firing A Trigger When A User Updates Data But Not When A Stored Procedure Updates Data
I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based: Code Block create table #IncomeAndExpenseData ( recordID nvarchar(5)NOT NULL, itemID int NOT NULL, itemvalue decimal(18, 2) NULL, monthitemvalue decimal(18, 2) NULL ) The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr. For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table. If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers. How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?
View Replies !
Problem When From Sequence File Insert Into Oracle Destination Table In SSIS Package
Hi , i was used the Follwing DataFlow for my Package.using Oracle 8i FalteFile Source -------------> Data Conversion --------------->OLEDB Destination (Oracle Data table) using above control flow to map the Source file to Destination . When i run the SSIS Package teh Folwing Error i got "Truncation Occur maydue to inserting data from data flow column "columnName " with a length of 50 " regarding this Error i i understood its for happening Data Length . so that i was changed the Source Column Length Exactly Match with the The Destination table. still i am getting this Error. pls any one give me a solution . SHould i Change the DataType also? pls give your suggestion Thanks & Regards Jeyakumar.M chennai
View Replies !
Trigger Firing And Not Firing
I have a trigger on a table that just updates a last_modified_date and this works fine on our production server. Now I have to update some data and I do not want the trigger to fire. I cannot disable or drop the trigger because the productions systems needs the trigger. Anyone an idea of how to solve this problem?
View Replies !
Importing DTS Package To SSIS Under X64 More Options
Hey there! I have a DTS import package from an old SQL server thattakes flat text files, does some munging on the columns using ActiveXscripts and imports those flat files to various tables.The package itself is rather tedious because it does this munging onanumber of large files with a number of different fields.When I bring the package in via SSIS, importing/migrating thepackage,the column mappings are completely gone, as are the ActiveXscripts...Is there a known issue with ActiveX column mapping and SSIS?I'd hate to have to create the package all over again using SSIS, itwould be a LOT of work...Andyhttp://www.fboweb.com
View Replies !
Error Importing SSIS Package
I can't import a package from the SSIS to the file system of my SQL Server Management Studio. Every time i've tried the following message appears. What should i do to correct this problem? Any ideas? =================================== Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS) ------------------------------ Program Location: at Microsoft.SqlServer.Dts.Runtime.Application.SaveToDtsServer(Package pPackage, IDTSEvents pEvents, String sPackagePath, String sServerName) at Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction.ImportPackage(ImportPackageAsForm dlg)
View Replies !
Putting Data From DataTable/DataSet Into SQL Server Table?
Hello, I created this DataTable, add rows of data to it, and then display the data on to a form via a repeater. Dim ds As DataSet = New DataSetDim dtTableName As DataTable = New DataTable("dtTableName") dtTableName.Columns.Add("Description")dtTableName.Columns.Add("ItemNumber")dtTableName.Columns.Add("Quantity")dtTableName.Columns.Add("Price")ds.Tables.Add(dtTableName) What I need to do now is create a table in SQL Server and update the database with the data I've collect in my dataset. How do I bind and update this data to a sql server table? Thanks!James
View Replies !
Trigger - Require Help For Updating A Trigger Following An INSERT On Another Table
A ID Name 1 Joe 2 Fred 3 Ian 4 Bill B ID 1 4 I want to be able to create a trigger so that when a row is inserted into table A by a specific user then the ID will appear in table B. Is it possible to find out the login id of the user inserting a row? I believe the trigger should look something like this: create trigger test_trigger on a for insert as insert into b(ID) select i.id from inserted i where --specific USER
View Replies !
Trigger - Require Help For Updating A Trigger Following An INSERT On Another Table
Table 1 First_Name Middle_Name Surname John Ian Lennon Mike Buffalo Tyson Tom Finney Jones Table 2 ID F M S DOB 1 Athony Harold Wilson 24/4/67 2 Margaret Betty Thathcer 1/1/1808 3 John Ian Lennon 2/2/1979 4 Mike Buffalo Tyson 3/4/04 5 Tom Finney Jones 1/1/2000 I want to be able to create a trigger that updates table 2 when a row is inserted into table 1. However I€™m not sure how to increment the ID in table 2 or to update only the row that has been inserted.
View Replies !
Trigger Not Firing
OK, I'm at a loss..it must be staring me right in the face. I have a junction table that relates 2 tables, with a unique key of the composit of the 2 keys. There is also an indicator that says 1 relationship between the 2 tables is "primary" and there should only be one of those. So I figured a trigger to take care of it...but I can't seem to get it working...I wrote sample sql to mimic the inserted table as well, and it seems correct, but the trigger just does not fire. Any ideas? CREATE TABLE [dbo].[PIF_MEP99] ( [PIFRecID] [int] NOT NULL , [MEPRecID] [int] NOT NULL , [PrimaryInd] [char] (1) NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[PIF_MEP99] WITH NOCHECK ADD CONSTRAINT [PIF_MEP99_PK] PRIMARY KEY CLUSTERED ( [PIFRecID], [MEPRecID] ) ON [PRIMARY] GO INSERT INTO PIF_MEP99(PIFRecID, MEPRecID, PrimaryInd) SELECT 1,1,'Y' UNION ALL SELECT 2,1,'N' UNION ALL SELECT 3,2,'N' GO CREATE TRIGGER dbo_PIF_MEP99_tr_Rule1 ON dbo.PIF_MEP99 FOR UPDATE, DELETE AS SET NOCOUNT ON -- Rule 1: Prevent and MEP from having more than 1 PIF as Primary IF Exists ( SELECT * FROM inserted i INNER JOIN PIF_MEP99 p ON i.MEPRecID = p.MEPRecID AND i.PrimaryInd = 'Y' AND p.PrimaryInd = 'Y') BEGIN ROLLBACK TRAN RAISERROR 500003 'Attempting to Insert 2 Primary PIFs for an MEP' END GO SELECT * FROM PIF_MEP99 GO SELECT * FROM (SELECT 4 AS PIFRecID,1 AS MEPRecID,'Y' AS PrimaryInd) AS i INNER JOIN PIF_MEP p ON i.MEPRecID = p.MEPRecID AND i.PrimaryInd = 'Y' AND p.PrimaryInd = 'Y' GO BEGIN TRAN INSERT INTO PIF_MEP99 (PIFRecID, MEPRecID, PrimaryInd) SELECT 4,1,'Y' COMMIT TRAN GO SELECT * FROM PIF_MEP99 GO SELECT * FROM (SELECT 5 AS PIFRecID,1 AS MEPRecID,'Y' AS PrimaryInd) AS i INNER JOIN PIF_MEP p ON i.MEPRecID = p.MEPRecID AND i.PrimaryInd = 'Y' AND p.PrimaryInd = 'Y' GO DROP TABLE PIF_MEP99 GO Brett 8-) Hint: Want your questions answered fast? Follow the direction in this link http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx Add yourself! http://www.frappr.com/sqlteam
View Replies !
Trigger Not FIRING With BCP
Hi, Anyone got an idea why this does not work. I have a trigger that is supposed to fire as an INSERT is done on a table. If I manually insert (insert into....), the trigger fires. If I use BCP to insert, the trigger DOES NOT FIRE..... Davy
View Replies !
Trigger Not Firing ...
Afternoon All ... I have a trigger on a table that works great when a single record is updated BUT it seems not to fire when I try to do a bulk update as in ... update TableA set field3 = ms.field3 FROM TableA as ta, #ms as ms where field1 = ms.field1 and field2 = ms.field2 Why does this happen and how can I correct it. Thanks!
View Replies !
Trigger Not Firing
I have build a SQL Trigger that fires on the update of a specific column; this works perfectly when I test using SQL or even the SQL Server GUI but when I do the update from .NET it doesn€™t fire. I have a dataset that gets modified and then I call sqlDataAdapter.Update(Dataset) - the row is successfully modified in the database but alas - the trigger isn't fired. Any help would be appreciated.
View Replies !
Trigger Not Firing On Update
hi,Here's the scenario1) I am running a DTS job to fetch some rows from Oracle2) The job populates the Table A as step 13) Then it fires a update statement which updates the rows in Table B.Here's the statementUPDATE Table B SETtime = case when (select median from Table A where sno = sno and TableA.stno=70 ) is null then timeelse (select median from Table A where Table B.sno = Table A.sno andTable A.sstno=70) end ,endWHERE EXISTS (select sstno from Table A where Table B.sno = TableA.sno)There is a trigger on table B which should fire as soon as thevalue>15.When I fire the update statement direcly with a higher value than itfires the trigger.update table B set time=17 where b.sno=1000But not when the job runs...I am puzzled.Thoughts?AJ
View Replies !
Trigger Not Firing, DTS Load
I have a table that is getting refreshed from DB2 using DTS (I believe the DBA is doing a DELETE and an APPEND). I have a trigger on this table ON APPEND, INSERT, but the trigger never fires. When I manually update the data, the trigger fires no problem... Is DTS capable of updating a SQL Server table without firing the trigger? I'm an Oracle guy, and this is my 1st experiences with SQL Server, so I'll put the code here and if you want to point out any bad practices (such as the way i converted the DB2 TIMESTAMP to a SQL Server DATETIME , please do. FYI, the DB2 TIMESTAMP is getting loaded into the SQL Server table as a VARCHAR(26) Carl CREATE TRIGGER trig_SAWakeUp ON tsnro FOR INSERT, UPDATE AS DECLARE @snro_stus char(10) DECLARE @snp_sht_dtm as datetime SELECT @snro_stus = snro_stus FROM tsnro IF (RTRIM(@snro_stus) = 'ASSIGNED') OR (RTRIM(@snro_stus) = 'REFRESHED') BEGIN SELECT @snp_sht_dtm = CONVERT(DATETIME, SUBSTRING(evt_dtm,1,19)) FROM tsnro INSERT INTO TSNP_SHT_DTM (SNRO_STUS, SNP_SHT_DTM ) VALUES (@snro_stus, @snp_sht_dtm) END ELSE BEGIN INSERT INTO TSNP_SHT_DTM ( SNRO_STUS, SNP_SHT_DTM ) VALUES (@snro_stus, @snp_sht_dtm) END
View Replies !
Artificially Firing A TRIGGER
I have a table (table 1) that has a trigger attached to it, which sends data to table 2. Both tables are attached to an appliction. In theory, the trigger populates table 2 (from table 1) from the data entered from the user interface application. But when I import data from the backend via DTS into Table 1, the trigger does not send the data to table 2. What do I need to do ??? Thank you in advance !!
View Replies !
Update Trigger Not Firing.
Hi All, I have an update trigger which stores the date a record was updated by a GIS app. It worked in SQL2000 but when I copied and pasted the code into SQL2 its not firing. Any Ideas? Below is the code. Create TRIGGER [dbo].[Date_Entered] ON [dbo].[ZONING_OP] FOR UPDATE AS UPDATE [dbo].[ZONING_OP] Set Date_Entered = GetDate() Where ID = (Select ID from Inserted)
View Replies !
Create Trigger Not Firing Via ODBC
I created a trigger in the "source table" that will "feed" and secondtable. The trigger is as follows:CREATE TRIGGER [FeedToP21] ON dbo.FromUPSFOR INSERTASDeclare @Count intSelect @Count = Count(*) from InsertedIf @Count > 0BeginInsert into ToP21Select i.* From Inserted iLeft Join ToP21 ton i.recnum = t.recnumWhere t.recnum is nullEndIf @@ERROR != 0Rollback TranA record was created in the "source table" via ODBC, however, thetrigger does not seem to have fired to create the record in the secondtable.If I create a record manually using SQL Server Enterprise Managerwithin the "tableview" the trigger fires and a duplicate record iscreated in the second table.Is there a fix for this problem?Thank you in advance.
View Replies !
UPDATE Trigger Firing Too Late?
Hi. This SHOULD be something simple, but I am apparently missing something. I have a Users table where a user's status is a varchar(100). I'm trying to implement a trigger so that when a user's status is changed to any string other than what it was before the update the trigger would change the LastUpdated field to current date/time. Here's the trigger, I replaced the update of LastUpdated with a simple print statement. For some reason, it seems like the trigger is firing after the update statement has committed because the values of @m_status_new and @m_status_old are always the same so this trigger always prints 'status has not changed.' What am I doing wrong? Thank you greatly for any help provided. USE myDB IF EXISTS (SELECT name FROM sysobjects WHERE name = 'tr_update_users' AND type = 'TR') DROP TRIGGER tr_update_users GO CREATE TRIGGER tr_update_users on dbo.Users FOR UPDATE AS DECLARE @m_status_new varchar(100), @m_status_old varchar(100), @m_UserID int SELECT @m_status_new = UserStatus, @m_UserID = UserID FROMinserted SELECT @m_status_old = UserStatus FROMUsers WHEREUserID = @m_UserID IF @m_status_new != @m_status_old BEGIN print 'status has changed' END ELSE BEGIN print 'status has not changed' END GO
View Replies !
UPDATE TRIGGER WITHOUT RECURSIVE FIRING
I have an update trigger I created that updates a field based on the user who last updated the record. Under 7 the only way it would work was to have recursive triggers firing turned on. Under 2000 might there be a btter solution. The code is below. Thanks CREATE trigger tr_cmsUpdt_MARS on dbo.PATIENT_MEDICATION_DISPERSAL_ for UPDATE as -- updates record with sql user and timestamp --created 11-28-00 tim cronin DECLARE @muser varchar(35), @rec_lock_status int, @ptacpt_status int set @muser = current_user begin UPDATE PATIENT_MEDICATION_DISPERSAL_ set MODIFIED_BY = @muser, MODIFIED_TS = getdate() from deleted dt WHERE --DT.MODIFIED_BY <> 'DBO' AND PATIENT_MEDICATION_DISPERSAL_.RECORD_ID = dt.RECORD_ID end
View Replies !
Error W/ Trigger When Firing On 'DELETE XYZ WHERE IN (1,2,3)'
Hi, Thanks for looking at this post. I currently have a trigger that fires when a row is inserted or deleted on a table. The idea behind the trigger is that when a row is inserted (representing a sub-category for images), the categories parent needs to have some work done on it. I currently have the trigger working just fine with single inserts and single deletes: sql Code: Original - sql Code CREATE TRIGGER smvcModImageManagerCategory_insert_delete_pdfManger_sync ON smvcModImageManagerCategory FOR INSERT, DELETE AS DECLARE @pdfId INTEGER; DECLARE @parentPdfId INTEGER; DECLARE @grandparentPdfId INTEGER; DECLARE @parentId INTEGER; DECLARE @grandparentId INTEGER; DECLARE @tableName VARCHAR( 255 ); -- If I am being inserted or deleted, and I am not a top level -- category, then my parent's pdf record needs to be set so that -- the pdf file is updated IF (SELECT id FROM Inserted) IS NOT NULL BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Inserted), @parentId = (SELECT parentCategory FROM Inserted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId); END ELSE BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Deleted), @parentId = (SELECT parentCategory FROM Deleted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId) END -- If I am not a top level category, set my parent's pdf to be -- updated IF @parentId <> -1 BEGIN SELECT @parentPdfId = (SELECT pdfManagerId FROM smvcModImageManagerCategory WHERE id = @parentId); UPDATE smvcModPdfManager SET data_last_updated = GETDATE() WHERE id = @parentPdfId; END GO CREATE TRIGGER smvcModImageManagerCategory_insert_delete_pdfManger_syncON smvcModImageManagerCategoryFOR INSERT, DELETEAS DECLARE @pdfId INTEGER; DECLARE @parentPdfId INTEGER; DECLARE @grandparentPdfId INTEGER; DECLARE @parentId INTEGER; DECLARE @grandparentId INTEGER; DECLARE @tableName VARCHAR( 255 ); -- If I am being inserted or deleted, and I am not a top level -- category, then my parent's pdf record needs to be set so that -- the pdf file is updated IF (SELECT id FROM Inserted) IS NOT NULL BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Inserted), @parentId = (SELECT parentCategory FROM Inserted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId); END ELSE BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Deleted), @parentId = (SELECT parentCategory FROM Deleted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId) END -- If I am not a top level category, set my parent's pdf to be -- updated IF @parentId <> -1 BEGIN SELECT @parentPdfId = (SELECT pdfManagerId FROM smvcModImageManagerCategory WHERE id = @parentId); UPDATE smvcModPdfManager SET data_last_updated = GETDATE() WHERE id = @parentPdfId; ENDGO However, when I execute a statement like: sql Code: Original - sql Code DELETE FROM smvcModImageManagerCategory WHERE (smvcModImageManagerCategory.id IN ('86','87','88','90','91')) DELETE FROM smvcModImageManagerCategory WHERE (smvcModImageManagerCategory.id IN ('86','87','88','90','91')) I get an error because the virtual 'Deleted' table has more than one record in it. So, what I really need is advice on how to turn the above trigger into something that will be able to handle multiple deletes. Thank you.
View Replies !
Multiple Insert Call For A Table Having Insert Trigger
Hi I am trying to use multiple insert for a table T1 to add multiple rows. Ti has trigger for insert to add or update multiple rows in Table T2. When I provide multiple insert SQL then only first insert works while rest insert statements does not work Anybody have any idea about why only one insert works for T1 Thanks
View Replies !
SSIS And Insert Trigger Not Going Off
I am using the SQL SERVER 2005 Import and Export Wizard to import a file from Excel 2003 into a table with an insert trigger. When the records are added, the trigger is not going off. There is no FastLoad option anywhere and I do not know how to add the Fire_Triggers paramater programatically. Please help. Thank you.Matt
View Replies !
Calling An SSIS Package From A Trigger Causes It To Run For Ever
hi, when calling an SSIS package from a trigger causes it to run for ever. Know why?? In Detail; Consider that there are two tables with the same schema in a db. Lets name that Test1 and Test2. I develope a package whihc will transform data from Test1 to Test2. I initiate this package from a Trigger for Insert on Test1. For eg. CREATE TRIGGER Trigger_Test1 ON Test1 AFTER INSERT AS BEGIN EXEC xp_cmdshell 'dtexec /FILE "C:TestTestPackage1.dtsx"' END This runs for ever when a record is inserted into the Test1 table. But, when the trigger is on someother table , everything works fine.For eg, if the trigger is on the table TT1 & this trigger initiates the same package while inserting a record into TT1, everything is fine. Can anyone help me on this. Thanks Man
View Replies !
Trigger A SSIS Package On Its Completion...
Is there a way to trigger a SSIS package automatically when it ends successfully. Actually i am moving data from one database to other.......i am passing start date and end date as my paramter...So is there some way once my SSIS package ends succesfully then it can start again on its own reading a new set of date range from a file or something.... I appreciate any reposne in advance Thanks...
View Replies !
Trigger Firing Multiple Stored Procedure
DESCRIPTION: I have an FTP server set up to log via ODBC into a table FTPLog. The trigger on table FTPLog fires when new files are received to process and load the file via a stored procedure. CREATE TRIGGER tr_new_file ON FTPLog AFTER INSERT AS SET NOCOUNT ON DECLARE @filename varchar(50), @logtime datetime DECLARE c1 CURSOR FOR SELECT filename, logtime FROM inserted OPEN c1 FETCH NEXT FROM c1 INTO @filename, @logtime WHILE @@fetch_status = 0 BEGIN EXEC sp1 @filename, @logtime FETCH NEXT FROM c_inserted INTO @filename, @logtime END CLOSE c1 DEALLOCATE c1 END PROBLEM: There are multiple problems with this setup. The first problem is that when the stored procedure gets executed it takes a long time to process the file and the FTP server never returned a completion code to the ftp client and ended with a connection time out from the client. My users keep asking if the FTP failed but it didnt fail. The server returned a completion code too late. PROBLEM2: When multiple files are ftp to the server on the same session, only the first one gets process. Even though my code loops through all the records because the processing takes a long time the second one never gets executed. If I replace the EXEC sp1 statement with a PRINT statement then it's working fine. SOLUTIONS and SUGGESTIONS highly appreciated.
View Replies !
DTS Package Encounters Error While Importing Data.
Hello All, I am encountering the following error message while impoting a text file from unix server into SQL Server 2000 table. I contacted MS before and was advised to run a SP which I already did, but I am still getting this error. The intresting thing is that this error always occur at 130,000th row everytime. I wonder it has to do with binary vs ASCII file format on Unix server, when accessing from SQL server within the Window server environment. I know other groups are able to import the files directly fine *************************************************************************************************** DATA TRANSFORMATION SERVICES: Data Pump Exception Log Package Name: DTS_Julie Package Description: (null) Step Name: DTSStep_DTSDataPumpTask_1 @@ExecutionStarted Execution Started: 03/29/2006 10:55:04 AM @@LogSourceRows: \aadfsusers psfile_a.txt.Source @@LogDestRows: \aadfsusers psfile_b.txt.Dest @@ErrorRow: 130001 Error at Source for Row number 130001. Errors encountered so far in this task: 1. Error Source: Microsoft Data Transformation Services Flat File Rowset Provider Error Description:Too many columns found in the current row; non-whitespace characters were found after the last defined column's data. Error Help File:DTSFFile.hlp Error Help Context ID:0 @@SourceRow: Not Available @@DestRow: Not Available @@ExecutionCompleted Execution Completed: 03/29/2006 10:59:09 AM **************************************************************************************************** Execution Started: 03/29/2006 10:59:14 AM @@LogSourceRows: @@LogDestRows: @@ExecutionCompleted Execution Completed: 03/29/2006 11:00:48 AM ****************************************************************************************************
View Replies !
Firing DTS Package From An ASP
Does anyone here know of a way to execute a DTS package from an ASP that works? I have been using a variation on Microsoft's example that has yet to work. I know that this code is getting a handle to both the package object and the task object because it recognizes if I have them spelled incorrectly and gives me an error. On the other hand when everything is correct it simply claims to execute but nothing really happens... My screen will read Executing... Done. The script goes something like this, any suggestions?: Const DTSReposFlag_Default = 0 Const DTSReposFlag_UseTrustedConnection = 256 Dim oPackage, strResult Dim oPump Set oPackage=Server.CreateObject("DTS.Package") oPackage.LoadFromSQLServer "hoaorg10","","",DTSReposFlag_UseTrustedConnection ,"","","","DirCopy" set oPump = oPackage.Tasks("Copy Data from Results to [lhr2000].[dbo].[Households] Task").CustomTask oPump.SourceSQLStatement = "SELECT * FROM HOREFPA WHERE NEIGHBORHOOD = 'testnbhd'" oPackage.Execute Response.Write "Executing package...<BR>" For i = 1 To oPackage.Steps.Count If oPackage.Steps(i).ExecutionResult = DTSStepExecResult_Failure Then oPackage.Steps(i).GetExecutionErrorInfo lpErrorCode iStatus = False strErr = oPackage.Steps(i).Name + " in the " + oPackage.Description + " failed.<BR>" End If Response.Write strResult Next If iStatus = True Then strResult = oPackage.Description + " Successful<BR>" Response.Write strResult End If Response.Write "Done.<BR>"
View Replies !
Importing Unicode Data Using BULK INSERT
Hi - I'm trying to import a unicode text file into SQL Server 2000 (sp1) with limited success. We get erros stating that the column collation is incorrect or an unexpected EOF I can get the file to import if I don't specify a format file, but we need to use the format file as our source files will have a smaller number of columns than the target table. Any one been able to use BULK INSERT with the FORMATFILE option when using a WIDECHAR file format? BOL states that -w prevents a format file being used for bcp, but doesn't mention BULK INSERT. Thanks, Paul
View Replies !
Firing Already Made DTS Package -- From Vb.net
Someone please help me with this. I'm trying to fire off an already created DTS package. This package is stored within SQL Server's -- underneith the Data Transformation Services / Local Packages section. HOW CAN I FIRE THIS OFF FROM A VB .NET APPLICATION I'm familiar with strored procedures and using them in vb.net so if somone could lead me down that road I would be very much appriciated. Thanks in advance everyone, RB
View Replies !
Skip First Row In A Datatable At Insert Into SQL...
Hi, I have the code below. I need to skip the first row in the datatable as it has the headers. This works now, but my gridview gets the header row inserted as a record.Private Shared Sub InsertData(ByVal sourceTable As System.Data.DataTable, ByVal destConnection As SqlConnection) ' old method: Lots of INSERT statements ' first, create the insert command that we will call over and over: destConnection.Open() Using ins As New SqlCommand("INSERT INTO [tblAppointmentDisposition] ([contactdate], [dnbnumber], [prospectname], [businessofficer], [phonemeeting], [followupcalldate2], [phonemeetingappt], [followupcalldate3], [appointmentdate], [appointmentlocation], [appointmentkept], [applicationgenerated], [applicationgenerated2], [applicationgenerated3], [comments], [newaccount], [futureopportunity]) VALUES (@contactdate, @dnbnumber, @prospectname, @businessofficer, @phonemeeting, @followupcalldate2, @phonemeetingappt, @followupcalldate3, @appointmentdate, @appointmentlocation, @appointmentkept, @applicationgenerated, @applicationgenerated2, @applicationgenerated3, @comments, @newaccount, @futureopportunity)", destConnection) ins.CommandType = CommandType.Text ins.Parameters.Add("@contactdate", SqlDbType.Text) ins.Parameters.Add("@dnbnumber", SqlDbType.Text) ins.Parameters.Add("@prospectname", SqlDbType.Text) ins.Parameters.Add("@businessofficer", SqlDbType.NVarChar) ins.Parameters.Add("@phonemeeting", SqlDbType.Text) ins.Parameters.Add("@followupcalldate2", SqlDbType.Text) ins.Parameters.Add("@phonemeetingappt", SqlDbType.Text) ins.Parameters.Add("@followupcalldate3", SqlDbType.Text) ins.Parameters.Add("@appointmentdate", SqlDbType.Text) ins.Parameters.Add("@appointmentlocation", SqlDbType.Text) ins.Parameters.Add("@appointmentkept", SqlDbType.Text) ins.Parameters.Add("@applicationgenerated", SqlDbType.Text) ins.Parameters.Add("@applicationgenerated2", SqlDbType.Text) ins.Parameters.Add("@applicationgenerated3", SqlDbType.Text) ins.Parameters.Add("@comments", SqlDbType.Text) ins.Parameters.Add("@newaccount", SqlDbType.Text) ins.Parameters.Add("@futureopportunity", SqlDbType.Text) ' and now, do the work: For Each r As DataRow In sourceTable.Rows For i As Integer = 0 To 16 ins.Parameters(i).Value = r(i) Next ins.ExecuteNonQuery() 'If System.Threading.Interlocked.Increment(rowscopied) Mod 10000 = 0 Then 'Console.WriteLine("-- copied {0} rows.", rowscopied) 'End If Next End Using destConnection.Close() End Sub
View Replies !
With Ssis :flat File Importing Data Problem
Hello, I have a problem with some in a file. when i excute ma package to import data with my *.cvs file ssis bloqued le data flow in the line number 1042 and output this error : column delimiter note found for the column 50 wich is the last column . How can i resolve this problème please. Thanks all
View Replies !
Importing Data And Script-processing Errors In SSIS
I'm currently trying to pull data from a ProvideX database and replicate it in a collection of SQL Server tables. However, I'm having a heck of a time trying to convert some strange decimals stored by the ProvideX database. As an example of the data I'm trying to retrieve, I'll see something like [. 1] or [. 1] ([]'s are to show the bounds of the field). After analyzing the data, it seems the decimal in the field represents a 1,000 placeholder. Thus [. 1] really means 1, and [. 1] really means 10. Something like .100 would be 100. 6.500 would be 6500. As you can imagine, the spaces are causing errors when trying to pull the data, and I can't for the life of me figure out to just pull it as a string, run a script to convert it to a correct number, and then save the transformed data into SQL Server. When running the import wizard, it seems I'm being forced to pull these columns as decimals. Currently I'm trying to just pull the data out "as is" and throw it in a raw file, to be processed out of SSIS. Obviously doing it all within SSIS would be ideal, but if that can't be done, I'll do whatever it takes. I should also say I'm new to SSIS packages, but not necessarily new to SQL Server or SQL in general. 1) How can I pull these columns as strings? If I try to change the Export columns in the source query data flow step, it gives me an error saying that I can't do that. 2) If I have to pull as decimals, how can I capture the row on error, process it, and send it back to the export? So far, when I get an error, I lose all information in the row to the right of and including the error field. I appreciate any responses, as I'm kind of going in circles at this point. If this sort of thing has been discussed here prior, I apologize...I didn't find it in any searches I did. Please just point me in the right direction if you've dealt with this sort of problem before. It seems to me that it should be an easy thing to do. I'm just not finding any tutorials on it.
View Replies !
Win 2003 X64 - SSIS Error Importing Xls And Access Data
I am having a problem importing data from xls and access into my SQL2005 DB using SSIS. Would appreciate any help in getting this resolved. Environment: Xeon 64 bit processor machine/Win 2003 64 bit (x64)/SQL 2005 64 bit Some of the resources that I have dug up so far have pointed to Jet Engine SP8 and WOW64. A search on the box shows this: C:WindowsSysWOW64Msjet40.dll €“ File version is 4.0.9025.0 Not sure what is missing. The following is the error from the import from xls. The one from access is very similar. ================================================================================== Task Import abc_xls Validation has started [DTS.Pipeline] Information: Validation phase is beginning. Progress: Validating - 0 percent complete [Excel Source [1]] Error: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. [DTS.Pipeline] Error: component "Excel Source" (1) failed validation and returned error code 0xC020801C. Progress: Validating - 50 percent complete [DTS.Pipeline] Error: One or more component failed validation. Error: There were errors during task validation. Validation is completed [Connection manager "Excel Connection Manager"] Error: An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
View Replies !
Insert And Update Trigger On Same Table
I currently have 2 tables as follows:CREATE TABLE [CRPDTA].[F55MRKT119](mhan8 int,mhac02 varchar(5),mhmot varchar(5),mhupmj int)GOCREATE TABLE [CRPDTA].[F55MRKT11](mdan8 int,mdac02 varchar(5),mdmot varchar(5),mdmail int,mdmag int,mdupmj int)What I would like to do is place a trigger on F55MRKT119 which willinsert records to the F55MRKT11 if they do not exist in that tablebased on the [mdan8] field. If the record does exist I would likeUpdate the corresponding record and increment either the [MDMAIL] orthe [MDMAG] based on the inserted [MHMOT]. What I have so far is asfollows:TRIGGER #1:CREATE TRIGGER trgIns_Summary ON [CRPDTA].[F55MRKT119]FOR INSERTASBEGININSERT INTO CRPDTA.F55MRKT11select INS.MHAN8, INS.MHAC02, INS.MHMOT,case when INS.MHMOT='MAG' then 0 ELSE 1 end,case when INS.MHMOT='MAG' then 1 ELSE 0 end,'0' from INSERTED INSWHERE ins.mhan8 not in(select mdan8 from crpdta.f55MRKT11)ENDTRIGGER #2:CREATE TRIGGER trgUpd_Summary ON [CRPDTA].[F55MRKT119]FOR UpdateASBEGINUPDATE CRPDTA.F55MRKT11SET MDMAIL= case when INS.MHMOT='MAG' then 0+MDMAILwhen INS.MHMOT<>'MAG' then 1+MDMAIL end,MDMAG= case when INS.MHMOT='MAG' then 1+MDMAGwhen INS.MHMOT<>'MAG' then 0+MDMAG endfrom INSERTED INS JOIN CRPDTA.F55MRKT11on(ins.mhan8=mdan8)ENDFor instance if I do the following insert:INSERT INTO CRPDTA.F55MRKT119VALUES('212131','VK4','AL4','0')thenINSERT INTO CRPDTA.F55MRKT119VALUES('212131','VK4','MAG','0')This is what I expect in both tables:[CRPDTA.F55MRKT119] (2 Records)MHAN8 MHAC02 MHMOT MHUPMJ------ ------ ----- ------212131 VK4 AL4 0212131 VK4 MAG 0[CRPDTA.F55MRKT11] (1 Record)MDAN8 MDAC02 MDMOT MDMAIL MDMAG MDUPMJ----- ------ ----- ------ ----- ------212131 VK4 AL4 1 1 0The insert part works fine in that it iserts in both tables with thecorrect values. However it seems as if the Update protion is failingfor some reason. WHat I have tried so far is setting the trigger orderfor the update to run first and vice-versa, but still no luck. Anyhelp would be appreciated.
View Replies !
Insert Trigger To Update Table
Hi,Does anyone know of a simple way to do this? I want to create aninsert trigger for a table and if the record already exists based onsome criteria, I want to update the table with the values that arepassed in via the insert trigger without having to use all the 'set'statements for each field (so if we add fields in the future I won'thave to update the trigger). In other words, I want the trigger codeto look something like this:if exists (select * from TableA where Fld1 = inserted.Fld1) then//don't do insert, do an update instead (would i want to rollback here?and will I have access to the 'inserted' table still?)Update TableASet TableA.<all the fields> = Inserted.<all the fields>where Fld1 = inserted.Fld1end ifAny help or ideas would be appreciated.Thanks,Teresa
View Replies !
Trigger To Insert Or Update 2nd Table
I am new to triggers and need help on the following: I have a hourly table that inserts new rows every hour but I need to either Insert or Update the daily table with the sum of the reading from the hourly table. If a row exist in the daily table with the date of the hourly table, then I need to update this row but if it doesn't exist, I need to insert this row. Thanks for any suggestions... Alan
View Replies !
TRIGGER On INSERT INTO SELECT FROM Table
hi need help how to do this TRIGGER on INSERT INTO SELECT FROM table + add one row for evry employee (on insert) this is table tb_test1 empid fname unit fld1 fld2 fld3 fld4 fld5 ---------------------------------------------------------------------------------------- 111 aaa 1 a b c d d 222 bbb 3 a c e g g 333 cccc 5 s h t u j INSERT INTO [nili].[dbo].[tb_test2] ([empid] ,[fname] ,[unit] ,[fld1] ,[fld2] ,[fld4] ,[fld5]) SELECT [empid] ,[fname] ,[unit] ,[fld1] ,[fld2] ,[fld4] ,[fld5] FROM [nili].[dbo].[tb_test1] i need to insert into tb_test2 + add TRIGGER ADD one row for evry employee val_orginal=1 (the orginal ROW) val_orginal=2 (the extra ROW) this is table tb_test2 after the insert HOW TO THIS empid fname unit fld1 fld2 fld3 fld4 fld5 val_orginal ------------------------------------------------------------------------------------------------- 111 aaa 1 a b c d d 1 111 aaa 1 - - - - - 2 222 bbb 3 a c e g g 1 222 bbb 3 - - - - - 2 333 cccc 5 s h t u j 1 333 cccc 5 - - - - - 2 and tnx for the help
View Replies !
Package Level OnError Event Handler Not Firing (as If Not There)
Hi all, I have a package on which i've applied a package level OnError event handler. The OnError event handler includes a Script Task (that builds up a string of errorCode, errorDescription, MachineName etc...) and a WebService Task that calls a webservice to send an email including the built up string from the script task in the body of the email. This has worked fine in one package where i've applied it but for some reason in a second package the existence of an OnError package level event handler seems to be completed ignored. I'm causing various package object to fail but the OnError handler never fires. I know the obvious answer is find what's different between the two packages but i can't see that any is different (in relation to package level OnError event handling). Has anyone else come across this? Any suggestions? Thanks M.
View Replies !
|
TRACKER
