CAST In SELECT Or Data Conversion In SSIS
Oct 18, 2007
Current situation:
Our data source is Oracle and there is a particular column, called number_of_units, with datatype numeric(28, 0) that we will extract into SQL Server data destination.
However, in our SQL Server data destination this column has a datatype numeric(34, 14). This is because we used this column to do some basic calculation (e.g. number_of_units/60) and we need to keep the decimals in tact.
In our current SSIS, we do a select query with CAST from the data source:
SELECT CAST(number_of_units AS NUMERIC(34, 14)) AS number_of_units FROM TABLE_A;
Will casting up from numeric(28,0) to (34,14) cause bigger number of bytes to be transferred across from the data source? I am talking about millions of records over here.
Are there other better ways of doing it?
1) Don't do a CAST in the select query (i.e. SELECT number_of_units FROM TABLE_A).
2) Add a derived/calculated column with numeric(34,14) in SSIS to fill in the calculation.
3) Lastly, load the calculation into SQL Server data destination.
View 5 Replies
ADVERTISEMENT
Nov 7, 2007
I have been having some trouble trying to get the date format YYYYMMDD 00:00:00.00 to convert to MM/DD/YYYY. I am using SQL 2005 and Reporting Services. I attempted several variations to solve this problem as outlined below, with the final working conclusion at the end. I am not saying that this is the BEST or the ONLY way to achieve the solution, but it worked for me.
Code Block
---------------------------------------------------------------------------------------
SELECT DATE_TIME AS DATE1
FROM TRANSLOG
--returns value of 20071027 02:26:24.06
---------------------------------------------------------------------------------------
SELECT CAST(DATE_TIME AS SMALLDATETIME) AS DATE1
FROM TRANSLOG
--returns value of 2007-10-27 02:26:00
---------------------------------------------------------------------------------------
SELECT LEFT(CAST(DATE_TIME AS SMALLDATETIME),11) AS DATE1
FROM TRANSLOG
--returns value of Oct 27 2007
---------------------------------------------------------------------------------------
SELECT CAST(SUBSTRING(DATE_TIME,0,9) AS SMALLDATETIME) AS DATE1
FROM TRANSLOG
--returns value of 2007-10-27 00:00:00
---------------------------------------------------------------------------------------
SELECT CONVERT(VARCHAR(10), DATE_TIME, 103)
FROM TRANSLOG
--although this works with GETDATE(), it does not work with the DATE_TIME field
---------------------------------------------------------------------------------------
SELECT CONVERT(VARCHAR(10), CAST(DATE_TIME AS SMALLDATETIME), 101) AS DATE1
FROM TRANSLOG
--THIS WORKS!!! Displaying 10/27/2007
Hope this helps somebody someday!
-Jody
View 5 Replies
View Related
Aug 3, 2006
Hello
I have an odd problem that is driving me nutz. I have a very simple SSIS package that imports a 5 colum flatfile into a sql Server 2005 Table.
When I created this package with the wizzard, it will execute perfectly fine and processes all rows into the destination table.
But when I hit F5 to execute it manually it will fail before inserting a single row.
The error it generates is (Spalte 5 is a Datetime in the format DD.MM.YYYY) :
Error: 0xC02020A1 at Datenflusstask, Source - Daten_NC_1_txt [1]: Data conversion failed. The data conversion for column "Spalte 5" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at Datenflusstask, Source - Daten_NC_1_txt [1]: The "output column "Spalte 5" (25)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "Spalte 5" (25)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0202092 at Datenflusstask, Source - Daten_NC_1_txt [1]: An error occurred while processing file "C:WorkDaten_NC_1.txt" on data row 177.
Edit: Modified the Title so it properly reflects the Problem & the Solution
View 3 Replies
View Related
Jul 31, 2007
what is the use of Data Conversion
please give me an example
View 1 Replies
View Related
Oct 2, 2007
I'm getting some data from a flat file with a SSIS Package, it comes a integer but I would like to converted to a decimal with a 3 scale.
Example:
Flat File: 2070015000950011800
In the data conversion I had it with a 3 scale, but what I got was this:20700.00015000.0009500.00011800.000But what I want is something like this:20.70015.0009.50011.800
I dont know if you guys get the idea. But I will apreciate if anyone can help me.
Thanks,
Erick
View 2 Replies
View Related
Nov 5, 2015
I am loading incremental data from sql server to oracle by using ssis and while data convert it says data type dont match.
SQL column data type is:smallint:SQL Server 2008 r2
Oracledata type is:Number(5):Oracle 10 g.
View 5 Replies
View Related
Apr 30, 2007
I created a SSIS package moving data from a SQL 2005 table to an existing DB2 table on AS400 using Microsoft OLE DB Provider for DB2.
When the package was run, it showed that rows were successfully inserted to DB2. However, the data didn't seem to be converted correctly. Most of the string values were inserted as unusual characters. Also any string values of digits were not inserted.
For example, 1.) a character field (char(1) or nchar(1) as I have tried both types) in SQL 2005 table with a simple value of 'H' was inserted into the DB2 table field of type "A" (alphanumeric) of length 1 as 'ç' and others letters were inserted as other unusual characters. 2.) A string value of '00100' in SQL Server is not inserted to DB2 table at all.
Later we found that the fields inserted with usual characters are difined as CSSID =65535. A few fields with correct data inserted have CSSID=00037.
Does anyone know why this happened and how to solve this to get the data inserted correctly in the DB2 table?
Thanks in advance for any help!
View 3 Replies
View Related
Nov 17, 2015
I am using flat file as source.I have quantity column in flat file which is a Numeric datatype and target table quantity datatype is Numeric.
I am able to load data from source to target but when I am comparing data from source to target I am not getting exact record from source flat files Source having data like
Source >> Target
31.61 >> 31.0000000000
00029.430 >> 29.0000000000
as we can see that data are not matching with source I can not change the target table quantity data type, is there any thing which I can do with source column data type.
View 9 Replies
View Related
Apr 20, 2008
Using OnSelected on the SqlDataSource----here is my currect code:private void On_Load(Object sender, SqlDataSourceCommandEventArgs e){if (e.Command.Parameters["@DueDate"].Value = "NULL"){e.Command.Parameters["@DueDate"].Value = "0000-00-00 00:00:00.000";}}
I am trying to change the value from NULL(in a datetime field) to "0000-00-00 00:00:00.000" for use on the page.Any ideas, suggestions, or thoughts would be greatly appreciated!
,Gary"we truly fail, only when we quit."
View 2 Replies
View Related
Dec 15, 2006
[Flat File Destination [46500]] Error: Data conversion failed. The data conversion for column "Column 0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page." What does this error mean exactly?
I am taking columns from a flat file source. Then I am adding some new columns then rewriting the file to a ragged file format with fixed column values. I've taken the Destination component off and it works fine. So I know it could be the destination component but what could it be? Any ideas?
View 6 Replies
View Related
Nov 30, 2015
I am trying to upload data from CSV to Sql table. I have a column as 'arrived_date' value '13:45' etc and while trying to load data i am getting error as "data conversion failed ,truncation may occur while loading data". In flat file connection this column datatype is string but in my table datatype is as time(). There is a error with conversion. I tried to change data type in advanced editor but no use. Using data conversion after flatfile makes my error disappear but it is giving error right at the file not even going through from file?
View 10 Replies
View Related
Feb 12, 2008
Hi, I am less of a technical but more of a analyst professional and right now investgating on various tools / options for the new conversion project I will be leading in insurance client. One of the tools that client want to use is SSIS but the source and target database is not on SQL server but plans are to build a staging SQL server database for transformation. Does SSID supports this kind of ETL process where both source and target system are non SQL servers.
Thanks,
H Gill
View 4 Replies
View Related
Apr 24, 2007
It seems I can do the type conversion in two place:
1. The advanced editor for flat file source => Input and output roperties. And I can set the output columns data type properties there.
2. Using a Data conversion box in Data flow transformation.
Any difference between these two? Which one should be used?
View 1 Replies
View Related
Sep 6, 2007
Hi i am working on sending data from a dat file to table in sql server Database and i am using the Data conversion transformation in ssis to convert string of fixed length into numeric (11,5) which is the datatype for the price field in the table and its returning an error saying that status vale 6 and error text as Conersion Failed sue to overflow of specific type ... Can anyone let me know how to overcome this error.
View 6 Replies
View Related
Mar 7, 2008
Hello Friends......I have to do the folowing transformation in SSIS:
I have a table in which I have colums of Month and details of sells in that month.
Now I want to make a packege which converts the data of Month column into code columns or numeric values insted of characters .
So how can I do this by using SSIS ?
View 4 Replies
View Related
Oct 19, 2006
I am trying to put the data from a field in my database into a row in a table using the SQLDataSource.Select statement. I am using the following code: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'" myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)But when I run the code, I get the following error:Server Error in '/YorZap' Application. Unable to cast object of type 'System.Data.DataView' to type 'System.String'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.Source Error: Line 54: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'"
Line 55: 'myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments).GetEnumerator.Current, String)
Line 56: myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)
Line 57:
Line 58: filesTable.Rows.Add(myDataRow)Source File: D:YorZapdir_list_sort.aspx Line: 56 Stack Trace: [InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.]
ASP.dir_list_sort_aspx.BindFileDataToGrid(String strSortField) in D:YorZapdir_list_sort.aspx:56
ASP.dir_list_sort_aspx.Page_Load(Object sender, EventArgs e) in D:YorZapdir_list_sort.aspx:7
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45
System.Web.UI.Control.OnLoad(EventArgs e) +80
System.Web.UI.Control.LoadRecursive() +49
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3743
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210 Please help me!
View 3 Replies
View Related
Jan 26, 2006
What is the correct syntax for querying the first 20 characters of amemo field?I'm trying toSELECT CAST (varchar(20) fieldname) as newfieldnameand i get a bunch of errorsCan someone please help me?MS Access DB used with ColdFusionThanks
View 4 Replies
View Related
Jul 20, 2005
name1 field is is nvarchar(40).(1)select case when isnumeric(name1) = 1 thencast(name1 as int) else null end as nameIn (1) when name1 is not numeric, name is null andits type becomes number not string(40).And,(2)select case when isnumeric(name1) = 1 thencast(name1 as int) else name1 end as nameIn (2) when name1 is Not numeric it gives error:can not cast name to int.Basically, i like to convert name1 to Integer if it isnumeric or keep it in its origianl nvarchar(40) if itis Not numeric. how?.--Sent by 3 from yahoo element from comThis is a spam protected message. Please answer with reference header.Posted via http://www.usenet-replayer.com
View 2 Replies
View Related
Aug 27, 2007
Hello Everyone,
I'm new to SQL within the past year and am having quite a bit of difficulty trying to replace NULL in a column cast as smalldatetime. What i'd like to do is return the value "EMPLOYEED" within any record containing a null value, obviously I need to CAST the column to a varchar but when I try and do this I keep getting errors. I'm trying hard to find the answer online but the only examples I get are is for money and i'm not sure i'm completely understanding how this is supposed to work. I've also tried using the ISNULL function as well - getting the same errors in this case (though i'm using ISNULL successfully in another column).
It seems to me that I should be able to write the syntax as the following:
CAST(ISNULL(TerminationDate, 'EMPLOYEED') AS Varchar(20)) AS TerminationDate
----OR---
CAST(COALESCE(TerminationDate, 'EMPLOYEED') AS Varchar(20)) AS TerminationDate
I'm new enough that I could just be confusing myself, but I really seriously cannot find any good explanations or examples online for what I am trying to do.
Can some nice SQL expert provide assistance?
Thanks!!!!!
Ave
View 3 Replies
View Related
Jun 6, 2007
Hi All,
I need a solution for the following:
I have a field with datatype string, length 8, in the form yyyymmdd (f.e. 20070604).
Now I need to transfer this field into a field with datatype dt_DBDATE or DT_DBDATE.
I tried to perform this with a derrived column and type cast (DT_DATE) or (DT_DBDATE), but this does not work.
Any hint for me!!!
Thanks in Advance
ulrike
View 5 Replies
View Related
May 30, 2008
Correct me if i am wrong anywhere.
This is my table structure.
CREATE TABLE [AC].[RUN_PARMS](
[FILE_NAME] [varchar](6) NOT NULL,
[RUN_IND] [bit] NOT NULL,
[GROUP] [varchar](50) NULL,
CONSTRAINT [pk_RUN_PARMS] PRIMARY KEY CLUSTERED
(
[FILE_NAME]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I am using an execute package task in SSIS based on the value of RUN_IND field in RUN_PARMS table. If all the value of RUN_IND field is 0 for all the files in a group, then the execute package task should be enabled else it should be disabled. So i have used the following query.
SELECT SUM(CONVERT(INT,RUN_IND)) AS RUN_IND FROM AC.RUN_PARMS
WHERE [GROUP] = 'FIN'
This will return the value which will be greater than zero. Since i have made the value as 1 for FIN group. Imagine the FIN group has 10 files in it. The output look like,
RUN_IND
10
and
SELECT SUM(CONVERT(INT,RUN_IND)) AS RUN_IND FROM AC.RUN_PARMS
WHERE [GROUP] = 'Ven'
This will return the value which will be equal to zero. Since i have made the value as 0 for Ven group. The output look like,
RUN_IND
0
I have created a variable in the package "user::vardisablepackage".
I have set Resultset as Single for SQL task component
In the package properties (The package which has to be disabled) i have selected property as "Disable" and the expression as @User::vardisablepackage = 0 ?False:True
The problem now is i am not sure what should be the data type for vardisablepackage.
The expression will assign False to Disable property when the RUN_IND = 0.
I am unable to complete this task since i have problem assigning data type for this variable.
Please suggest your views.
Sarvan
View 4 Replies
View Related
Oct 10, 2007
Hi,
I get the following error opening an existing (or working) ssis package on my new server.
Error loading 'TestExecutionFromScript.dtsx' : Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSContainer90'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{8BDFE892-E9D8-4D23-9739-DA807BCDC2AC}' failed due to the following error: Library not registered. (Exception from HRESULT: 0x8002801D (TYPE_E_LIBNOTREGISTERED))..
I found in a blog that this is caused due to a dll which is not registered ("DTS.dll")
Can anyone try to explain the exact reason for this? I am not even able to execute a package from the command prompt using the dtexec utility. I have "Microsoft SQL Server Analysis Services Designer Version 9.00.3042.00" on my server. My other machine which executed this package successfully was running "Microsoft SQL Server Analysis Services Designer Version 9.00.1399.00".
Thanks very much for any kind of help.
View 3 Replies
View Related
Oct 4, 2006
When I open up my existing SSIS project, I always get this error. Does anyone know what was wrong ?
TITLE: Microsoft Visual Studio
------------------------------
Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.IObjectWithSite'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{FC4801A3-2BA9-11CF-A229-00AA003D7352}' failed due to the following error: The application called an interface that was marshalled for a different thread. (Exception from HRESULT: 0x8001010E (RPC_E_WRONG_THREAD)).
View 32 Replies
View Related
Jan 7, 2008
Hi Experts,
I am extracting data from SQL Server 2005 to flat file destination. I am using SQL Command to specify the data selection query. One of my query uses Replicate function to derive a column value. When I execute this package it fails with the error "Data conversion failed. The data conversion for column "value" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page".
The reason for the problem is that, it is taking the InputColumnWidth of the flat file destination as 8000 and I specified the OutputColumnWidth as 4.
If I change the OutputColumnWidth to 8000, it is working without any error but resulting in the column width of 8000.
I tried using DerivedColumn Transformation's Type cast and DataConversion Transformation but still I am getting the same error in the respective Transformation components.
Can anyone suggest how to solve this issue.
View 11 Replies
View Related
Apr 24, 2006
Hi,
I have built a few pages and a stored procedure and a class on the back of a SQL2000 dbase. and I get the following error:
Cast from type 'DBNull' to type 'String' is not valid.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: Cast from type 'DBNull' to type 'String' is not valid.Source Error:
Line 111: Dim myWorkJobs As WorkJobsDATA = New WorkJobsDATA
Line 112:
Line 113: myWorkJobs.CustomerID = CStr(parameterCustomerID.Value)
Line 114: myWorkJobs.WorkID = CStr(parameterWorkID.Value)
Line 115: myWorkJobs.DateOfQuote = CStr(parameterDateOfQuote.Value).Trim()
Source File: C:InetpubwwwrootCommerceComponentsWorkJobs.vb Line: 113
My Database has 1 line of data (for testing) and all fields are populated. I am Querying a column called IndividualID which has a value of 3425243 at the moment. This is hardcoded in the aspx.vb at the moment.
ASPX VB:
Public Class WorkRequest Inherits System.Web.UI.Page
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here End Sub
Private Sub btnEnter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEnter.Click
'for now, send this value (stored in dbase under individualID) to the querystring
Dim IndividualID As String = "3425243"
Response.Redirect("WorkRequestMain.aspx?IndividualID=" & IndividualID) End SubEnd Class
COMPONENTSWorkJobs.vb (This is the class file)
Imports SystemImports System.ConfigurationImports System.DataImports System.Data.SqlClient
Namespace ASPNET.StarterKit.Commerce
Public Class WorkJobsDATA
Public CustomerID As String Public WorkID As String Public DateOfQuote As String Public QuoteAmount As String Public Title As Decimal Public FirstName As String Public Surname As String Public FirstLine As String Public District As String Public Town As String Public Postcode As String Public Telephone As String Public Requirements As String Public WorkRequired As String Public EmailAddress As String
End Class
Public Class WorkJobs
Public Function GetWorkDetails(ByVal IndividualID As String) As WorkJobsDATA
Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim myCommand As SqlCommand = New SqlCommand("SP_PendingQuotes", myConnection)
' Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure
' Add Parameters to SPROC Dim parameterIndividualID As SqlParameter = New SqlParameter("@IndividualID", SqlDbType.NVarChar, 50) parameterIndividualID.Value = IndividualID myCommand.Parameters.Add(parameterIndividualID)
Dim parameterCustomerID As SqlParameter = New SqlParameter("@CustomerID", SqlDbType.BigInt, 8) parameterCustomerID.Direction = ParameterDirection.Output myCommand.Parameters.Add(parameterCustomerID)
Dim parameterWorkID As SqlParameter = New SqlParameter("@WorkID", SqlDbType.NVarChar, 50) parameterWorkID.Direction = ParameterDirection.Output myCommand.Parameters.Add(parameterWorkID)
Dim parameterDateOfQuote As SqlParameter = New SqlParameter("@DateOfQuote", SqlDbType.DateTime, 8) parameterDateOfQuote.Direction = ParameterDirection.Output myCommand.Parameters.Add(parameterDateOfQuote)
Dim parameterQuoteAmount As SqlParameter = New SqlParameter("@QuoteAmount", SqlDbType.Money, 8) parameterQuoteAmount.Direction = ParameterDirection.Output myCommand.Parameters.Add(parameterQuoteAmount)
Dim parameterTitle As SqlParameter = New SqlParameter("@Title", SqlDbType.NVarChar, 50) parameterTitle.Direction = ParameterDirection.Output myCommand.Parameters.Add(parameterTitle)
Dim parameterFirstName As SqlParameter = New SqlParameter("@FirstName", SqlDbType.NVarChar, 50) parameterFirstName.Direction = ParameterDirection.Output myCommand.Parameters.Add(parameterFirstName)
Dim parameterSurname As SqlParameter = New SqlParameter("@Surname", SqlDbType.NVarChar, 50) parameterSurname.Direction = ParameterDirection.Output myCommand.Parameters.Add(parameterSurname)
Dim parameterFirstLine As SqlParameter = New SqlParameter("@FirstLine ", SqlDbType.NVarChar, 50) parameterFirstLine.Direction = ParameterDirection.Output myCommand.Parameters.Add(parameterFirstLine)
Dim parameterDistrict As SqlParameter = New SqlParameter("@District", SqlDbType.NVarChar, 50) parameterDistrict.Direction = ParameterDirection.Output myCommand.Parameters.Add(parameterDistrict)
Dim parameterTown As SqlParameter = New SqlParameter("@Town", SqlDbType.NVarChar, 50) parameterTown.Direction = ParameterDirection.Output myCommand.Parameters.Add(parameterTown)
Dim parameterPostcode As SqlParameter = New SqlParameter("@Postcode", SqlDbType.NVarChar, 50) parameterPostcode.Direction = ParameterDirection.Output myCommand.Parameters.Add(parameterPostcode)
Dim parameterTelephone As SqlParameter = New SqlParameter("@Telephone", SqlDbType.NVarChar, 50) parameterTelephone.Direction = ParameterDirection.Output myCommand.Parameters.Add(parameterTelephone)
Dim parameterRequirements As SqlParameter = New SqlParameter("@Requirements", SqlDbType.NVarChar, 3500) parameterRequirements.Direction = ParameterDirection.Output myCommand.Parameters.Add(parameterRequirements)
Dim parameterWorkRequired As SqlParameter = New SqlParameter("@WorkRequired", SqlDbType.NVarChar, 3500) parameterWorkRequired.Direction = ParameterDirection.Output myCommand.Parameters.Add(parameterWorkRequired)
Dim parameterEmailAddress As SqlParameter = New SqlParameter("@EmailAddress", SqlDbType.NVarChar, 100) parameterEmailAddress.Direction = ParameterDirection.Output myCommand.Parameters.Add(parameterEmailAddress)
myConnection.Open() myCommand.ExecuteNonQuery() myConnection.Close()
parameterEmailAddress.Value.GetType()
Dim myWorkJobs As WorkJobsDATA = New WorkJobsDATA
myWorkJobs.CustomerID = CStr(parameterCustomerID.Value) myWorkJobs.WorkID = CStr(parameterWorkID.Value) myWorkJobs.DateOfQuote = CStr(parameterDateOfQuote.Value).Trim() myWorkJobs.Title = CStr(parameterTitle.Value).Trim() myWorkJobs.FirstName = CStr(parameterFirstName.Value).Trim() myWorkJobs.Surname = CStr(parameterSurname.Value).Trim() myWorkJobs.FirstLine = CStr(parameterFirstLine.Value).Trim() myWorkJobs.District = CStr(parameterDistrict.Value).Trim() myWorkJobs.Town = CStr(parameterTown.Value).Trim() myWorkJobs.Postcode = CStr(parameterPostcode.Value).Trim() myWorkJobs.Telephone = CStr(parameterTelephone.Value).Trim() myWorkJobs.Requirements = CStr(parameterRequirements.Value).Trim() myWorkJobs.WorkRequired = CStr(parameterWorkRequired.Value).Trim() myWorkJobs.EmailAddress = CStr(parameterEmailAddress.Value).Trim()
Return myWorkJobs
End Function
End ClassEnd Namespace
And finally my stored procedure:
CREATE Procedure SP_PendingQuotes( @IndividualID nvarchar, @CustomerID bigint OUTPUT, @WorkID nvarchar(50) OUTPUT, @DateOfQuote datetime OUTPUT, @QuoteAmount money OUTPUT, @Title nvarchar(50) OUTPUT, @FirstName nvarchar(50) OUTPUT, @Surname nvarchar(50) OUTPUT, @FirstLine nvarchar(50) OUTPUT, @District nvarchar(50) OUTPUT, @Town nvarchar(50) OUTPUT, @Postcode nvarchar(50) OUTPUT, @Telephone nvarchar(50) OUTPUT, @Requirements nvarchar(3500) OUTPUT, @WorkRequired nvarchar(3500) OUTPUT, @EmailAddress nvarchar(100) OUTPUT)AS
SELECT @IndividualID = IndividualID, @CustomerID = CustomerID, @WorkID = WorkID, @DateOfQuote = DateOfQuote, @QuoteAmount = QuoteAmount, @Title = Title, @FirstName = FirstName, @Surname = Surname, @FirstLine = FirstLine, @District = District, @Town = Town, @Postcode = Postcode, @Telephone = Telephone, @Requirements = Requirements, @WorkRequired = WorkRequired, @EmailAddress = EmailAddress
FROM PendingQuotes
WHERE IndividualID = @IndividualIDGO
Any ideas anyone?
I appreciate this is a big amount of data, but if anyone wants to chat to me i'm available on MSN Messenger under wolvokid@msn.com
View 2 Replies
View Related
May 30, 2007
Hey y'all,
Can someone make this right? i have an int column and need text:
SELECT (SELECT CASE score WHEN 0 THEN 'qqqqqqqq' ELSE 1 END) AS Expr1, COUNT(Score) AS Expr1
Thanks in advance
View 3 Replies
View Related
Apr 16, 2014
i am trying to take a field that has part of a date in it, so I have to parse it out as follows:
SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)
This is because a date of 04/16/2014 will show as 160416 in the first part of the field I need to parse it out of, thus becoming 04162014.
From there I then need to convert this "date" into a legitimate SQL datetime type, so that I can then run a DATEDIFF to compare it to when the record was actually entered, which is a separate field in the table, and already in datetime format.
When I use the below statement, I am getting the message that, "Conversion failed when converting date and/or time from character string."
CAST((SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)) as datetime)
I also tried CONVERT(datetime, (SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)), and got the same message.
how I can parse that field, then convert it to a datetime format for running a DATEDIFF statement?
View 9 Replies
View Related
Mar 25, 2007
I have date in Flat file and it is in the string format,but now i want to convert it in to normal date format.I have tried doing this by SSIS but it is not working.
View 12 Replies
View Related
Apr 8, 2008
Can you assist me with converting the code below to VB ?
Function Main()
Dim objExcel
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open "C:FTPOUTGOINGFTP_MarkOff.xls",,,,""
objExcel.Workbooks(1).SaveAs "C:FTPOUTGOINGFTPMarkoff.xls",,"password"
objExcel.Workbooks.Close
Set objExcel = Nothing
Main = DTSTaskExecResult_Success
End Function
View 6 Replies
View Related
Jan 30, 2008
Why would I get these errors:
" SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Invalid character value for cast specification".
There was an error with input column "UniqueID" (3486) on input "OLE DB Command Input" (3438). The column status returned was: "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 : SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Command Input" (3438)" failed because error code 0xC0209069 occurred, and the error row disposition on "input "OLE DB Command Input" (3438)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure."
I read related posts but could not figure out the error?
View 3 Replies
View Related
Apr 5, 2006
I am trying to convert a command line using the dtexecui utility. I need to pass three parameters ; account number ,begin and end date to project.
What am i doing wrong ?
DTEXEC /DTS "File SystemArchive Data" /SERVER SRV2 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW package /SET "Account_Number";"'00001'" /SET "File_Name";"'C:InetpubwwwrootoutputArchive'" /SET "Begin_Date";"'04/03/2006'" /SET "End_Date";"'04/04/2006'"
Error I get
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 9:52:49 AM
Warning: 2006-04-05 09:52:51.58
Code: 0x80012018
Source: Archive Data
Description: The configuration entry, "Account_Number", has an incorrect form
at because it does not begin with the package delimiter. Prepend "package" to t
he package path.
End Warning
Warning: 2006-04-05 09:52:51.58
Code: 0x80012017
Source: Archive Data
Description: The package path referenced an object that cannot be found: "Acc
ount_Number". This occurs when an attempt is made to resolve a package path to a
n object that cannot be found.
End Warning
DTExec: Could not set Account_Number value to '00001'.
Started: 9:52:49 AM
Finished: 9:52:51 AM
Elapsed: 2.172 seconds
View 1 Replies
View Related
Sep 6, 2007
I tried to setup a flat file data source that has code page 37 (EBCDIC)
Then I have a flat file destionation that is ASCII.
And inbetween I have tried several different data flow conversion tasks liked Data Conversion, and Derived Column. But I keep getting errors about different code pages.
I also tried to load the EBCDIC data into a SQL Server DB, and it complains about different code page.
Has anyone been able to do this with SSIS out of the box, without any extra components ?
Clarence
View 21 Replies
View Related
Apr 1, 2008
I have been trying to read a flat file which has a birthdate field. The field is string and the format is "011594". I have been trying to convert it to a date field in the following format "01/15/1994". Using the forum I was able to write the following expression but I am still keep getting error messages.
I also noticed few records are missing birthdate and few do not have the complete birthdate which means some birthdates are missing year and some are missing month. For example, some are completely missing and some are partially missing, like " " or "0312 ". Could this be the reason I was getting error message since the following expression does not include null and incomplete date of birth?
(DT_DBDATE)(SUBSTRING([DateofBirth] ,1,2) + "/" + SUBSTRING([DateofBirth] ,3,2) + "/" + SUBSTRING([DateofBirth] ,5,2))
Here are the error messages:
[Derived Column [1014]] Error: An error occurred while attempting to perform a type cast.
[Derived Column [1014]] Error: The "component "Derived Column" (1014)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "DOB" (2292)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The ProcessInput method on component "Derived Column" (1014) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.
View 13 Replies
View Related
