Hello,
I have searched the forum, and have discovered that the DTS method using IsNumeric to check for numierc values (ActiveX) is not valid in SSIS. Most of what I have seen prescribes using the script component to handle this.
So formerly, I checked to see if a column was numeric. If it was, then I needed to use the numeric value as is, or in some cases, I needed to perform a calculation on the value and use the result. If the value was not numeric, then whatever the value was needed to be changed to zero.
Here is an example of how I would use the current value, or set the value to zero:
If IsNumeric(DTSSource("Col003")) Then DTSDestination("ADepTrnx") = CLng(DTSSource("Col003")) Else DTSDestination("ADepTrnx") = 0 End If
This is an example of how I would use the current value in a calculation, or set the value to zero:
If IsNumeric(DTSSource("Col012")) Then DTSDestination("AlliStdFee") = CLng(DTSSource("Col012"))/100 Else DTSDestination("AlliStdFee") = 0 End If
Does anyone have an example of how I would handle both situations in a script component?
Has anyone attempted to perform a load test with SSIS? I am trying to saturate(100% processor utilization) a HP integrity rx4640 server with 60GB of ram and 4 processors, windows 2003 and Sql server 2005 in order to to build a sizing document?
I have been somewhat dismayed by the lack of information available relative to testing SSIS solutions. At a time when both managed code and db code are getting tooling (through VSTS) to help drive an automated testing mentality into developers everywhere - there seems to be little out there for those using SSIS. I have seen people talk about having SSIS packages that test the actual packages - there is always the manual way to write the tests in something like C# and then programmatically invoke the package and manually validate the results. Anyone out there have approaches that are working for them?
How to perform testing on SSIS packages? I need to find out how the package progress and the re-entry takes place through c#.net. Please provide me an appropriate code.
I am currently working on a project which needs to load over a 1000 xml files. The files are stored across 10 subfolders. I am using a foreach loop with a file enumerator, which is configured at the top of the folder structure and traverses the subfolders. This loops through the files, load the data and then moves the file to another folder. The package executes fine for a few 100 files but then hangs; this happens after a different number of processed files each time the package is run. While trying to resolve the problem we ran performance counters and noticed that the number open handles increased significantly just about the time Dtexec looked like it had hanged and DTexec also then started taking a lot of the cpu processing time. Has one else come across similar situations?
i am migrating data from a legacy system with a not nice front-end. as a result, i have all sorts of garbage stored on the tables.
i am trying to convert values from varchar(12) to float, but i have an error during selecting data that says that data can not be converted eventhough i am using the ISNUMERIC() function to check.
case when isNumeric( myCol01 ) = 0 then null else convert( float , myCol01 ) end
but my error occours when ISNUMERIC() encounters the value '. ' ; that is a dot with spaces after it.
2/1/2008 1 2/1/2008 1 2/1/2008 0 2/1/2008 x 2/1/2008 0
The grpMiscError can contain 0, 1 or x only. I need to sum up this column for all the zeros by a particular date. I have the following but doesn't work: SELECT SUM(CASE ISNUMERIC(grpMiscError) WHEN 0 THEN 1 ELSE 0 END)AS MiscError FROM TableA WHERE GrpDate = '2/1/2008'
I get back an answer of 1 MiscError instead of 2 What am I doing wrong here?
Now that we have a good programming model in SSIS - the question is whether to write automated unit tests for your packages, and would it generally be a good idea for packages?
Also - if yes to write tests - then where to find more informations regarding How to accomplish that?
HiHere's the problem:I need to search a postcode database by the first one or two letters.Problems occur for example when i want to search north London postcodes (N) when using:postcode LIKE @postcode + '%' As this picks up everything beginning with N, eg, NG for Nottingham, or NE for Newcastle. So i need a like statement which searches for the first one or two digits followed by a number!I've found the ISNUMERIC() function but not sure what the best way to use it with the like statement - or even if there is a better way altogether - can you use regular expressions in MSSQL?thanks
sneaky, sneaky, sneaky ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 guarantees that expression can be converted to one of these numeric types.
thanks, but which one??
numeric as far as float is concerned, is not the same thing as numeric as far as money is concerned
create table isnumerics ( id integer not null identity , txtfld varchar(11) )
insert into isnumerics (txtfld) values ( '1' ) insert into isnumerics (txtfld) values ( '937' ) insert into isnumerics (txtfld) values ( '937.0' ) insert into isnumerics (txtfld) values ( '$937' ) insert into isnumerics (txtfld) values ( '$937.00' ) insert into isnumerics (txtfld) values ( 'free' ) insert into isnumerics (txtfld) values ( '.50' ) insert into isnumerics (txtfld) values ( '1,000' ) insert into isnumerics (txtfld) values ( '' )
select id , txtfld , isnumeric(txtfld) from isnumerics
select id , txtfld , isnumeric(txtfld) , case when isnumeric(txtfld) = 1 then cast(txtfld as money) else cast(null as money) end as case1 from isnumerics
select id , txtfld , isnumeric(txtfld) , case isnumeric(txtfld) when 1 then cast(txtfld as money) else cast(null as money) end as case2 from isnumerics
select id , txtfld , isnumeric(txtfld) , case isnumeric(txtfld) when 1 then cast(txtfld as float) else cast(null as float) end as case2 from isnumerics
1111.0 29371937.0 3937.01937.0
6free0 7.5010.5 the others got "Error converting data type varchar to float"
no, there wasn't a question here, but yes, i'd love to hear your comments
I'm casting a varchar field to a decimal field using the format
CASE ISNUMERIC(GrossMktCapGbp) WHEN 1 THEN CONVERT(DECIMAL(18,6),GrossMktCapGbp) ELSE NULL end
Thinking this would ensure that any spurious rows got set to null.
However I had a problem with some values that were set to '.', it seems that isnumeric thinks these are numbers but casting them to decimal produces an error.
SELECT ISNUMERIC('.') SELECT CAST('.' AS DECIMAL(18,6))
Should I have been doing something different in my check possibly.
The above expression seems to work fine if Fields!Accreditation.Value is a number. However, if Fields!Accreditation.Value is not a number, it gives an #Error. Why is the true part evaluated when the expression is false?
Been meaning to post this for a while. It does a very limited job of only allowing [0-9], but could be extended to allow negative numbers, or numeric values that are suitable for numeric types other than INT, but avoiding the pitfalls of IsNumeric() which might allow through data not suitable for some of the numeric datatypes
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[kk_fn_UTIL_IsINT]') AND xtype IN (N'FN', N'IF', N'TF')) DROP FUNCTION dbo.kk_fn_UTIL_IsINT GO CREATE FUNCTION dbo.kk_fn_UTIL_IsINT ( -- String to be tested - Must only contain [0-9], any spaces are trimmed @strINTvarchar(8000) ) RETURNS int-- NULL = Bad INT encountered, else cleanedup INT returned /* WITH ENCRYPTION */ AS /* * kk_fn_UTIL_IsINTCheck that a String is a valid INT *SELECT dbo.kk_fn_UTIL_IsINT(MyINTColumn) *IF dbo.kk_fn_UTIL_IsINT(MyINTColumn) IS NULL ... Bad INT * * Returns: * *int valueValid integer *NULLBad parameter passed * * HISTORY: * * 30-Sep-2005 Started */ BEGIN
DECLARE@intValueint
SELECT@strINT = LTRIM(RTRIM(@strINT)), @intValue = CASE WHEN @strINT NOT LIKE '%[^0-9]%' THEN CONVERT(int, @strINT) ELSE NULL END RETURN @intValue
I would like to validate datatype using Derived Column.My data type are such as numeric(X,X),datetime,int, and varchar.How do I do this using Derived Column.Example if row does not qualify as ISNUMERIC()...throw it in ERROR table else send it to SUCCESS table.Any Idea ?
I've wrote a small query for SQL 2005 and it's doesn't seem to work.
I have a table that contains two columns (X and Y), X is an int and Y is an nvarchar(50). I've populated this table with some data where Y contains numbers and some strings (e.g. "1", "2", "foo", etc). I've then got a view which only returns the rows where Y is numeric - now, I then query this table stating I only want numbers greater than 0 (i've casted the column) but this throws an error stating "foo" can't be casted. This is strange because the view doesn't return that.
What's going on? All of this works fine in SQL 2000 but not in SQL 2005 - looks like it's looking at the underlying table rather than the view. Sample code below to help you all out: -
Create Table ============ CREATE TABLE [dbo].[tblTest]( [X] [int] NOT NULL, [nvarchar](50) NOT NULL ) ON [PRIMARY]
Insert Data =========== INSERT INTO tblTest(X, Y) VALUES(1, '1') INSERT INTO tblTest(X, Y) VALUES(1, '2') INSERT INTO tblTest(X, Y) VALUES(2, 'foo') INSERT INTO tblTest(X, Y) VALUES(2, 'bar')
Create View =========== CREATE VIEW [dbo].[vwTest] AS SELECT X, Y FROM dbo.tblTest WHERE (ISNUMERIC(Y) = 1)
Finally ======= SELECT X, Y FROM dbo.vwTest WHERE (CONVERT(int, Y) >= 0)
Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the nvarchar value 'foo' to data type int.
I have a task (Derived Column Task) and I want to write something like this :
IsNumeric(aColumnOfString) == true ? "All numbers" : "there are some characters"
Here aColumnOfString can be something like "123a5" or 12345". I do not want to simply check if the left-most character is a number or not. I want to check the entire expression and return me a TRUE or false.
A TRUE is returned if the entire expression contains ONLY numbers, and FALSE otherwise.
I read some posting using regular expression. But that is not a solution for this situation.
Hi - Please excuse me if this is really simple, but I'm fairly new to this lark.
My (made up) code is below... I'd be grateful for any pointers.
insert into [tblInvoices] (full_period, supplier_no, account_code, tran_amount, function) select full_period, supplier_no, account_code, tran_amount case when substring(account_code,1,2) = 'FY' then '-' when isNumeric(account_code) then left(account_code, 2) when not isNumeric(substring(account_code,1,2)) then left(account_code, 1) else 'oops' end as function, from tblLoadMSV900_i end
Is this even close?
I'm using a stored proc to insert the data from tblLoadMSV900_i into tblInvoices and at the same time insert some data into the function field.
In plain english I want make sure that: If the first 2 chars of account_code are 'FY' then function='-', If the first char of account_code is numeric then function=left(account_code, 2), If the the first char is not numeric (and if first two chars are not 'FY' i.e. first char could be 'F') then function=left(account_code, 1)
And there's plenty more where this came from! But if I can crack this with your help then I should have a better idea about the rest of the proc.
There is a MSSQL function that check the value. Like ISNULL(), ISDATE() and ISNUMERIC(). I don't see a function that check for decimal. If there isn't any then is there an user-defined function for it? I need to be able to validate the string value for decimal before it get assigned to a decimal datatype or T-SQL will run into an error.
SELECT uri, evFieldUri, evFieldVal , CAST(evFieldVal AS BIGINT) FROM TSEXFIELDV
[Code] ....
And it returns this error:
Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to bigint.
So, I tried again, and this worked…
SELECT uri, evFieldUri, evFieldVal,CAST(evFieldVal AS BIGINT), ISNUMERIC(evFieldVal) FROM TSEXFIELDV WHERE URI > 0 AND evFieldUri IN ( SELECT URI FROM TSEXFIELD WHERE exFieldFormat IN (1,11))
I logged out and came back and tried again, and it still worked. So then I tried…
SELECT uri, evFieldUri, evFieldVal,CAST(evFieldVal AS BIGINT) FROM TSEXFIELDV WHERE URI > 0
I have another issue. I have an excel file that I pipe through a "data conversion" task. I have set all the column data types to strings, because there's no way to know beforehand if a particular column will be number or text because the file is very non-standard (it looks more like a formatted report).
After the data conversion, I send all the rows to a script task. In the script task, I do a check on the numeric fields.
for example:
If Not IsNumeric(Row.Price) Then
Row.Price_IsNull = True
End If
However, this check fails each and every time, even if the field contains a number! I don't have this problem when using flat file sources.
So, none of my numeric fields are getting loaded to my ole db destination.
Help, is there a way around this? Or am I forced to just skip this number check altogether? I'd prefer not to.
I am using SqlServer 2005 Express Edition. I have written a stored procedure that has a transaction to make sure that it rolls back in case of failure. Is there a way to test that the transaction works. I don't now how to cause an error that would cause the transaction to roll back and raise the error with the details of the exception. Thanks, laura
hello everyone i was hoping to get some help. i have sql server2000 and iis5.1 running and im using vbscript through asp. i have a table that has two keys so that together they make a unique key. so i am trying to test my table in asp so that if there is a duplicate unique key inserted then show an error... Can anyone help me?
I want to run remote procs from a local - central proc, but don't want to crash my proc if the remote NT Server or SQL Server is not available. So, I want to test for NT and SQL availability first.
/* I find the server-dbasename name in a lookup table, and then build dynamic script */ SELECT @DBName = "<SRVRNAME~DBASENAME>" SELECT @QueryString = N'master..xp_cmdshell "ECHO >' + SUBSTRING(@DBName, 1,6) + 'pipesqlquery", no_output' EXEC @Results = sp_executesql @QueryString
SELECT @Results /* <-- testing here.... */
=====================
If the NT / SQL is running, "@Results" should = 0 (success on the ECHO to the PIPE) If the NT / SQL is NOT running, "@Results" should = 1 (error on the ECHO to the PIPE)
If I test in QA, I get the expected "@Results" values.
If I run the proc, I get "@Results" = 0 for both events, and them my proc fails on the remote proc call if the NT / SQL is NOT running.
I don't know how to write DMO in TSQL to try that. I have also tried using 'odbcping' too.
HINTS? Better way to test remote NT / SQL is NOT running?
