Are there methods to redirect incoming data to different tables on the fly in the situation when application feeds data to the table via ODBC and I'm unable to alter ODBC or application settings? Somthing on the server side?
Kind regards,
A.
Are there methods to redirect incoming data to different tables on the fly in the situation when application feeds data to the table via ODBC and I'm unable to alter ODBC or application settings?
Kind regards,
A.
I have a table that among other holds volume data. I need to calculate something called Intelligent Volume based on set of rules.
After all rules were followed and I still find data that does not belong to any of the rules this data is bad and needs to be reported on (can not be discarded)
I wanted to do this is sql task (insert intel. volume) and I am using Cursor to loop through all the data and all rules.
How do I redirect this data to a file so we can report on those records?
Thanks
Hi, all,
I have this SSIS data flow ( Flat file to sql server) that I want to add a step to redirect any "bad" data instead of fail out.
I had the red arrow hocked up to a sql new table to dump the bad data, but the flow still failed.
Here is the first error, and I knew what was wrong. A description field in that line has pipe(|) character in it, which also happen to be the column delimiter in this case.
[Flat File Source [1]] Error: Data conversion failed. The data conversion for column "Column 22" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
I knew if I fixed the data, every thing will be fine, but I just want to use this redirect feature of SSIS. Is there a place where I can turn off validation, or do something to make it work?
Thanks!
If the system not able to convert the data from one data type(string,int) to another data type then i need to move the reocord into my error_table.
How to redirect the bad data into another table, while extracting the flat file data ?
Bad Data means if the system not able to convert the data from one data type to another data type.
Say €œX2R322€? data can€™t convert as DATE data type but "070899" can be converted as DATE data type.
Thank you very much.
I was wondering if there was a way to redirect an insert to another column...
Example:
Original Insert Statement:
INSERT INTO [table] ([columnA], [columnB])
SELECT '2015-01-01 00:00:00', 99.99
We have changed [columnB] from a decimal(19,9) to a computed column. So instead, we added another column [ColumnC] to take [ColumnB]'s insert data. I thought we could've used a trigger instead of insert, but that fails with the message "cannot be modified because it is either a computed column or is the result of a UNION operator".
This is the trigger I was using:
CREATE TRIGGER [Trigger] ON [table]
INSTEAD OF INSERT
AS
INSERT INTO [table] ([columnA], [columnC])
SELECT [dataA], [dataB]
FROM Inserted
We are working on a DataWarehouse app. The DW has been loaded wiith transactional data from the start of September. and we want refresh the DW with a full load from the original source. This full load wil consist largely of the same records that we loaded initially in the DW but some records will be new and others will have changed.
During the load I want to direct input records NOT already in the DW to a "mods" table and ignore those input records that alreayd exist in the DW. Can SSIS help out with this task?
TIA,
Barkingdog
I'm having problems designing a package to attempt to execute a fast load data transfer but failback to regular speed with error redirection in the event of an error.
The way I designed this was to add one data flow task to my package called "DFT FASTLOAD". The data flow copies a table SRC to another table DEST in the same SQL Server database. In the error handler for the data flow task I copied the original data flow task and changed the name to "DFT REGULARLOAD with Error redirection". In this data flow task I did not use fast load and addtionally redirected errors to a text file.
In the Data Flow Task "DFT FASTLOAD". I am copying from a varchar source field(with non-date strings) to a datetime destination field to force errors. However the Data Flow Task "DFT REGULARLOAD with Error redirection" never seems to start transferring data from source to destination. The data Flow Task "DFT REGULARLOAD with Error redirection" turns yellow (after the error occurs in "DFT FASTLOAD"), but no data is being transferred). It seems like it hangs.
Do I need to increase the MaximumError Count or something? The data flow task "DFT FASTLOAD" does not turn red when the error occurs it just remains yellow, so i assume I'm on the right track since it seems the error is caught.
I have added screenshots ... hopefully these screenshots will clarify my problem.
DESIGN:
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD1.jpg
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD2.jpg
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD3.jpg
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD4.jpg
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD5.jpg
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD6.jpg
RUNTIME:
http://i256.photobucket.com/albums/hh179/abzbank/RUN_FASTLOAD7.jpg
http://i256.photobucket.com/albums/hh179/abzbank/RUN_FASTLOAD8.jpg
http://i256.photobucket.com/albums/hh179/abzbank/RUN_FASTLOAD9.jpg
http://i256.photobucket.com/albums/hh179/abzbank/RUN_FASTLOAD10.jpg
http://i256.photobucket.com/albums/hh179/abzbank/RUN_FASTLOAD11.jpg
I can provide more details if needed... but really this is just a basic test.
Any assistance would be appreciated!
I need to copy data from warehouse tables to master tables of different SQL instances. Refresh need to done once in an hour. What is the best way to do this? SQL agent jobs or SSIS packages?
View 3 Replies View RelatedI have a package that works fine when taking data from Oracle and loading it into SQL Server using Ole DB source and Ole DB destination tasks.
However I have a bad record in my source now. Date value for a specific field is less than year 1753. So everytime I run with that record present in the source, the load fails when it hits that record.
Whats the best to deal with this? I tried redirecting the bad record to a file but it doesnt work as SSIS doesnt let you create error output for ole db destination task, so it can sent the record into a flat file or something else for later review.
suggestions on how to trap the bad record and redirect it to a text file? and what would the dataflow look like?
thanks
This is my stored procedure .It is working fine .I want to capture the output in a print statement .Can anyone help me please .
alter proc r (@id INT)
as
BEGIN
DECLARE @input VARCHAR(800)
DECLARE @c_input INT
DECLARE @i_Input INT
DECLARE @input_left VARCHAR(800)
DECLARE @delimiter CHAR(1)
select @delimiter = ','
DECLARE @in VARCHAR(800)
DECLARE @list VARCHAR(800)
declare @list2 VARCHAR(800)
SET @input = 'db2,oracle,sybase'
select @c_input = (select dbo.Fx_CharCount(@delimiter,@input))
set @c_input = @c_input + 1
while @c_input > 0
BEGIN
select @i_input = charindex(@Delimiter,@input)
if @i_input != 0
BEGIN
select @input_left = left(@input, @i_input - 1)
END
else
select @input_left = @input
select @in = '''' + @input_left + ''''
select @list = ISNULL(@list + ',', '') + @in
select @input = right(@input ,(len(@input) - @i_input))
SET @c_input = @c_input -1
if @c_input = 0 or @input = @input_left
break
end
Print @list
EXECUTE ('SELECT Label FROM systemtype WHERE Label Not IN (' + @list + ')')
END
my actual task is like this
My input is a list of values seperated by commas
now my output should be list of values not in the table joined by comma
eg : if my table consists of list of all databases like
db2
oracle,
sybase,
mssql,
mysql,
myinput would be like this db2,sybase,oracle
my output should be mssql,mysql
how to get that
?
Any suggestions
Hi i get the above error when moving from one page to another. the following code is used. Can u help
Response.Redirect("Applicationform2.aspx?Id="+StudentID.Text+"&nam="+Name.Text+"&surNa="+ SrName.Text +"&DOB="+Ddate+ "&addr="+address.Text+"&pCode="+postal.Text+"&Cntry="+country.Text+"&email="+email.Text+"&ph="+phone.Text);
i dont understand the problem, can u help resolve the problem
Hello friends
i m using Sqlserver 2000 and i wish to know that ,
Can i redirect the output of the query into a text/xls/html file !
If possible then please help me !!!!!
Thanks
Hi,I have a DTS package (SQL2k, sp3) that I can to execute agains a newdatabase in the same server. I don't see how to easily redirect. If youchange the connection properties and clear the transformations you have toredo them one at a time (too long).If you don't data still goes to the old database.What to do?Sd
View 1 Replies View RelatedIn SSIS packages, records which do not get processed successfully can be re-directed to different destination for logging or correcting purposes. With 2 additional fields ERROR_CODE and ERROR_COLUMN appended to the dirty row values. To indicate the specific error that has occurred and on which column the error has occurred, I have certain doubts on this error reporting mechanism in SSIS packages.
The ERROR_COLUMN that is reported is not the column name but a number identifying that column uniquely. how can we at run time remap this column number to the exact column name?
Any help on this will be greatly appreciated.
Thanks,
S Suresh
create procedure usp_test (@AccountID INT)asbegin
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR
SELECT Account_ID
FROM Accounts
OPEN @getAccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @AccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
END
CLOSE @getAccountID
DEALLOCATE @getAccountIDend i get nearly ten rows in the print statement how can i assign the output to a out variable where i get all ten rows.
My requirement is something like this
I get department ,salary as input from a stored procedure .
now i have to select all the employee no's from the department table.
and based on that i have to select all the employee details from employee table whose salary is greater than given salary.
and the complete row should be passed as output parameter.
This cursor is fine
create procedure usp_proc (@dept char(10),@sal decimal (10,2),@emplist cursor varying output)
declare @empid int
DECLARE @getempid CURSOR
SET @getempid = CURSOR FOR
SELECT emp_id
FROM department where dname = @dept
OPEN @getAempid
FETCH NEXT
FROM @getempid INTO @empid
WHILE @@FETCH_STATUS = 0
BEGIN
select ename,dept,dob,doj,status,pos,sal from employee where empno = @emp_id
FETCH NEXT
FROM @getempid INTO @empid
END
CLOSE @getempid
DEALLOCATE @getempid
I am getting the complete row displayed as output .
How do i redirect the output to the declared output is my concern.
Is it possible to do a redirect to an arbitrary page in by using custom code?
eg.
Public sub myRedirect()
response.redirect(http://www.microsoft.com)
End sub
/Alex
Hi!
I have several lookups in my data flow task and for each of these I want to redirect error to one file (append data)
I created Flat File connection manager and first lookup goes fine with errors redirected to the file.
However, second error redirect that I am sending to the same file is failing.
Error I get is:
"[Flat File Destination 1 [14851]] Warning: The process cannot access the file because it is being used by another process.€?
So my goal is to have one central file where I would redirect all records that fail.
thanks
I have 2 pages. ( i want to pass information from a text box to the "certificate.aspx" database query)
page 1 certsearch.aspx
this is my script i have a label, commnad button, & textbox
If txtSearchCert.Text = "" Then
lblMsg.Text = "Please enter a certificate #"
Else
Response.Redirect("certificate.aspx" & txtSearchCert.Text)
End If
page 2 certificate.aspx
i am not sure what goes here.this is what i am trying
Request.QueryString = (txtSearchCert.text)
This is my database query on certificate.aspx page
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:imacsConn %>"
SelectCommand="SELECT * FROM [SummaryBlue] WHERE REPORTNUMBER = ?"></asp:SqlDataSource>
Hi,
I have to write a sp which takes in a input and redirect the result to a text file?
Any idea how to write it?
TIA.
Hi,
Does someone know how to spool output of a query/procedure to a file? I'm running MS SQL 2000 and will have to set up an automated job which will email this file to interested parties. Here is the procedure that I'm executing:
IF EXISTS (SELECT 1 FROM sysobjects WHERE [name] = 'sp_db_stats' AND type = 'P')
DROP PROC sp_db_stats
GO
CREATE PROCEDURE sp_db_stats
@DBName sysname = '*'
AS
DECLARE @DBStatus int,
@dbid int
DECLARE DBs CURSOR FOR
SELECT name, dbid, status
FROM master..sysdatabases
FOR READ ONLY
OPEN DBs
FETCH NEXT FROM DBs INTO @DBName, @dbid, @DBStatus
WHILE @@FETCH_STATUS = 0
BEGIN
exec master..sp_helpdb @DBName
NextDB:
FETCH NEXT FROM DBs INTO @DBName, @dbid, @DBStatus
END
CLOSE DBs
DEALLOCATE DBs
NoCursor:
RETURN
Thanks in advance!
-Alla
When I click the link to download the report in web page, it can pop up a window so that let me "open" or "save". After I open or save this (.pdf) file, I want to auto redirect to another web page. Can I do that? Here is my code:
Response.AddHeader("Content-Disposition","attachment;filename="" + getExportFilename() + """);
Response.ContentType = "application/octet-stream";
byte[] result = rs.Render(TLOWebRptFunc.GetSaveReportPath(this.Context), TLOWebRptFunc.GetReportType(this.Context), null, "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>", wparam, credentials, null, out encoding, out mineType, out wparam, out warnings, out streamIDs);
Response.BinaryWrite(result);
Response.End();
Occassionally we have the need to redirect the Reporting Services website so users can not access it. I have in the past disabled the Report Server application pool in IIS, but it only provides the generic "Service Unavailable" message and I end up fielding phone call all day about it. I would prefer to have it display a custom webpage that explains the service outage and when they may expect it to be available again.
So far, I have created a custom webpage name SiteMessage.html and in IIS created a new virtual directory and new website. In Reporting Services Configuration Manager, under Report Manager Virtual Directory Setting, I have changed the Website and Virtual Directory settings accordingly and received confirmation that the virtual directory was created. However, when I test the site, it still brings up the default web site.
Could somone explain how I may get the desired results listed above?
Hi there.
I have a database mirroring session running, and both principal and mirror are working fine. But when i unplug the network cable from principal server, creating a failover scenario, the client doesn´t get redirected to the mirror.
I get an error saying: "A transport-level error has ocurred when received results from the server. The specified network name is no longer available."
connection = "Data Source=SISAD;Failover Partner=Projecto-SWS1;Initial Catalog=SIERE;Persist Security Info=True;User ID=sa;Password=testing;Connection Timeout=100";
//this method is called many times by a thread, emulating some request to the server
public static void Inserir(int codAutor)
{
SqlConnection sqlConnection = new SqlConnection(connection);
SqlCommand command = new SqlCommand("insert into Autor values(@codAutor,'test')", sqlConnection);
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@codAutor", codAutor.ToString());
command.CommandTimeout = 200;
sqlConnection.Open();
command.ExecuteNonQuery();
sqlConnection.Close();
}
I think the error is because the ExecuteNonQuery() method return info about the rows affected on the server. But in this case how can i create a failover scenario with client-side redirect ?
Thanks in advance.
Hi, i have a OLE DB transformation that executes a stored procedure. The stored procedure RAISERROR when an error is caught. I have set the OLE DB transformation to redirect rows. But instead, the task turns red and fails. Did i do something wrong here ?
View 3 Replies View RelatedHi All,
My apologies if this question seems abit basic, but I'm a DBA by trade and programming .Net isn't my strong point ;)
I've enabled database mirroring on 3 SQL 2005 servers, a principal, a mirror and a witness.
Principal - SQL 2005 Enterprise Edition, SP1
Mirror - SQL 2005 Enterprise Edition, SP1
Witness - SQL 2005 Express, SP1
I've written some test code to test the mirroring but as soon as the connection is pulled from the principal, the client re-direct doesn't work and the program bombs. I'd be grateful fi someone could have a look at my code below and tell me if there's any schoolboy errors??
(NB, the user running the code is a sys admin on all 3 servers)
Thanks in advance.
Imports System.Data.SqlClient
Public Class Form1
Dim DCTADS As New SqlClient.SqlConnection
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
DCTADS.ConnectionString = "Network=dbmssocn; Server=STUD; Failover Partner=DBDRTEST; Database=mirrortest; Integrated Security=True"
DCTADS.Open()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cmd As New SqlClient.SqlCommand
cmd.CommandText = "Select fulldesc from unclproduct where internal = '20000110'"
cmd.Connection = DCTADS
Dim dr As SqlClient.SqlDataReader
dr = cmd.ExecuteReader
MsgBox(DCTADS.DataSource.ToString)
If dr.Read Then
MsgBox(dr("fulldesc").ToString)
End If
dr.Close()
End Sub
End Class
Hi,
when I'm trying to redirect a row I'm having the following error:
Error 4 Validation error. Data Flow Task: OLE DB Destination [535]: The error row disposition on "input "OLE DB Destination Input" (548)" cannot be set to redirect the row when the fast load option is turned on, and the maximum insert commit size is set to zero. PCKG_MPP.dtsx
Can somebody help me?
How to redirect output of a stored procedure to a table
View 3 Replies View RelatedWill this work with replication too. I see it with a mirror database, but could it work if I was keeping two copies of the database and wanted to transparently reroute the connection to a surviving server?
View 3 Replies View RelatedHi All,
My apologies if this question seems abit basic, but I'm a DBA by trade and programming .Net isn't my strong point ;)
I've enabled database mirroring on 3 SQL 2005 servers, a principal, a mirror and a witness.
Principal - SQL 2005 Enterprise Edition, SP1
Mirror - SQL 2005 Enterprise Edition, SP1
Witness - SQL 2005 Express, SP1
I've written some test code to test the mirroring but as soon as the connection is pulled from the principal, the client re-direct doesn't work and the program bombs. I'd be grateful fi someone could have a look at my code below and tell me if there's any schoolboy errors??
(NB, the user running the code is a sys admin on all 3 servers)
Thanks in advance.
Imports System.Data.SqlClient
Public Class Form1
Dim DCTADS As New SqlClient.SqlConnection
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
DCTADS.ConnectionString = "Network=dbmssocn; Server=STUD; Failover Partner=DBDRTEST; Database=mirrortest; Integrated Security=True"
DCTADS.Open()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cmd As New SqlClient.SqlCommand
cmd.CommandText = "Select fulldesc from unclproduct where internal = '20000110'"
cmd.Connection = DCTADS
Dim dr As SqlClient.SqlDataReader
dr = cmd.ExecuteReader
MsgBox(DCTADS.DataSource.ToString)
If dr.Read Then
MsgBox(dr("fulldesc").ToString)
End If
dr.Close()
End Sub
End Class
how to implement a redirect in SSRS? If a user goes to [URL] I want it to redirect to [URL]
View 3 Replies View RelatedThis seems like a simple task that just doesn't work. I have an XML source, and on that source, I have the Error Output configured such that the Trunctations are set to "Ignore failure", and the Errors are all set to "Redirect Row". They are then being redirected to a Row Count transformation.
To set up a test for this, I have an integer type (DT_I4 specifically) that I am populating with an alpha-numeric value. For example:
<BatchID>118a</BatchID>
However, this column prevents my XML source from loading at all, and throws the following error:
The component "XML_SRC - File" (46) failed attempting to write data to output column "BatchID" (2030) on output "Payment Error Output" (115). Input string was not in a correct format.
I checked BatchID (2030) throught the advanced properties of the XML Source, and this is defined as a Unicode text stream [DT_NTEXT] field, so I don't see why there would be a problem.
Is this a bug, or am I overlooking something?
Thanks in advance for your help.
Jerad