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.
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.
Here is my sample data
Input text file:
"Name" "25" "00/00/00"
When i store the data in the database it get's stored with " " just the way it looks above
Expected output:I want to remove " " surrounding data.How do i remove???
eg: Name 25 00/00/00
Please let me know
thanks
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've read the other posts related to this issue, but I'm just REALLY confused as to whats happening in my case. Like everyone else it was working fine in SQL 2000 but now in SQL 2005 there is an issue. I'm calling a stored procedure with parameters defined like this:
@action varchar(10),
@GLTransactionID int = NULL OUTPUT ,
@GLBatchID int = NULL ,
@GLAccountID int = NULL ,
@CurrencyID int = NULL ,
@LocalDebit decimal(28, 13) = NULL ,
@LocalCredit decimal(28, 13) = NULL ,
@BaseDebit decimal(28, 13) = NULL ,
@BaseCredit decimal(28, 13) = NULL ,
@TransID int =NULL,
@Description varchar(255) = NULL
I am calling this proc from VS.NET 2003 using the .Net SqlClient Data Povider (C#). I'm setting the values of the parameters like this:
cm.Parameters.Add("@action", "insert");
cm.Parameters.Add("@GLBatchID", _gLBatchID.DBValue);
cm.Parameters.Add("@GLAccountID", _gLAccountID.DBValue);
cm.Parameters.Add("@CurrencyID", _currencyID.DBValue);
cm.Parameters.Add("@LocalDebit", _localDebit.DBValue);
cm.Parameters.Add("@LocalCredit", _localCredit.DBValue);
cm.Parameters.Add("@BaseDebit", _baseDebit.DBValue);
cm.Parameters.Add("@BaseCredit", _baseCredit.DBValue);
cm.Parameters.Add("@TransID", _transID.DBValue);
cm.Parameters.Add("@Description", _description.DBValue);
When I execute the call to the stored proc I get this:
"The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 8 ("@BaseDebit"): The supplied value is not a valid instance of data type numeric. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision."
Using the VS.NET command window I then inspect that parameter to see what the heck is going on and get this:
?cm.Parameters["@BaseDebit"].SqlDbType
Decimal
?cm.Parameters["@BaseDebit"].Precision
0
?cm.Parameters["@BaseDebit"].Scale
22
?cm.Parameters["@BaseDebit"].DbType
Decimal
?cm.Parameters["@BaseDebit"].Value
1000000
[System.Decimal]: 1000000
So I set a decmial parameter to 1,000,000, that parameter in the DB is defined as decimal(28,13) so should fit no problem, but it seems the Sql data provider is confused and thinks 1,000,000 is decimal (0,22)???
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!
How would I go about checking incoming e-mails? For example, on a certain e-mail address, I would get e-mails formatted in a certain way. According to the response, some scripts need to run/ some sql tables updates etc. How can one do this in (ASP) .NET with SQL Server? Anyone did this kind of stuff before?
View 3 Replies View RelatedI would like to check if the incoming date is valid date and i would also like to check if the date exists in my database.
i am transfering data from a flat file so all the data is string data.
How would i assign the datatype to the incoming columns as they exists in the sql table.Because it would better when i try to compare my incoming columns with the once in my database.
Here when i have a lookup transform and try to map one of my columns with string datatype to a column in my sql table of datatype date time i get a datatype mismatch error. How do i need to check if its date time...and how can i check if the incoming date is valid date.
please let me know
are there any tasks in SSIS that watches a folder and waits for incoming files?
View 9 Replies View Related
ok, so sp_processmail is deprecated and should no longer be used in SS2005.
how do you process and execute a SS2005 query using an incoming email??
can it even be done in SS2005??
thanks for any ideas,
chester
Is there a way to check if duplicates exists in the incoming textfiles????
Here's the senario:
View 8 Replies View RelatedI 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 RelatedOk, I'm not quite sure how to approach this one. This is a VB.NET console app in which I want to capture each row and throw it into a table. The reason being, they want a report on what was processed...which I'll be able to do easily in Reporting Services 2005 once this crap is in a table where it should be.
1) What should I use to do this, dataset? I want to use stored procedures also, not inline SQL
Function here takes an incoming file, and splits it up into separate files. I want to insert each row that is succesfully split
Public Sub ProcessFiles(ByVal sIncomingfile As String, ByVal sOutputDirectory As String)
If sIncomingfile <> "" And sOutputDirectory <> "" Then
Dim f As New Security.Permissions.FileIOPermission(Security.Permissions.PermissionState.None) f.AllLocalFiles = Security.Permissions.FileIOPermissionAccess.Read
Dim file As New IO.FileInfo(sIncomingfile) Dim filefs As IO.FileStream = Nothing If file.Exists Then Try filefs = New IO.FileStream(file.FullName, IO.FileMode.Open) 'Place: 1 Catch ex As Exception SendEmail("Incoming .mnt or .naf Filename Invalid or not found", "Place: 1") Application.Exit() End Try End If
Dim reader As New IO.StreamReader(filefs) Dim counter As Integer = 0
Dim CurrentFS As IO.FileStream Dim CurrentWriter As IO.StreamWriter Dim extension As String = IO.Path.GetExtension(file.FullName)
If extension = ".mnt" Then While Not reader.Peek < 0 Dim Line As String = reader.ReadLine If IsNumeric(Line.Substring(0, 1)) Then Dim Parts() As String = Line.Split(" "c) ' split row into parts If Parts(0).Length = 8 Then ' if first part is 8 then know we hit another header so cut and then write to file counter += 1 If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close() CurrentFS = New IO.FileStream(IO.Path.Combine(IO.Path.GetDirectoryName(sOutputDirectory), Line.Substring(59, 4) & "[" & counter.ToString & "]" & Now.ToString("MM-dd-yyyy") & IO.Path.GetExtension(file.FullName)), IO.FileMode.Create) CurrentWriter = New IO.StreamWriter(CurrentFS) End If
If Not CurrentWriter Is Nothing Then CurrentWriter.WriteLine(Line) End If
End If End While
If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()
MoveFilesFTP(sOutputDirectory, "mnt")
ElseIf extension = ".naf" Then While Not reader.Peek < 0 Dim Line As String = reader.ReadLine If Not IsNumeric(Line.Substring(0, 1)) Then ' if first part is not a number, then we know it's a header so split the file counter += 1 If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close() CurrentFS = New IO.FileStream(IO.Path.Combine(IO.Path.GetDirectoryName(sOutputDirectory), Line.Substring(6, 4) & "[" & counter.ToString & "]" & Now.ToString("MM-dd-yyyy") & IO.Path.GetExtension(file.FullName)), IO.FileMode.Create) CurrentWriter = New IO.StreamWriter(CurrentFS) End If
If Not CurrentWriter Is Nothing Then CurrentWriter.WriteLine(Line) End If
End While
If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()
MoveFilesFTP(sOutputDirectory, "naf") End If Else 'input file not valid SendEmail("Incoming .mnt or .naf Filename Invalid", "Place: 1") End If End Sub
This is on SQL Server 2008. Please find a detailed description and the file of the data, that I am working on.
Requirements:
1.
If
'Channel'
is
not
equal to "Omnibus"
where
the 'Trans Description'is
equal to "Purchase"
and
"Redemption"
for
one purchase and
one redemption that match on 'System'
,
'Account TA Number'
,
'Product Name'
,
'Settled Date'
,
and
where
the 'Trade Amount'
of the purchase and
redemption is
within 5%,
then
display those set of records.
2.
If
deemed wash trades,
allow user to update the purchase and
redemption pair 'Trans Description'
from
"Purchase"
to "Exchange In"
and
'Trans Description'
from
"Redemption"
with
"Exchange out"
System Channel Dealer Name Firm Name Product Cusip Product Name Product Share Class Trade ID Settled Date Account TA Number Trans Description  Trade AmountÂ
SCHWABPORTAL US - ASG MILLIMAN MILLIMAN 64128K777 Strategic Income Fund A 29806259 30-Jan-15 000BY00F2RW Redemption  $     25,68,458.15
[Code] .....
I have below records coming in from source files
ProdName Amount TranType
P1 100 A
P1 100 S
P2 200 A
P2 205 S
In case the ProdName is same, and Amount = or (within +/- 5%) of Amount, I have to update the TranType column as IN/OUT respectively as shown below in the tables.
I am okay with using 2 different tables if needed as in the records comes in one table and then i can reference that table to upload the values in another.
ProdName Amount TranType
P1 100 IN
P1 100 OUT
P2 200 IN
P2 205 OUT
The order of the records coming in can be different order, they need not be subsequent.
This is happening in SQl Server 2008.
I 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