Casting DT_WSTR To Integers In SSIS

Jul 5, 2007

Hello, all.



Why is it that, despite what is said in the sketchy SQL Help content, it appears to be impossible to cast a string to an integer in the Expression Builder to generate a value for a variable? More specifically, why does the following expression cause an error?



(DT_UI4) (SUBSTRING(@[User::FullDataPath], LEN(@[User:ataPath]) + 1, 2))



I'm iterating over files and using the name of a given file as an ID for an operation. I simply want to grab a file name using the Foreach Loop Container and process that file, while at the same time use the name in another operation. The file name will be something like "2.txt" (full path something like "c:somethingsomething2.txt"). I can use string functions to return the file name, which is a number as a string, and it should be no problem to cast that number as a string to a number (an Int32). SQL Server 2005 help has a chart that indicates such a cast is legal.



Maybe it's a crazy thing to be doing. Maybe I have to go about this a completely different way, but casting from "2" to 2 should be possible in the Expression Builder.



Thanks for any help.

View 7 Replies


ADVERTISEMENT

Data Casting, Casting Operations

Jul 23, 2005

Does anybody know how could I calculate the new date(adding @c to @bor subtracting @b to @c), having for example a declaration like this:DECLARE @a CHAR(12)DECLARE @b DATETIMEDECLARE @c INTSET @b='3.04.04';SET @c=6and to calculate the number of days between two dates with this kindof declaration(@a-@b or @b - @a):DECLARE @a CHAR(12)DECLARE @b DATETIMEDECLARE @c INTSET @a='12.2.04';SET @b='3.04.04';Thanks in advance.

View 1 Replies View Related

DT_14 And DT_wstr

Feb 28, 2008



