Handling Bad Data
Jul 25, 2007
I have an SSIS package that takes in a flat file and pushes the data into a table. However, once in a while the file will have some bad data in it (for example, this particular time I have too many delimiters on one line). I want the package to redirect the row to an error table and keep going with the processing for the rest of the data. To do this, I have hooked up an error output from the flat file source to an OLE DB destination and assigned all the rows to Redirect Row on Error in the Error Output section of the flat file source. Unfortunately, it does not work! The flat file receives an error and stops. Is this because something different has to happen when it is a problem with the whole row? Any help would be appreciated, thanks.
View 6 Replies
ADVERTISEMENT
Jan 2, 2008
I have a peculiar requirement. I need the unmatched data to passed on a bad data table as well as tied to a dimension "Others". Please guide how to suffice these two condition simultaneously
View 1 Replies
View Related
Feb 21, 2007
Hi,
I want to fetch Unicode data from MS SQL server 2005.I have created one user with russian language characters.
Following is my code snippet:
------------------------------------
wchar_twchar1[55];
char VALUE[255];
while (rc != SQL_NO_DATA)
{
rc = SQLFetch(hstmt);
if (rc != SQL_NO_DATA)
memset(VALUE,0,255);
int length=wcstombs( NULL,(wchar_t *)&wchar1, wcslen(wchar1));
char* strChar = (char *) malloc((length +1 )* sizeof(char));
if (strChar != NULL)
{
count++;
memset(strChar,0, length + 1 );
int i =wcstombs(strChar, wchar1, wcslen(wchar1) );
printf( "i is %d",i);
}
printf( "The length is %d",length);
wprintf(L" <%s>", wchar1);
printf(" simple char <%s>", strChar);
wprintf(L" simple char <%S>", wchar1);
}
-----------------------------------
CAn anybody help me ?
I think problem is in fetching of name.
Does anybody have working code for fetching unicode data from SQL server 2005.
Also I want query that will tell the locale setting of the server and also client?
Thanks-----
View 4 Replies
View Related
Apr 18, 2007
Hi Guys,
I am facing problems with concurrent access in SQL Server 2000,The scenario is that the DB contains one huge de-normalized table containing 40 million records.
The application frequently queries this table to populate other derived tables,the sql queries take a long time to return results.
So if one query is in execution the other user's query goes into a
wait mode.Please suggest how I can better this.
Or do I need to upgrade to 2005.
Regards,
Prashant
View 2 Replies
View Related
May 2, 2007
I have a need to read a table and extract records that matches a criteria (dough!, not that simple). Then I put the record in a text message and send them out via email. Works OK, but the problem is when I have multiple records for one entry. Since SQL doesn't have arrays "per say", I need to load the records on a "temporary array" then assembly the message at the end to include the records. Any suggestions how to "emulate" an array on a stored procedure (SQL), not sure what direction to follow. thanks
View 2 Replies
View Related
Mar 11, 2008
Hi, I have source data in encrypted format. How should i handle it in SSIS?
I have found no information for such situation.
Anybody have any idea about it.
Bhakti
View 2 Replies
View Related
Oct 3, 2007
Hi,
i have a package that uses a ForEach loop component to import flat files, and uses an OLE DB Destination component to insert the data into some staging tables (using table fast load with a max insert commit of 1000 rows), the biggest individual table import would be circa 5000 rows. At the end of each file import a stored proc is called to transfer the data into production tables, then the next file is imported.
Periodically (when importing more than 5 or 6 files) the process fails with the error message:
The transaction log for database 'blah' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
This occurs when committing the data to staging. I do not start any transactions during this process, and the staging tables are cleaned out by truncating them, so i'm not sure exactly what is causing the log file to fill up (i'm not a DBA).
I know this is not specifically a SSIS problem, but can anyone give me a suggestion about the best way to handle the log file during an SSIS import? Should i execute a DBCC SHRINKFILE before each flat file is imported? Is there some other approach i should be taking to either insert the data or to move it from staging to production?
Thanks!
View 3 Replies
View Related
Jun 21, 2001
I'm trying to convert a column from a varchar(14) to float, some of the rows contain some non-numeric characters. I've tried to write a cursor to step through the records, run cast(col1 as float) on each record, then use if @@error <> 0 to capture an error but it doesn't work. It steps through fine but when the cast command fails it ends the execution of the script with "Error converting data type varchar to float."
How can i capture this error without having the script fail? Is there another way to get this done?
Thanks,
Jim
View 1 Replies
View Related
Nov 23, 2005
Hi guys,Got a problem now :( please help...now we got a project handling records saved in a table in a sql2000(will upgraded to 2005 soon) server. every month around a millionrecords will be inserted.now user raised a request, that is, once criterios are matched, theproject should do some backend handle, for example, ifSELECT colID, fieldC, fieldD FROM dataTable WHERE fieldA ='fieldA_valueB'returns some recordset, for each @fieldC, @fieldD we shall do someback-end trick, maybeUPDATE dataTable SET fieldE = 'fieldE_valueF' WHERE fieldC = @fieldCAND fieldD = @fieldDlet's say such a rule is named as rule01. Hope I'm expressing theproblem clearly?my questoin is, shall we do this in Database side, using triggers, orby informing our .net project to do it?1. since the records are coming around 1 millison per month, how can wehandling the performance issue?2. now the rules are still somewhat simple, seems at least we could doit by EXEC or SP_EXECUTESQL. but rules may turn quite complex, forexample, audit log, or more complex issues, shall we do it in .netprogram? but how can a trigger in SQL database informing a .netprogram, or webservice, or windows service? by executeing an executableconsole program?3. user may raise more and more rules, how could we provied a flexiblesolution? i mean we're trying to build it less hard-coded. seems in sqldatabase EXEC or SP_EXECUTESQL are still somewhat flexible, while in..net to do something like eval() in javascript or EXEC in sql server isa little bit troublesome. but, put all these bussiness logic in storedprocedure sounds a little bit weired.guys, hope i have made myself clear. any suggestion? Thanks very mcuhyours,athos.
View 7 Replies
View Related
Mar 28, 2007
Hi, Ive got a report using a List item that is vertically displaying the columns from a table. The problem I run into, is that some of the fields in this table contain large blocks of text where the users have entered comments and such.
I am using Textboxes to display this data.
So my report will look something like
-----
Field label 1 Field value 1
Field label 2 Field value 2
Field label 3
<white space>
<page break>
Field value 3 ---> this is a big block of text
Field label 4 Field value 4
etc
------
It appears as though the report attempts to keep the contents of each textbox together even if that means breaking onto an entirely new page to do this. I would prefer for the data to flow more natrually instead where the page breaks in the middle of the data being displayed should it be too large to fit on the page it started on.
-----
Field label 1 Field value 1
Field label 2 Field value 2
Field label 3 Field value 3 --- As much as can fit on this page
<page break>
Field value 3 ---> remaining data that broke over the page
Field label 4 Field value 4
etc
------
Any suggestions would be apprecaited.
View 3 Replies
View Related
Apr 11, 2008
hello All,
I have a flat file which has a column (length of 24) . Whenever I have that column more the 24 char length, my package failed. I would like to know if there is any way if I can continue my package in this situation and send my bad row to the output table?
Any help will be appreciated.
Thanks
View 2 Replies
View Related
Jun 23, 2006
Hopefully this is an easy question:
Inside of a for each loop (looping through an ADO record set of objects to import) I have a data flow task (along with many other processes).... if the dataflow task suceeds I log success in a table. If it errors I want it to fail the dataflow task (which will fire off my Event Handler for that data flow and log the failure, email etc) BUT I want it to continue the loop - I can't seem to figure out how to get the data flow object not to fail the whole loop. If any other objects inside the foreach, other than the data flow, fail I would like the whole loop to fail. Also if possible (but not a requirement) I would like it to have a threshold where if the data flow fails X variable times it will fail the package.
I am having difficulty how to not fail the loop when the import data fails..... just looking for a simple "on error next" type logic for that specific object in the foreach but not the rest. Thanks in advance for the help/advice.
View 4 Replies
View Related
Sep 11, 2007
I am trying to execute a SP like below in OLEDB source in data flow... and this statement include the insert stament ( row by row transaction).. I would like to creat an error hadling logic so that if the trasaction fail to insert the row then ignore that particular row then, move to the next row without stopping the whole process.. how can i do this?
exec usp_Inert_Registration_Episodes_Assessments
@Unique_ID=?,
@Gender_Cd=?,
@Birth_Date=?,
@Race_Ind=?,
@Ethnicity_Cd=?,
@Registration_Dt=? ,
--
--@Object_Key
View 16 Replies
View Related
May 16, 2014
We have a wrapper procedure that i am looking at replacing shortly. Basic Layout is something like like
Create proc SP_Wrapper
as
SP_LogProc 'proc1','Start'
Exec proc1
SP_LogProc 'proc1','End'
SP_LogProc 'proc2','Start'
Exec proc2
SP_LogProc 'proc2','End'
SP_LogProc 'proc3','Start'
Exec proc3
SP_LogProc 'proc3','End'
....
You get the idea. So logproc just logs that the procedure has started or stopped to a table so we can monitor it easily.
The procedure is then run by an agent job every morning. This morning we had a little bit of an odd one. In the example above we effectively got to 'proc3' being started (as it was logged). However there was then an error of String or binary data would be truncated (severity 16 i believe). However when proc3 was then manually run it worked (the data was unchanged). Then going back proc2 works fine, and its actually proc1 that has the error.
I have looked through the procs in question (and the wrapper) and cant find any error handling that is relevent (there one try and catch block completely separate at the end of the wrapper procedure for a small routine).
View 5 Replies
View Related
May 22, 2008
Hi All,
I have a requirement here to import data from XML file to SQL Database. The XML schema contains of various elements and one of the element is recursive ie. Parameter node contains parameter node within it and it can have n number of iterations. I have given the sample schema below:
<xs:element minOccurs="0" name="Parameter">
<xs:complexType>
<xs: sequence>
<xs:element minOccurs="0" name="ID" type="xs: string" />
<xs:element minOccurs="0" name="Description" type="xs: string" />
<xs:element minOccurs="0" name="Type" type="xs: string" />
<xs:element minOccurs="0" name="Parameter">
<xs:complexType>
<xs: sequence>
<xs:element minOccurs="0" name="ID" type="xs: string" />
<xs:element minOccurs="0" name="Description" type="xs: string" />
<xs:element minOccurs="0" name="Type" type="xs: string" />
<xs:element minOccurs="0" name="Parameter">
<xs:complexType>
<xs: sequence>
<xs:element minOccurs="0" name="ID" type="xs: string" />
<xs:element minOccurs="0" name="Description" type="xs: string" />
<xs:element minOccurs="0" name="Type" type="xs: string" />
</xs: sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="Parameter">
...............
</xs: sequence>
</xs:complexType>
</xs:element>
</xs: sequence>
</xs:complexType>
</xs:element>
But all the nodes contain the data which has to be imported to a single table dbo.Parameters. I cannot use Union ALL since i dont know how many iterations I will have in the file. Is there any way to do this operation in Data Flow Task using XML Source? Can anyone help me on this?
Thanks,
Dhileep
View 1 Replies
View Related
Jun 16, 2015
We have created SSIS package to load a text file into a table. Source system shares 10 text files and recently they stopped generating data for one of the text file (comping empty), after few months they will start generating the data for the empty file batch processing.Â
The Issue here is Data Flow task is getting failed while loading empty text file into table. How to handle this empty file load issue in SSIS package.
View 3 Replies
View Related
Jun 26, 2007
Hi,
After reading this helpful blog entry:
http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx
I think this might be a good place to ask the following question.
I am writing the error handling code for my data access layer for a web application. I am using the Enterprise Library Data Access Application Block. Although this supports generic database connections, I realized that I need to handle errors specific to each database type. Microsoft SQL is the only database type I am using for now, so I am using a try...catch (SqlException e).
In testing my code, I intentionally changed the instance name in web.config to a name that does not exist. I get the very popular error 26 - Error Locating Server/Instance Specified. This is returned as a SqlException, but the SqlError.Number property is set to -1.
Am I getting "-1" because the provider hasn't actually connected to SQL yet, so it doesn't have an actual SQL error number?
Can I assume that (SqlError.Number == -1) is always a fatal, provider-level connection exception?
Will the provider ever use another SqlError.Number of its own? Or do all numbers besides -1 come from the SQL sysmessages table?.
Is there a comprehensive list of what exceptions might be raised by the SqlClient provider, including #26?
The reason for all the questions is that in a web application, I want to prevent the end-user from seeing the "real" exception if it has to do with configuration errors. However, maybe there are other errors that the user should see and handle? It's hard to know without a full list of SqlClient provider errors, along with the SqlError.Number that each error maps to.
Thanks and regards,
Mark
View 6 Replies
View Related
Apr 21, 2006
1) We are writing a custome Source component for Oracle with OCI calls, Could some one please let me know how to Enable Error Handling for the Same,
2) Is it possible to write Custome Error Handeling Component for SSIS? if yes could you please help me on how to write it.
Thanks in advance.
View 1 Replies
View Related
Feb 25, 2006
Hi,
I am executing script like this. How to check for the errors if "master..xp_cmdshell @bcpCommand" fails. Is there any way to verify that BCP is completed successfully
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
SET @FileName = 'E:TestBCPOut.txt'
SET @bcpCommand = 'bcp "SELECT * FROM pubs1..authors ORDER BY au_lname" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -c -U -P'
EXEC master..xp_cmdshell @bcpCommand
Thanks in Advance,
View 10 Replies
View Related
Sep 7, 2007
it gives error while calling a sql stored procedure as "INPUT STRING WAS NOT IN A CORRECT FORMAT". I am providing the code here.public void get_issid(string cse_email, string tech_email, string subject, string issue_details, string response, string solv_date, out int issid)
{
// Establish ConnectionSqlConnection oConnection = GetConnection();
// build the commandSqlCommand oCommand = new SqlCommand("get_issid", oConnection);
oCommand.CommandType = CommandType.StoredProcedure;
// ParametersSqlParameter paracse_email = new SqlParameter("@cse_email", SqlDbType.VarChar, 50);
paracse_email.Value =cse_email;
oCommand.Parameters.Add(paracse_email);
SqlParameter paratech_email = new SqlParameter("@tech_email", SqlDbType.VarChar,50);
paratech_email.Value = cse_email;
oCommand.Parameters.Add(paratech_email);SqlParameter parasubject = new SqlParameter("@subject", SqlDbType.VarChar, 50);
parasubject.Value = subject;
oCommand.Parameters.Add(parasubject);SqlParameter paraissue_details = new SqlParameter("@issue_details", SqlDbType.VarChar, 500);
paraissue_details.Value = issue_details;
oCommand.Parameters.Add(paraissue_details);SqlParameter pararesponse = new SqlParameter("@response", SqlDbType.VarChar, 500);
pararesponse.Value = response;
oCommand.Parameters.Add(pararesponse);SqlParameter parasolv_date = new SqlParameter("@solv_date", SqlDbType.DateTime);
parasolv_date.Value = solv_date;
oCommand.Parameters.Add(parasolv_date);SqlParameter paraissid = new SqlParameter("@issid", SqlDbType.Int);paraissid.Direction = ParameterDirection.Output;
oCommand.Parameters.Add(paraissid);
try
{
oConnection.Open();
oCommand.ExecuteNonQuery();issid = int.Parse(paraissid.Value.ToString());
}catch (Exception oException)
{throw oException;
}
finally
{
oConnection.Close();
}
}
the stored procedure is:
create proc [dbo].[get_issid](@tech_email varchar(50), @cse_email varchar(50),@subject varchar(50),@issue_details varchar(500),@response varchar(500),@solv_date datetime, @issid int output)
as
select @issid=tech_response.issue_id from tech_response,issue_details where tech_response.tech_email=@tech_email and tech_response.cse_email=@cse_email and tech_response.subject=@subject and tech_response.issue_details=@issue_details and response=@response and solv_date=@solv_date and tech_response.issue_id=issue_details.issue_id
requested to help in this
View 2 Replies
View Related
Nov 14, 2007
I'm unsure how to handle an SQL Exception correctly when the database is unavailable/offline.
I have my aspx file with the C# code-behind, but all of the SQL stuff is done in a separate code file in the App_Code directory.E.g.
CODE-BEHINDDatabaseModifier.deleteUser(username);
DATABASEMODIFIER.cspublic static void deleteUser(string username){ SqlConnection conn = SqlLogin.SqlConnect; SqlCommand command = new SqlCommand("DELETE FROM <table> WHERE Username = '" + username + "'", conn); conn.Open() command.ExecuteNonQuery(); conn.Close()}
Now, that code works perfectly, however if the database I'm connecting to is offline, an SQLException is thrown and because the SQL is handled in my DatabaseModifier class, I'm not sure how to handle it correctly.If I use a Try/Catch block in my code-behind, it doesn't get thrown because the error occurs in my DatabaseModifier class. If I use a Try/Catch block in my DatabaseModifier class, what can I put in the catch block that will inform the user of the database being offline and/or how can I perform a url redirection?
Any help is greatly appreciated.
View 3 Replies
View Related
Apr 16, 2008
Hi there,
Can anyone help me in catching @@error value.
I have a stored procedure which return @@error value, I need to read that in my dataaccesslayer and act according to it.
how do I catch the return value from stored procedure in my dataaccesslayer.
if I am not wrong @@error return a bool value
Thanks in advance.
View 1 Replies
View Related
May 1, 2008
dear friends i am writng a store procedure to insert into a two table .table 1 data inserted but when i inserting into a table2 it have some error at that time table 1 data also want to delete.give suggestions
View 3 Replies
View Related
May 7, 2008
Hi All, Please help me out how to implement the locking in below scenarioReq - There are two tables Table1 & Table2 If I will insert in table1 then related data fields will be auto updated in table2 , similarly based on the data in table2 table1 data needs to be updated. Now the sync of table1 & table2 is working fine.My prob is we are handling the updation/insertion from the UI screens . Two separate screen for each table. When we have multiple user accessing the screens say - User1 updates table1 and User2 updates table2 then we need to implement the locking so that at one time one screen will allow updation in the table1 and hence table2.The other screen shouldnt allow updation in table2 and hence in table1.This is very common locking functionality ...but am not getting any way to implement it , Please advise.Srain.
View 1 Replies
View Related
Mar 25, 2006
Hello to all,
On my webPage I have Used one SQLDataSource to access DataBase. Now whenever some error occures it shows error page by default. I am not able to catch Errors and tackle in my way...
Furthermore in this new structure of accessing DataBase even I do not know where to write Try... Catch...
View 1 Replies
View Related
Mar 14, 2000
I am writing a stored procedure that loads transaction logs to a database and I am having trouble trapping meaningful error messages. When ever the load fails, it gives me two error messages, the first one is meaningful and the second one just states that the load ended abnormally. Unforunately, when I capture the error using @@error after the load statement, it is the second error message that I am getting.
Is there anyway to trap the first error message as well?
Thanks
Angela
View 1 Replies
View Related
Jan 21, 1999
I currently have defined a source server, a transformation, and a destination server using DTS. How and where do I create an error routine that would capture any and all errors that would occur. This would include connection error, transformation errors etc. I know the errors can be written out to a text file but I would like them written to a table on my destination server.
The DTS package will be called from an external program using the xp_cmdshell extended stored procedure. We are using it this way as a flag so if one transformation fails it will return a 1. If all are successful it will return a O. These will be our flags to check the errors table in the destination server.
Could someone tell me where this code is to go and what the code will look like. Samples would be the most help.
Thanks
Tom
View 1 Replies
View Related
May 21, 2004
is there any way to do sometype of Error handling with in a Stored Proc? Example, If I have a deadlock, can I trap that error and execute other sql code or will always simply kill the stored proc?
View 1 Replies
View Related
Dec 12, 2006
I have a composite unique key on the audit_hub table that includes TimeStamp, UID, Type, Mailbox all as part of the unique key.
I’m trying to do inserts, and know that in some cases I will violate unique index.
I’m using stored procedure, and want to handle the error gracefully there – just move on.
Reading from text-based log files via .vbs and get back the nasty popup window.
Looks like I have it right but obviously I don’t considering how it’s (not) working.
I thought error handling would be the simplest way to avoid dupe records. Might be necessary to run the same log in more than once, and only want to add the new records since the last insert session.
Here’s what I have. It obviously simply halts and complains immediately after the insert attempt.
ALTER procedure eml_HubInsert
@TimeStamp bigint
,@UID varchar(255)
,@Type varchar(255)
,@MailBox varchar(255)
,@ServerID varchar(50)
as
insert into audit_hub (TimeStamp,UID,Type,Mailbox,ServerID)
values (@TimeStamp,@UID,@Type,@MailBox,@ServerID)
if @@ERROR = 2601
begin
return 0
end
View 2 Replies
View Related
Jul 25, 1998
Hallo... and sorry about my english
1 question:
What Error-Handling msut i mplement in a trigger, who is updating permanently two tables
in a database (with the follow commands: insert, delete and update) ???
thank you !
View 1 Replies
View Related
Apr 18, 1999
Hi,
Im Nithyananda working on a project with SQL server 6.0
Im having a stored procedure which inserts into a table .
If I violate the primary key constraint on the table , I get a 2627 error.
I would like to replace this error with my own.Could u tell me how to do it?
Im an ORACLE guy and relatively new to SQL server.
Is it possible for me to propogate the same error message to any front end?
In my case ,Im using VC++ as front end.
Could someone also tell me the way of calling a SQL stored procedure from
VC++?
Regards
Nithyananda
View 1 Replies
View Related
Dec 5, 2005
I have some stored procedures that insert information from a third party plugin that hooks into our database (so I can't do any client side validiation). The problem I am encountering is that some of the fields I use need to be in DateTime format. In order for SQL SERVER 2000 to be able to insert this field, the Date has to be in the correct syntax or an error is thrown.
Is there any way to do something like this in SQL SERVER 2000
Code:
DELCARE @SomeDate DateTime
Try
Set @SomeDate = CONVERT(DateTime, Parameter1, 101)
Catch
return custom error describing what field is formatted
wrong and exit stored procedure
END
View 3 Replies
View Related
Mar 3, 2005
I am running some bcp copies through a Sql job. I am copying 35 tables in individual steps. However, sometimes the bcp step fails to copy the data, and I want the step to fail if the data is not copied properly. Is that possible? If so, how? Any help is greatly appreciated.
View 2 Replies
View Related