Hi,
In excel file, I have one column CertNum. The Sqlserver destination column is CertNum of type int. I am using one data conversion to convert excel CertNum to integer datatype. I am getting the error like Potentila loss of data, truncation occurs. So Before doing the Data Conversion I included one Derived column with the expression
ISNULL([CertNum) || [CertNum] == "" ? 0 : [CertNum]. Then here I am getting error: The DT_14 and DT_WSTR are incomaptable datatypes.
How to avoid this error?

Thanks in advance

View 11 Replies View Related

DT_WSTR To DT_STRING

Apr 14, 2008



Hello How can we convert DT_WSTR (Unicode) to DT_STR (Data string)

i cant use DATA CONVERSION transformation

Using SQL and SSIS

Please let me know

Thanks

View 4 Replies View Related

(DT_DBTIMESTAMP)(TRIM(01-01- + (DT_WSTR,4)(Year))) HELP WITH THIS

May 15, 2008



(DT_DBTIMESTAMP)(TRIM("01-01-" + (DT_WSTR,4)(Year)))


Hello I want to know what is the significance of putting DT_DBTIMESTAMP and DT_WSTR in the above line,

Thanks

View 2 Replies View Related

How To Convert DT_WSTR To DT_DBDate In Expression

May 2, 2007

Hi all,

Actually trying to add one month to current variables value so I can process per month data in for loop for that I have written the following statement in Assign Expression Part of For Loop
dateadd("Month",1,((DT_DBDATE)(@[User::_year]+"/"+@[User::_month]+"/"+"01"))) here I am getting the error message that error converting from data type dt_wstr to to datatype dt_dbdate

@[User::_year] contains year value like 2003 and @[User::_month] contains month value like 1 using these variables I want to add the month to the date created by using these both variables.

so this is the first part to add the month to date and other part is to assign new value to variables by using datepart function

any help and guidance related to this whether I am doing right or not if any other wayout to acheive this goal.



View 1 Replies View Related

(Error) Conversion From DT_TEXT To DT_WSTR Is Not Supported.

Feb 6, 2006

Below is the error I get when trying to convert a Visual FoxPro memo field to a DT_WSTR (4000 ) in a SQL table. It does not let me convert a DT_TEXT to a DT_WSTR.

Thank in advance




TITLE: Editing Component
------------------------------

The component is not in a valid state. The validation errors are:
Error at NMF [Data Conversion [6328]]: Conversion from "DT_TEXT" to "DT_WSTR" is not supported.


Do you want the component to fix these errors automatically?

------------------------------
BUTTONS:

&Yes
&No
Cancel
------------------------------

View 5 Replies View Related

How To Convert DT_NTEXT To DT_WSTR In Script Component

Oct 24, 2006

The IC column referenced in the script below is DT_NTEXT, so when I run it the output value column gets "Microsoft.SqlServer.DTS.Pipeline.BlobColumn" instead of the individual codes (separated by "*"). Also, there is only 1 output row per input row, instead of 1 output row per code.

I found some references to the GetBobData() method, but replacing Row.IC.ToString() with Row.IC.GetBlobData(0, CInt(Row.IC.Length)).ToString() puts "System.Byte[]" in the value output column. There is still only output row per input row.

So how do I convert the IC column to a String that can be Split()?Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim seq As Integer = 0
For Each code As String In Row.IC.ToString().Split("*"c)
'Add a row to the output buffer:
Output0Buffer.AddRow()

'Preserve columns from the input buffer:
Output0Buffer.itemid = Row.itemid
Output0Buffer.attributeid = Row.attributeid
'Output0Buffer.IC = Row.IC

'Add output columns:
Output0Buffer.seq = seq
Output0Buffer.value = code
seq += 1
Next
End Sub

View 4 Replies View Related

DT_GUID To DT_WSTR Yields Braces Around Uniqueidentifier

Sep 8, 2006

I'm using a Lookup component to add a DT_GUID column named cat_id to a data flow, which is used in a downstream Derived Column component to add a DT_WSTR column named value. The DC expression simply casts the uniqueidentifier to the desired type: (DT_WSTR, 434)cat_id

But when the values are persisted in the destination table's nvarchar(434) column, they have braces around them, e.g. {F475DB7F-5CB0-4EE1-9BF2-758C77D7A6D7}

What is introducing those braces, and what do I need to do to prevent it?

View 5 Replies View Related

AS 400 Data Being Interpreted As Unicode Strings (DT_WSTR) Datatype

Jun 23, 2006

I am building a data warehouse. Some of the data comes from an AS 400 EPR system. I used the OLEDB connector when first pulling the data into SQL Server doing simple import data from table option. That worked great for getting the initial data load into SQL Server and creating the base SQL Server tables although it was excruciatingly slow (that was probably due to the transport from the AS 400).

Now, I need to get new records that are added to the AS400 side of things on a daily basis. For that, I was trying to use the OLEDB AS400 connector. However, I found that the OLEDB connector wouldn't work when I was trying to specify an SQL Statement for what to get; i.e., a simple query like Select * from TWLDAT.STKT where BYSDAT >= '2005-01-27' would simply not work. Found articles here explaining that it is probably a problem on the AS400 side of things and where people recommended using an ADO ODBC data reader source for this type of thing. So, I'm trying to implement that. However, I have a huge problem with it.

The original tables that got created were mapped to use NVARCHAR fields for character data. When the ADO ODBC data reader source accesses the AS400 data, it insists on interpreting the string type fields as being unicode strings and giving it a data type of DT_WSTR when what I need it to have is a plain old DT_STR data type. When the strings are interpreted as unicode strings, they cannot be converted in a way that allows the NVARCHAR fields to be filled with the data. The exact error message I get for all the fields that should wind up being nvarchar fields is as follows:

Column "BYStOK" cannot convert between unicode and non-unicode string data types.

Okay, so I try to change the data types in the ADO ODBC data reader to be plain DT_STR data types and I cannot do so.

Does anyone have any idea why the ADO ODBC data reader source insists on interpreting the string data coming from the AS 400 as unicode string data or why it refuses to allow that to be changed to DT_STR data type?

Thanks in advance for any info. By the way, if there is a better way than the ADO ODBC data source to get at this data when I need to specify an SQL command, I would love to hear about it. Not wild about using ODBC in the OLEDB age.



Steve Wells

View 9 Replies View Related

Wildcard For Integers Or Something?

Dec 10, 2001

I'm writing a stored procedure where one of the arguments (WHERE area) really only needs to be used in some circumstances. I.e., when the procedure is passed a USER_ID it needs to check that against the database, but in some instances I'll send 0 instead of a real USER_ID, and in those cases it should return all records regardless of the ID.

Here's what I've got:
...
and b.user_ID = CASE @user_ID WHEN 0 THEN '%'
ELSE @user_ID
...
...the problem being the '%' part. That won't work on an integer column.

Does anyone have any ideas here?

Thanks,
Al

View 2 Replies View Related

Adding Integers

Feb 15, 2007

The code below has this line
SET @SOGallons = @ODTGallons

I need it to add the Current value of @SOGallons to the newly selected value of @ODTGallons and set that as the new value of @SOGallons.

I've tried
SET @SOGallons = @SOGallons + @ODTGallons

SET @SOGalTemp = @SOGallons
SET @SOGallons= @SOGalTemp + @ODTGallons

Neither Worked

<CODE>
FROM [CSITSS].[dbo].[Orderdt] as ODT LEFT OUTER JOIN [CSITSS].[dbo].[Orddtcom] as OCOM
ON ODT.[Companydiv] = OCOM.[Companydiv] AND ODT.[OrderNumber] = OCOM.[OrderNumber] AND
ODT.[Sequence] = OCOM.[Sequence] WHERE ODT.[Companydiv]= 'GLPC-TRANS' AND ODT.[OrderNumber] = @OrdNum AND
([LineType] = 'IP' OR [LineType] = 'SO' OR [LineType] = 'DL' OR [LineType] = 'PU')

OPEN TC1

FETCH NEXT FROM TC1 INTO @LT, @ODTGallons, @ODTComm
WHILE @@FETCH_STATUS=0
BEGIN
IF @LT = 'SO'
BEGIN
SET @SplitTest = 1
SET @SOGallons = @ODTGallons
IF @SOGallons > 0
BEGIN
SET @SOGalTest = 1
END
ELSE
BEGIN
SET @SOGalTest = 0
END
IF @SplitTest <> @SOGalTest
BEGIN
SET @SOGalTest = 0
END
END
ELSE
BEGIN
SET @SOGalTest = 1
END
FETCH NEXT FROM TC1 INTO @LT, @ODTGallons, @ODTComm
END
CLOSE TC1
DEALLOCATE TC1</CODE>

View 3 Replies View Related

Integers Which Are Greater Than Ones Before Them

Aug 29, 2013

I have a table which measures the changes in a feedback rating, measured by an integer. Most of my records are the same. Only the primary key & the timestamp change.

How do I query just the changes?

Example dataset:

idrating
15
25
35
45
56
66

[code]....

There are 20 rows & 5 changes. The query I want will result in just those that are different from the ones before them:

idrating
45
56
97
118
189

I use Microsoft SQL 2008

View 2 Replies View Related

Count Using Non Integers

Feb 23, 2007

1
2
3
* (unscheduled visit) (should be 3.01)
* (unscheduled visit) (should be 3.02)
Basically when there is an unscheduled visit, it should take the previous visit number and add .01

I am not sure how to count using non integers

Thanks

View 10 Replies View Related

Integers && Dynamic Allocation

Mar 10, 2004

Here is what I am trying to do...
I want to goup "members" togethers in a "group."

A table for members and a table for groups.

each containing coluns...

but inside Groups I would like a column that contains ID numbers for the members that be long to that group.

Being that members can belong to multiple groups - I can not use a GroupID in Members - if so I also need a way of it modular.

Obvisiously I am not a very good DB programmer - but I want the least amount of empty/unused space in my tables.

Hope this makes sense

View 1 Replies View Related

Is There A Function Like SUBSTRING But For Integers?

Apr 14, 2006

I'm wondering if there is a function in SQL that works like SUBSTRING function but for integers. Like for example if I have a number like 20010112 and I want to cut it to the first for digits so that it reads 2001?

View 5 Replies View Related

Comparing Two Integers And Returning A Third

Oct 20, 2006

I am having difficulty trying to figure out how to compare two integers stored in a table to return a third. I have two integer fields in one table and two in another like this:

Table1.SomeNumber1 = 1

Table1.SomeNumber2 = 2

Table2.SomeNumber1 = 2

Table2.SomeNumber2 = 1

I need to be able to compare the first number from the first table to the first number in the second table. If the values are different I need to set a variable or field to 0. If the numbers are the same I need to set my variable or field to 1.

I need to follow the same procedure comparing the second number in the first table to the second number in the second table. In addition, I need to be able to do it in a single select statement.

Does anyone have any ideas on how this could be done? Thank you for any help you may be able to provide.

Gmz

View 2 Replies View Related

Converting Integers To DateTime

Jun 12, 2007

It seems I am facing again an unsurmountable problem It should be so simple but one has to spend hours researching how to handle it. The MSDN help on this subject is increadibly obscure.



I have input parameters @months int, @days int, @years int in a stored procedure.



All I want to do is to get a DateTime variable out of them.



DECLARE @dated DateTime.



Thus I want @dated to be set to a DateTime value with month = @months, day = @days and year = @years. The MSDN help says that no CAST should be used since the conversion from int to DateTime should be implicit!!



No examples are given. They seem to show how to CAST or CONVERT varchar to DateTime. Shall I first convert my int to varchar?



It is rudiculous. I've tried dozens of variants. Please help.



Thanks.

View 10 Replies View Related

Decimals Converting Back To Integers? What's Going On?

Feb 23, 2007

I am trying use the decimal data type for a field in SQL Server.  When I input the values below, they round off.
73.827 Rounds to 74
1925.1 Rounds to 1925
119.79 Rounds to 120
What am I missing?  Access never gave me this issue.  Do you see any reason this would happen? I am entering the values into the table directly!

View 5 Replies View Related

Dividing Integers To Produce A Percentage

Aug 27, 2003

I'm embarassed I haven't figured this out already but here goes.

Lets say you need a percentage from dividing two integers for example

"select 2000/4000"

This will produce a zero and I'm assuming that is because of the datatypes involved (the values are coming from columns where the datatype is int)

I've tried converting the values to decimal types but I keep getting overflow erros unless I use very small values.

As always, thanks VERY MUCH for the kind advice.

View 2 Replies View Related

Replace All Integers With Continuous 6 Or More Occurrences With X

Oct 4, 2012

I have a table with below data. Requirement is to replace all integers with continuous 6 or more occurrences with 'x'. Less than 6 occurrences should not be replaced.

create table t1(name varchar (100))
GO
INsert into t1
select '1234ABC123456XYZ1234567890ADS'
GO
INsert into t1
select 'cbv736456XYZ543534534545XLS'
GO

EXPECTED RESULT:

1234ABCxxxxxxXYZxxxxxxxxxxADS
cbvxxxxxxXYZxxxxxxxxxxxxXLS

drop table t1

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle

View 9 Replies View Related

Derived Column Can't Add 8-byte Integers

Jun 7, 2007

I'm having trouble adding a 4-byte integer with an 8-byte integer. Here's what I'm doing:



Column Name: BIG_ID

Derived Column: < add as new column >

Expression: (DT_I8)[ID] + 840230000538058

Data Type: eight-byte signed integer {DT_I8]



The error I get:



The literal 840230000538058 is too large to fit into type DT_I4. The magnitude of the literal overflows the type.





Then I try the expression:



(DT_I8)[ID] + (DT_I8)840230000538058



and



[ID] + 840230000538058



and get the same error.





What am I doing wrong? Is it possible to add 2 8-byte integers in regular expression? Why does it still think the literal is DT_I4?



Thanks,

Michael

View 6 Replies View Related

JOINS And Integers Vs. Indexed Strings.

Jun 15, 2006

I was told that, when possible, use integer fields for the equality comparison in INNER JOINS. Today someone suggested that using character fields that are indexed should be just as efficient. What do you think?


TIA,

barkingdog

View 1 Replies View Related

REPLACE Integers With Text Data

Jun 14, 2006

I am working with a database named €œDocuments€? that contains 4 categories of text documents, each having its own number designation in an integer datatype column named SectionTypeId:

1 = Text
2 = Report
3 = Background
4 = Index

I would like to create a new column named €œDocType€? in which the integer data type for each document is replaced with a varchar data type letter (1 = T, 2 = R, 3 = B, 4 = I). I was able to easily create the new column and cast the data type from integer to varchar:

--CREATE NEW COLUMN €œDocType€? WITH VARCHAR DATATYPE

ALTER TABLE FullDocuments ADD DocType VARCHAR(1) NULL
Go

--UPDATE NEW COLUMN WITH CAST STRING

UPDATE FullDocuments SET DocType = CAST(SectionTypeID AS VARCHAR(1))
Go

But I have problems with the REPLACE method for replacing the numbers with letters. First I tried this based on the examples in MSDN Library:

--REPLACE NUMBERS WITH LETTERS

UPDATE Fulldocuments REPLACE (DocType,"1","T")

Which produced an error message: €œIncorrect syntax near 'REPLACE'.€?

Thinking that the datatype may be the problem, I tried this to convert to DT_WSTR data type prior to replace:

UPDATE Fulldocuments REPLACE ((DT_WSTR,1)DocType,"1","T")

Which produced the same error message: €œIncorrect syntax near 'REPLACE'.€?

I have never done a REPLACE before, so any suggestions for accomplishing this would be appreciated.

View 3 Replies View Related

Can I Return A Range Between Two Integers On A Recordset?

Feb 15, 2008

I have a table where each entry represents a range:
id, num_ini, num_fim
1, 1, 19
2, 20, 39
3, 40, 59
etc

Is there any way to select a recordset on this table with the following format?
id, num
1, 1
1, 2
1, 3
etc
2, 20
2, 21
2, 22
etc

I'm using MSSQL 2005

tks

View 1 Replies View Related

Concatenating Numbers/Converting Dates To Integers

Feb 24, 2004

My ERP software stores all dates as integers. So originally, I wrote a T-SQL function to convert these integer dates to normal people dates in the query I use as the recordset for my report. Well...that worked fine on 1,000 rows, but NOT for 100,000. So I've figured out that if I convert my normal person date parameter to an integer date, then SQL only has to convert my 1 parameter instead of having to convert 100,000 fields, (actually, 300,000 because I have 3 date columns).

So my question is, what is the best way to do this? This is what I have so far:



SET @Macola = Cast(Datepart(yy,@MacolaDate) as varchar) + Cast(Datepart(mm,@MacolaDate) as varchar) + Cast(Datepart(dd,@MacolaDate) as varchar)



However, I want the leading zeros for the month and day. For example if I enter '1/1/2004' into this function, it returns 200411, but I need it to return 20040101.

Any suggestions would be greatly apprectiated. Thank you.

View 9 Replies View Related

Using SET To Correct Integers In A Dbo.Table: Incorrect Sysntax Near The Keyword ‘SET’?

Jan 31, 2008

Hi all,

I have a wrong €œdbo.Samples€? table:
SampleID SampleName Matrix SampleType ChemGroup ProjectID
1 Blueriver01 Soil QA VOCs 1
7 Greentree01 Water Primary VOCs 1
8 Greentree02 Water Duplicate VOCs 1
9 Greentree03 Water QA VOCs 2
10 Greentree11 Soil Primary VOCs 1
11 Greentree11 Soil Duplicate VOCs 1
12 Greentree11 Soil QA VOCs 3
13 Redrock01 Water Primary VOCs 1
14 Redrock02 Water Duplicate VOCs 1
15 Redrock03 Water QA VOCs 2
16 Redrock11 Soil Primary VOCs 1
17 Redrock12 Soil Duplicate VOCs 1
18 Redrock13 Soil QA VOCs 3



I used the following sql code to correct the wrong ProjectIds:

USE ChemDatabase
GO
ALTER TABLE Samples
SET ProjectID = 4 WHERE SampleID = 7
SET ProjectID = 4 WHERE SampleID = 8
SET ProjectID = 5 WHERE SampleID = 9
SET ProjectID = 4 WHERE SampleID = 10
SET ProjectID = 4 WHERE SampleID = 11
SET ProjectID = 6 WHERE SampleID = 12
SET ProjectID = 7 WHERE SampleID = 13
SET ProjectID = 7 WHERE SampleID = 14
SET ProjectID = 8 WHERE SampleID = 15
SET ProjectID = 7 WHERE SampleID = 16
SET ProjectID = 7 WHERE SampleID = 17
SET ProjectID = 9 WHERE SampleID = 18
GO


I got the following error message:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.


Please help and tell me what it is the right syntax for my €˜SET€™
used in this sql code. I think there are more mistakes in this set of sql code. Please enlighten me and advise me how to make this set of code right.


Thanks,
Scott Chang

View 4 Replies View Related

Casting Help

Aug 25, 2004

Can someone please help me with this? I'm losing all my hair trying to figure it out. I've tried all that I can think of. I am getting the Error converting data type varchar to numeric.

Thanks
Sam "O"



cmdInsert = New SqlCommand( "INSERT INTO tmp_blank (sessionid, ssn,job,sun,mon,tue,wed,thu,fri,sat,totcol)
SELECT '" & Session.SessionId & "', ssn,job,sun,mon,tue,wed,thu,fri,sat,
(cast(sun as numeric(4,2)) + cast(mon as numeric(4,2)) +
cast(tue as numeric(4,2)) + cast(wed as numeric(4,2)) +
cast(thu as numeric(4,2)) + cast(fri as numeric(4,2)) +
cast(sat as numeric(4,2))) as totcol from
" & strTableName, conTimeCard)

intUpdateCount = cmdInsert.ExecuteNonQuery()

View 4 Replies View Related

Casting

Jul 3, 2007

Hello,

I'm new to SQL Server and I have to finish a VB.Net program from an employee that has left the company.

I have a table with column 'vanafdatum' witch has datatype char but they stored only dates in this column ( like this: 13/09/2006).

When I try to run the next querie I receive an error message "The conversion of a char data type to a datetime data type resulted in an out of range datetime value".

This is the querie I try to run


SELECT MAX(CAST(vanafdatum AS datetime)) AS vanaf_datum
FROM tarieven
WHERE (vanafdatum <
(SELECT getdate()))


Is there any way I can cast the char datatype to a datetime datatype without getting this error.
I allready tried to change the data type from char to datetime, but doesn't work either.
A possible solution is to erase the column an manually fill in all the fields again in the data type datetime, but that seems a little stupid to do.
So I wonder if there is a solution that can change the data type for all rows in the database without losing any data.

View 5 Replies View Related

Casting...

Aug 30, 2006

I've got an ftp task that will be downloading a couple of files each night. today they're called

blah20060830blah

the date value in the middle changes each day. I'm trying to adjust this value with an expression. The expression doesn't want to cast my getdate function into a string that this property will accept. I know i'm missing something stupid.

Thank you

View 23 Replies View Related

SQL CE Casting

Feb 6, 2007

Hi,

I would like to know if casting is supported in SQL Server Compact Edition. I get an error when i attempt to cast a datetime or bigint value to nvarchar or ntext. CAST(datetime AS nvarchar(15))

Does anybody knows if its supported or how could i cast values?

Thank you

View 1 Replies View Related

SQL Casting Question

Oct 10, 2005

Hi,I've come across a problem that requires i cast a string to a date, had a go but can't get it to work properly.SELECT EventID, EventName, EventShortDesc, EventLink, EventStartDateFROM dbo.tbl_EventWHERE (CAST EventStartDate AS DATE) > GetDate()ORDER BY EventStartDate DESCBasically i only want events which haven't expired (were before the current date) to be returned. EventStartDate is held as an varchar(10) therefore needs to be cast before i can compare. Is this the best way or is there a better one? If it is how do i get it to work?Any help would be great thanks,drazic19

View 2 Replies View Related

Casting '01/07' To A Date

Feb 18, 2008

Hi All

My brain has truned to mush at the moment.
:(

I want to sort on a char(5) column with data in it that looks like

01/07
05/02
06/01
12/01
07/98

so it comes out in the human date order.
e.g

07/98
06/01
12/01
05/02
01/07

--
David

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved