I am trying to import an Excel spreadsheet into SQL Server. Why can I impor
t
an empty Excel column only if it is a text field? If the field is a number
or date field, I get a conversion error.
i.e.
create table labresults
(
analyte varchar(50),
sampledate datetime,
result numeric(19,6)
)
The excel worksheet has 3 columns:
analyte (formatted as text)
sampledate (formatted as date)
result (formatted as number)
If the first 7 rows in the analyte column are empty, the file imports fine,
but if the first 7 rows of sampledate or result are empty, I get a
"Conversions invalid for data types" error.
Anybody have any good suggestions on how to fix this issue?
Thanks.
ArcherHi
Sounds like it is related to:
http://www.sqldts.com/default.aspx?254
John
"bagman3rd" wrote:
> I am trying to import an Excel spreadsheet into SQL Server. Why can I imp
ort
> an empty Excel column only if it is a text field? If the field is a numbe
r
> or date field, I get a conversion error.
> i.e.
> create table labresults
> (
> analyte varchar(50),
> sampledate datetime,
> result numeric(19,6)
> )
> The excel worksheet has 3 columns:
> analyte (formatted as text)
> sampledate (formatted as date)
> result (formatted as number)
> If the first 7 rows in the analyte column are empty, the file imports fine
,
> but if the first 7 rows of sampledate or result are empty, I get a
> "Conversions invalid for data types" error.
> Anybody have any good suggestions on how to fix this issue?
> Thanks.
> Archer
>sql
Showing posts with label field. Show all posts
Showing posts with label field. Show all posts
Thursday, March 29, 2012
dts Foxpro MEMO Field
how can I import memo field from Foxpro table'
Many Thanks in Advance!!You can transform it to a large varchar() column or a text column.
Which of these to use would depend on the average and max lengths of the
memo field.
* in vfp
select max(len(theMemoField)) as max_len from theTable
If' the max length is over 8000, you'll have to use text. If it's <=8000
_and you can say with assurance_ that the max length will stay <=8000,
I'd tend to go with a large varchar column.
SQLbeginner wrote:
>how can I import memo field from Foxpro table'
>Many Thanks in Advance!!
>|||Thanks Trey.
"Trey Walpole" wrote:
> You can transform it to a large varchar() column or a text column.
> Which of these to use would depend on the average and max lengths of the
> memo field.
> * in vfp
> select max(len(theMemoField)) as max_len from theTable
> If' the max length is over 8000, you'll have to use text. If it's <=8000
> _and you can say with assurance_ that the max length will stay <=8000,
> I'd tend to go with a large varchar column.
>
> SQLbeginner wrote:
>
>|||Thanks very much for the helps
"SQLbeginner" wrote:
> how can I import memo field from Foxpro table'
> Many Thanks in Advance!!|||I was able to import the MEMO field using the Foxpro ODBC driver, however,
not all information from the memo fields are imported. For example, a recor
d
has 11 lines of information, it would only import part of it and some record
s
didn't even get any information transferred. On the SQL database table, I
use 'text' as the data type.
Does anyone has a solution?
Many Thanks in Advance.
Nancy
"SQLbeginner" wrote:
> how can I import memo field from Foxpro table'
> Many Thanks in Advance!!
Many Thanks in Advance!!You can transform it to a large varchar() column or a text column.
Which of these to use would depend on the average and max lengths of the
memo field.
* in vfp
select max(len(theMemoField)) as max_len from theTable
If' the max length is over 8000, you'll have to use text. If it's <=8000
_and you can say with assurance_ that the max length will stay <=8000,
I'd tend to go with a large varchar column.
SQLbeginner wrote:
>how can I import memo field from Foxpro table'
>Many Thanks in Advance!!
>|||Thanks Trey.
"Trey Walpole" wrote:
> You can transform it to a large varchar() column or a text column.
> Which of these to use would depend on the average and max lengths of the
> memo field.
> * in vfp
> select max(len(theMemoField)) as max_len from theTable
> If' the max length is over 8000, you'll have to use text. If it's <=8000
> _and you can say with assurance_ that the max length will stay <=8000,
> I'd tend to go with a large varchar column.
>
> SQLbeginner wrote:
>
>|||Thanks very much for the helps
"SQLbeginner" wrote:
> how can I import memo field from Foxpro table'
> Many Thanks in Advance!!|||I was able to import the MEMO field using the Foxpro ODBC driver, however,
not all information from the memo fields are imported. For example, a recor
d
has 11 lines of information, it would only import part of it and some record
s
didn't even get any information transferred. On the SQL database table, I
use 'text' as the data type.
Does anyone has a solution?
Many Thanks in Advance.
Nancy
"SQLbeginner" wrote:
> how can I import memo field from Foxpro table'
> Many Thanks in Advance!!
DTS Fixed Field length File Limitations
Hi,
I am trying to upload a fixed field text file to a sqlserver table using the DTS wizard. The txt file has 111 columns and the total length of a single row is 5897. The problem is when I use the wizard to specify the starting and ending of each column, its not allowing me to specify the columns beyond the position 4095.
Is there a limitation on this? if so is there a work around ? to solve this.
Any help on this is truly appreciated.
Thanks much. :)I've never encountered this problem but then I have never had a file quite that wide....
Personally what I would do is write a quick wee app or ActiveX Script to slice the file in half and then do the import in two stages... probably doesn't help much but it's the best suggestion I can give you.
I am trying to upload a fixed field text file to a sqlserver table using the DTS wizard. The txt file has 111 columns and the total length of a single row is 5897. The problem is when I use the wizard to specify the starting and ending of each column, its not allowing me to specify the columns beyond the position 4095.
Is there a limitation on this? if so is there a work around ? to solve this.
Any help on this is truly appreciated.
Thanks much. :)I've never encountered this problem but then I have never had a file quite that wide....
Personally what I would do is write a quick wee app or ActiveX Script to slice the file in half and then do the import in two stages... probably doesn't help much but it's the best suggestion I can give you.
Tuesday, March 27, 2012
DTS Fail when IDENTITY(1,1)
Hi,
i m facing this error when running DTS on IDENTITY(1,1) Field.
how can this field increment automatically ?
Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:The number of failing rows exceeds the maximum specified. (Microsoft Data Transformation Services (DTS) Data Pump (80040e21): Insert error, column 14 ('s_no', DBTYPE_I4), status 10: Integrity violation; attempt to insert NULL data or data which violates constraints.) (Microsoft OLE DB Provider for SQL Server (80040e21): Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.)
Step Error code: 8004206ADon't include any tranformation for the IDENTITY column. In other words, have DTS ignore that column and let the database engine deal with it.
-PatP|||there is a option button (check box) in your transformation task that says
"enable identity insert"
change that.|||Does the table have existing data?
Does your import file have existing "keys" (boy I hate to use that word here) that need to be imported into that column?
Does this import need to update as well as add data (or delete)?|||Hi,
Actually guys i have not run the DTS wizard to transform data i have written my own dts package that transfer hetrogenous data from one table to another.
e.g
Table1
mfg_no varchar(255)
Product_Name varchar(255)
Description varchar(255)
Table2
s_no int IDENTITY(1,1) Not Null {constraint for identity seed 1)
mfgno varchar(255)
ProductName varchar(255)
i just have to transfer data column (mfgno and product name) from Table1 to Table 2. the problem is that when i run dts its not inserting "s_no" automatically in Table 2 (i hope SQL server should handle this but its not working). if i remove constraint of identity (s_no)and make it allow Null then data transfer successfully.
i m using DTSDataPump in package to transform data.
waiting for ur reply|||Does the table have existing data?
NO
Does your import file have existing "keys" (boy I hate to use that word here) that need to be imported into that column?
NO there is no existing keys in SourceTable
Does this import need to update as well as add data (or delete)?
No it just Add data|||ctually guys i have not run the DTS wizard to transform data i have written my own dts package that transfer hetrogenous data from one table to another.That is what I thought... Just view the transform code, and remove any transformation for the IDENTITY column. It ought to be pretty simple.
-PatP|||Don't include any tranformation for the IDENTITY column. In other words, have DTS ignore that column and let the database engine deal with it.
-PatP
yes i have't include any transformation for the identity column and i m wonder why db engine not handle this :mad:|||I don't get it...
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE Table1(mfg_no varchar(255), Product_Name varchar(255), [Description] varchar(255))
CREATE TABLE Table2(s_no int IDENTITY(1,1) Not Null, mfgno varchar(255), ProductName varchar(255))
GO
INSERT INTO Table1 (mfg_no, Product_Name, [Description])
SELECT 'x002548','Gas Powered Blenders', 'The Ultimate is finally here' UNION ALL
SELECT 'x002548','Shake, rattle and Roll Mixers', 'Get the Party started' UNION ALL
SELECT 'x002548','1.75 litre sampler packages', '6 Bottles of unconciousness' UNION ALL
SELECT 'x002548','Margarita Glasses', 'Glasses, Hell you wont be able to see, son' UNION ALL
SELECT 'x002548','Hide away Car Key Chain', 'inebriation Dector - will hide your keys on you'
GO
SELECT * FROM Table1
GO
INSERT INTO Table2(mfgno, ProductName)
SELECT mfg_no, Product_Name FROM Table1
GO
SELECT * FROM Table2
GO
SET NOCOUNT ON
DROP TABLE table1
DROP TABLE table2
GO|||I don't get it...
[/code]
u run direct quries its working fine :( but if i run dts package then it fails :(|||That's what I don't get.
Why are you bothering with a DTS package at all?|||That's what I don't get.
Why are you bothering with a DTS package at all?
i want to tranfer data from one table to another on schedule basis so that i need a DTS package that tranfer only those column data that i want to tranfer.
whole dts working fine its transform the desired column to anothe table but when identiy column is removed cuz destination table has a unique column type int and identity seed 1.
:( this is sample code u can run it . if u remove constraint on s_no it will work fine. try this
CREATE TABLE [DTS_UE].[dbo].[NorthwindProducts] (
[s_no] [int] IDENTITY(1,1) Not NULL ,
[ProductName] [nvarchar] (40) NULL ,
[CategoryName] [nvarchar] (25) NULL ,
[CompanyName] [nvarchar] (40) NULL )
This is the Visual Basic code for the application:
Public Sub Main()
'Copy Northwind..Products names, categories, suppliers to DTS_UE..NorthwindProducts.
Dim objPackage As DTS.Package2
Dim objConnect As DTS.Connection2
Dim objStep As DTS.Step2
Dim objTask As DTS.Task
Dim objPumpTask As DTS.DataPumpTask2
Dim objTransform As DTS.Transformation2
Dim objLookUp As DTS.Lookup
Dim objTranScript As DTSPump.DTSTransformScriptProperties2
Dim sVBS As String 'VBScript text
Set objPackage = New DTS.Package
objPackage.FailOnError = True
objPackage.LogFileName = "C:\Temp\TestConcurrent.Log"
'Establish connections to data source and destination.
Set objConnect = objPackage.Connections.New("SQLOLEDB.1")
With objConnect
.ID = 1
.DataSource = "(local)"
.UseTrustedConnection = True
End With
objPackage.Connections.Add objConnect
Set objConnect = objPackage.Connections.New("SQLOLEDB.1")
With objConnect
.ID = 2
.DataSource = "(local)"
.UseTrustedConnection = True
End With
objPackage.Connections.Add objConnect
'Create copy step and task, link step to task.
Set objStep = objPackage.Steps.New
objStep.Name = "NorthwindProductsStep"
Set objTask = objPackage.Tasks.New("DTSDataPumpTask")
Set objPumpTask = objTask.CustomTask
objPumpTask.Name = "NorthwindProductsTask"
objStep.TaskName = objPumpTask.Name
objStep.ExecuteInMainThread = False
objPackage.Steps.Add objStep
'Link copy task to connections.
With objPumpTask
.SourceConnectionID = 1
.SourceSQLStatement = _
"SELECT ProductName, CategoryID, SupplierID " & _
"FROM Northwind..Products"
.DestinationConnectionID = 2
.DestinationObjectName = "[DTS_UE].[dbo].[NorthwindProducts]"
.UseFastLoad = False
.MaximumErrorCount = 99
End With
'Create lookups for supplier and category.
Set objLookUp = objPumpTask.Lookups.New("CategoryLU")
With objLookUp
.ConnectionID = 1
.Query = "SELECT CategoryName FROM Northwind..Categories " & _
"WHERE CategoryID = ? "
.MaxCacheRows = 0
End With
objPumpTask.Lookups.Add objLookUp
Set objLookUp = objPumpTask.Lookups.New("SupplierLU")
With objLookUp
.ConnectionID = 1
.Query = "SELECT CompanyName FROM Northwind..Suppliers " & _
"WHERE SupplierID = ? "
.MaxCacheRows = 0
End With
objPumpTask.Lookups.Add objLookUp
'Create and initialize rowcount and completion global variables.
objPackage.GlobalVariables.AddGlobalVariable "Copy Complete", False
objPackage.GlobalVariables.AddGlobalVariable "Rows Copied", 0
objPackage.ExplicitGlobalVariables = True
'Create transform to copy row, signal completion.
Set objTransform = objPumpTask.Transformations. _
New("DTSPump.DataPumpTransformScript")
With objTransform
.Name = "CopyNorthwindProducts"
.TransformPhases = DTSTransformPhase_Transform + _
DTSTransformPhase_OnPumpComplete
Set objTranScript = .TransformServer
End With
With objTranScript
.FunctionEntry = "CopyColumns"
.PumpCompleteFunctionEntry = "PumpComplete"
.Language = "VBScript"
sVBS = "Option Explicit" & vbCrLf
sVBS = sVBS & "Function CopyColumns()" & vbCrLf
sVBS = sVBS & " DTSDestination(""ProductName"") = DTSSource(""ProductName"") " & vbCrLf
sVBS = sVBS & " DTSDestination(""CategoryName"") = DTSLookups(""CategoryLU"").Execute(DTSSource(""CategoryID"")) " & vbCrLf
sVBS = sVBS & " DTSDestination(""CompanyName"") = DTSLookups(""SupplierLU"").Execute(DTSSource(""SupplierID"").Value) " & vbCrLf
sVBS = sVBS & " DTSGlobalVariables(""Rows Copied"") = CLng(DTSTransformPhaseInfo.CurrentSourceRow)" & vbCrLf
sVBS = sVBS & " CopyColumns = DTSTransformStat_OK" & vbCrLf
sVBS = sVBS & "End Function" & vbCrLf
sVBS = sVBS & "Function PumpComplete()" & vbCrLf
sVBS = sVBS & " DTSGlobalVariables(""Copy Complete"") = True" & vbCrLf
sVBS = sVBS & " PumpComplete = DTSTransformStat_OK" & vbCrLf
sVBS = sVBS & "End Function" & vbCrLf
.Text = sVBS
End With
objPumpTask.Transformations.Add objTransform
objPackage.Tasks.Add objTask
objPackage.Execute
End Sub
i m facing this error when running DTS on IDENTITY(1,1) Field.
how can this field increment automatically ?
Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:The number of failing rows exceeds the maximum specified. (Microsoft Data Transformation Services (DTS) Data Pump (80040e21): Insert error, column 14 ('s_no', DBTYPE_I4), status 10: Integrity violation; attempt to insert NULL data or data which violates constraints.) (Microsoft OLE DB Provider for SQL Server (80040e21): Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.)
Step Error code: 8004206ADon't include any tranformation for the IDENTITY column. In other words, have DTS ignore that column and let the database engine deal with it.
-PatP|||there is a option button (check box) in your transformation task that says
"enable identity insert"
change that.|||Does the table have existing data?
Does your import file have existing "keys" (boy I hate to use that word here) that need to be imported into that column?
Does this import need to update as well as add data (or delete)?|||Hi,
Actually guys i have not run the DTS wizard to transform data i have written my own dts package that transfer hetrogenous data from one table to another.
e.g
Table1
mfg_no varchar(255)
Product_Name varchar(255)
Description varchar(255)
Table2
s_no int IDENTITY(1,1) Not Null {constraint for identity seed 1)
mfgno varchar(255)
ProductName varchar(255)
i just have to transfer data column (mfgno and product name) from Table1 to Table 2. the problem is that when i run dts its not inserting "s_no" automatically in Table 2 (i hope SQL server should handle this but its not working). if i remove constraint of identity (s_no)and make it allow Null then data transfer successfully.
i m using DTSDataPump in package to transform data.
waiting for ur reply|||Does the table have existing data?
NO
Does your import file have existing "keys" (boy I hate to use that word here) that need to be imported into that column?
NO there is no existing keys in SourceTable
Does this import need to update as well as add data (or delete)?
No it just Add data|||ctually guys i have not run the DTS wizard to transform data i have written my own dts package that transfer hetrogenous data from one table to another.That is what I thought... Just view the transform code, and remove any transformation for the IDENTITY column. It ought to be pretty simple.
-PatP|||Don't include any tranformation for the IDENTITY column. In other words, have DTS ignore that column and let the database engine deal with it.
-PatP
yes i have't include any transformation for the identity column and i m wonder why db engine not handle this :mad:|||I don't get it...
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE Table1(mfg_no varchar(255), Product_Name varchar(255), [Description] varchar(255))
CREATE TABLE Table2(s_no int IDENTITY(1,1) Not Null, mfgno varchar(255), ProductName varchar(255))
GO
INSERT INTO Table1 (mfg_no, Product_Name, [Description])
SELECT 'x002548','Gas Powered Blenders', 'The Ultimate is finally here' UNION ALL
SELECT 'x002548','Shake, rattle and Roll Mixers', 'Get the Party started' UNION ALL
SELECT 'x002548','1.75 litre sampler packages', '6 Bottles of unconciousness' UNION ALL
SELECT 'x002548','Margarita Glasses', 'Glasses, Hell you wont be able to see, son' UNION ALL
SELECT 'x002548','Hide away Car Key Chain', 'inebriation Dector - will hide your keys on you'
GO
SELECT * FROM Table1
GO
INSERT INTO Table2(mfgno, ProductName)
SELECT mfg_no, Product_Name FROM Table1
GO
SELECT * FROM Table2
GO
SET NOCOUNT ON
DROP TABLE table1
DROP TABLE table2
GO|||I don't get it...
[/code]
u run direct quries its working fine :( but if i run dts package then it fails :(|||That's what I don't get.
Why are you bothering with a DTS package at all?|||That's what I don't get.
Why are you bothering with a DTS package at all?
i want to tranfer data from one table to another on schedule basis so that i need a DTS package that tranfer only those column data that i want to tranfer.
whole dts working fine its transform the desired column to anothe table but when identiy column is removed cuz destination table has a unique column type int and identity seed 1.
:( this is sample code u can run it . if u remove constraint on s_no it will work fine. try this
CREATE TABLE [DTS_UE].[dbo].[NorthwindProducts] (
[s_no] [int] IDENTITY(1,1) Not NULL ,
[ProductName] [nvarchar] (40) NULL ,
[CategoryName] [nvarchar] (25) NULL ,
[CompanyName] [nvarchar] (40) NULL )
This is the Visual Basic code for the application:
Public Sub Main()
'Copy Northwind..Products names, categories, suppliers to DTS_UE..NorthwindProducts.
Dim objPackage As DTS.Package2
Dim objConnect As DTS.Connection2
Dim objStep As DTS.Step2
Dim objTask As DTS.Task
Dim objPumpTask As DTS.DataPumpTask2
Dim objTransform As DTS.Transformation2
Dim objLookUp As DTS.Lookup
Dim objTranScript As DTSPump.DTSTransformScriptProperties2
Dim sVBS As String 'VBScript text
Set objPackage = New DTS.Package
objPackage.FailOnError = True
objPackage.LogFileName = "C:\Temp\TestConcurrent.Log"
'Establish connections to data source and destination.
Set objConnect = objPackage.Connections.New("SQLOLEDB.1")
With objConnect
.ID = 1
.DataSource = "(local)"
.UseTrustedConnection = True
End With
objPackage.Connections.Add objConnect
Set objConnect = objPackage.Connections.New("SQLOLEDB.1")
With objConnect
.ID = 2
.DataSource = "(local)"
.UseTrustedConnection = True
End With
objPackage.Connections.Add objConnect
'Create copy step and task, link step to task.
Set objStep = objPackage.Steps.New
objStep.Name = "NorthwindProductsStep"
Set objTask = objPackage.Tasks.New("DTSDataPumpTask")
Set objPumpTask = objTask.CustomTask
objPumpTask.Name = "NorthwindProductsTask"
objStep.TaskName = objPumpTask.Name
objStep.ExecuteInMainThread = False
objPackage.Steps.Add objStep
'Link copy task to connections.
With objPumpTask
.SourceConnectionID = 1
.SourceSQLStatement = _
"SELECT ProductName, CategoryID, SupplierID " & _
"FROM Northwind..Products"
.DestinationConnectionID = 2
.DestinationObjectName = "[DTS_UE].[dbo].[NorthwindProducts]"
.UseFastLoad = False
.MaximumErrorCount = 99
End With
'Create lookups for supplier and category.
Set objLookUp = objPumpTask.Lookups.New("CategoryLU")
With objLookUp
.ConnectionID = 1
.Query = "SELECT CategoryName FROM Northwind..Categories " & _
"WHERE CategoryID = ? "
.MaxCacheRows = 0
End With
objPumpTask.Lookups.Add objLookUp
Set objLookUp = objPumpTask.Lookups.New("SupplierLU")
With objLookUp
.ConnectionID = 1
.Query = "SELECT CompanyName FROM Northwind..Suppliers " & _
"WHERE SupplierID = ? "
.MaxCacheRows = 0
End With
objPumpTask.Lookups.Add objLookUp
'Create and initialize rowcount and completion global variables.
objPackage.GlobalVariables.AddGlobalVariable "Copy Complete", False
objPackage.GlobalVariables.AddGlobalVariable "Rows Copied", 0
objPackage.ExplicitGlobalVariables = True
'Create transform to copy row, signal completion.
Set objTransform = objPumpTask.Transformations. _
New("DTSPump.DataPumpTransformScript")
With objTransform
.Name = "CopyNorthwindProducts"
.TransformPhases = DTSTransformPhase_Transform + _
DTSTransformPhase_OnPumpComplete
Set objTranScript = .TransformServer
End With
With objTranScript
.FunctionEntry = "CopyColumns"
.PumpCompleteFunctionEntry = "PumpComplete"
.Language = "VBScript"
sVBS = "Option Explicit" & vbCrLf
sVBS = sVBS & "Function CopyColumns()" & vbCrLf
sVBS = sVBS & " DTSDestination(""ProductName"") = DTSSource(""ProductName"") " & vbCrLf
sVBS = sVBS & " DTSDestination(""CategoryName"") = DTSLookups(""CategoryLU"").Execute(DTSSource(""CategoryID"")) " & vbCrLf
sVBS = sVBS & " DTSDestination(""CompanyName"") = DTSLookups(""SupplierLU"").Execute(DTSSource(""SupplierID"").Value) " & vbCrLf
sVBS = sVBS & " DTSGlobalVariables(""Rows Copied"") = CLng(DTSTransformPhaseInfo.CurrentSourceRow)" & vbCrLf
sVBS = sVBS & " CopyColumns = DTSTransformStat_OK" & vbCrLf
sVBS = sVBS & "End Function" & vbCrLf
sVBS = sVBS & "Function PumpComplete()" & vbCrLf
sVBS = sVBS & " DTSGlobalVariables(""Copy Complete"") = True" & vbCrLf
sVBS = sVBS & " PumpComplete = DTSTransformStat_OK" & vbCrLf
sVBS = sVBS & "End Function" & vbCrLf
.Text = sVBS
End With
objPumpTask.Transformations.Add objTransform
objPackage.Tasks.Add objTask
objPackage.Execute
End Sub
Thursday, March 22, 2012
DTS doesnt recognize CRLF in Text File
I'm using DTS to import a text file (fixed field). In my sample data, I have 21 rows of data.
DTS only brings in 15 or so, because it fails to recognize the CRLF on some of the rows, and just treats it and the subsequent row as part of the previous row.
The text file is being produced via FTP from an IBM Iseries. It imports just fine into Notepad and Excel.
Anyone have any ideas why DTS would have trouble with this ?
Thanks
Gregnevermind...apparently DTS requires flat text files to be padded with blanks in order to make all the records exactly the same length. I don't know why DTS is so picky about this when every other Micro$oft application isn't.
I reckon I'll just make it comma delimited and be done with it.
DTS only brings in 15 or so, because it fails to recognize the CRLF on some of the rows, and just treats it and the subsequent row as part of the previous row.
The text file is being produced via FTP from an IBM Iseries. It imports just fine into Notepad and Excel.
Anyone have any ideas why DTS would have trouble with this ?
Thanks
Gregnevermind...apparently DTS requires flat text files to be padded with blanks in order to make all the records exactly the same length. I don't know why DTS is so picky about this when every other Micro$oft application isn't.
I reckon I'll just make it comma delimited and be done with it.
DTS Date Problem
I have a DTS package that imports from a text file into SQL 2k SP3. I am trying to insert a field (along with the imported fields) during the DTS execution that will be the current date. Does anyone know of a way to do this? I really didn't think I should have to schedule a seperate job to UPDATE each record with the current date...
Make sense?Make a datetime column in the imported table default to getdate().|||Originally posted by joejcheng
Make a datetime column in the imported table default to getdate().
hehe, thanks. That's what I was trying to do, but for some stupid reason I wasn't putting the () on the end of getdate.
Thanks
Make sense?Make a datetime column in the imported table default to getdate().|||Originally posted by joejcheng
Make a datetime column in the imported table default to getdate().
hehe, thanks. That's what I was trying to do, but for some stupid reason I wasn't putting the () on the end of getdate.
Thanks
Sunday, March 11, 2012
DTS and decimal issue
I am importing a table from our ERP system using a DTS. THe table imports fine. The only issue that I am having is that a decimal field in our ERP system can be for example 105.1275, but when it gets inported to sql the number is 105.1270.
What could possibly be the issue and sollution for this. All help is appreciated.Ummm... what's the source platform of the data and the destination platform?
That'll learn you for using DT...uh...I can't say it...
Did you look at the transformation? Waht's the DDL of the source and destination look like?|||Check that the precision/length settings for the destination field is correct.
What could possibly be the issue and sollution for this. All help is appreciated.Ummm... what's the source platform of the data and the destination platform?
That'll learn you for using DT...uh...I can't say it...
Did you look at the transformation? Waht's the DDL of the source and destination look like?|||Check that the precision/length settings for the destination field is correct.
dts and bit fields
sql2k sp2
If I dts data from a table to a .txt file all of the data
in the bit field gets turned into a "true" or "false"
value. Is there a way to turn this setting off?
TIA, ChrisDid not test, but
turn the column to allow null, then when dts, do not load the column.
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:014401c3c995$94930900$a501280a@.phx.gbl...
> sql2k sp2
> If I dts data from a table to a .txt file all of the data
> in the bit field gets turned into a "true" or "false"
> value. Is there a way to turn this setting off?
> TIA, Chris|||DTS a query or view rather than the base table.
In the query or view, use CONVERT or CAST to cast the bit column to integer
(or what you want).
James Hokes
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:014401c3c995$94930900$a501280a@.phx.gbl...
> sql2k sp2
> If I dts data from a table to a .txt file all of the data
> in the bit field gets turned into a "true" or "false"
> value. Is there a way to turn this setting off?
> TIA, Chris
If I dts data from a table to a .txt file all of the data
in the bit field gets turned into a "true" or "false"
value. Is there a way to turn this setting off?
TIA, ChrisDid not test, but
turn the column to allow null, then when dts, do not load the column.
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:014401c3c995$94930900$a501280a@.phx.gbl...
> sql2k sp2
> If I dts data from a table to a .txt file all of the data
> in the bit field gets turned into a "true" or "false"
> value. Is there a way to turn this setting off?
> TIA, Chris|||DTS a query or view rather than the base table.
In the query or view, use CONVERT or CAST to cast the bit column to integer
(or what you want).
James Hokes
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:014401c3c995$94930900$a501280a@.phx.gbl...
> sql2k sp2
> If I dts data from a table to a .txt file all of the data
> in the bit field gets turned into a "true" or "false"
> value. Is there a way to turn this setting off?
> TIA, Chris
Wednesday, March 7, 2012
DTS "Can Not Insert Null Value"
Hi,
I'm using a DTS package to import data from csv files, the destiantion
table however has a unique primary key field that can not be null, this
data is not in the CSV file.
I'm using an ActiveX transformation, and I've used the following code
to add a number to the ID field:
Function Main()
if isEmpty(N) then
N = 0
end if
N = N+1
DTSDestination("CDR_ID") = N
Main = DTSTransformStat_OK
End Function
However the problem with this is that it needs to start at whatever the
LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
x+3 where X is the previously highest ID.
I was hoping that SQL server would generate the ID field if I didnt put
one in, but alas it was not to be.
Thanks is advance for any help.
Matt.With the unique PK have you got auto identity set up ?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
<Matt.Mawdsley@.gmail.com> wrote in message
news:1146128307.625814.307880@.e56g2000cwe.googlegroups.com...
> Hi,
> I'm using a DTS package to import data from csv files, the destiantion
> table however has a unique primary key field that can not be null, this
> data is not in the CSV file.
> I'm using an ActiveX transformation, and I've used the following code
> to add a number to the ID field:
> Function Main()
> if isEmpty(N) then
> N = 0
> end if
> N = N+1
> DTSDestination("CDR_ID") = N
> Main = DTSTransformStat_OK
> End Function
> However the problem with this is that it needs to start at whatever the
> LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
> x+3 where X is the previously highest ID.
> I was hoping that SQL server would generate the ID field if I didnt put
> one in, but alas it was not to be.
> Thanks is advance for any help.
> Matt.
>|||Jack,
I don't belive I do, where is this set? and how? =)
Thanks,
Matt.
I'm using a DTS package to import data from csv files, the destiantion
table however has a unique primary key field that can not be null, this
data is not in the CSV file.
I'm using an ActiveX transformation, and I've used the following code
to add a number to the ID field:
Function Main()
if isEmpty(N) then
N = 0
end if
N = N+1
DTSDestination("CDR_ID") = N
Main = DTSTransformStat_OK
End Function
However the problem with this is that it needs to start at whatever the
LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
x+3 where X is the previously highest ID.
I was hoping that SQL server would generate the ID field if I didnt put
one in, but alas it was not to be.
Thanks is advance for any help.
Matt.With the unique PK have you got auto identity set up ?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
<Matt.Mawdsley@.gmail.com> wrote in message
news:1146128307.625814.307880@.e56g2000cwe.googlegroups.com...
> Hi,
> I'm using a DTS package to import data from csv files, the destiantion
> table however has a unique primary key field that can not be null, this
> data is not in the CSV file.
> I'm using an ActiveX transformation, and I've used the following code
> to add a number to the ID field:
> Function Main()
> if isEmpty(N) then
> N = 0
> end if
> N = N+1
> DTSDestination("CDR_ID") = N
> Main = DTSTransformStat_OK
> End Function
> However the problem with this is that it needs to start at whatever the
> LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
> x+3 where X is the previously highest ID.
> I was hoping that SQL server would generate the ID field if I didnt put
> one in, but alas it was not to be.
> Thanks is advance for any help.
> Matt.
>|||Jack,
I don't belive I do, where is this set? and how? =)
Thanks,
Matt.
DTS "Can Not Insert Null Value"
Hi,
I'm using a DTS package to import data from csv files, the destiantion
table however has a unique primary key field that can not be null, this
data is not in the CSV file.
I'm using an ActiveX transformation, and I've used the following code
to add a number to the ID field:
Function Main()
if isEmpty(N) then
N = 0
end if
N = N+1
DTSDestination("CDR_ID") = N
Main = DTSTransformStat_OK
End Function
However the problem with this is that it needs to start at whatever the
LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
x+3 where X is the previously highest ID.
I was hoping that SQL server would generate the ID field if I didnt put
one in, but alas it was not to be.
Thanks is advance for any help.
Matt.With the unique PK have you got auto identity set up ?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
<Matt.Mawdsley@.gmail.com> wrote in message
news:1146128307.625814.307880@.e56g2000cwe.googlegroups.com...
> Hi,
> I'm using a DTS package to import data from csv files, the destiantion
> table however has a unique primary key field that can not be null, this
> data is not in the CSV file.
> I'm using an ActiveX transformation, and I've used the following code
> to add a number to the ID field:
> Function Main()
> if isEmpty(N) then
> N = 0
> end if
> N = N+1
> DTSDestination("CDR_ID") = N
> Main = DTSTransformStat_OK
> End Function
> However the problem with this is that it needs to start at whatever the
> LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
> x+3 where X is the previously highest ID.
> I was hoping that SQL server would generate the ID field if I didnt put
> one in, but alas it was not to be.
> Thanks is advance for any help.
> Matt.
>|||Jack,
I don't belive I do, where is this set? and how? =)
Thanks,
Matt.
I'm using a DTS package to import data from csv files, the destiantion
table however has a unique primary key field that can not be null, this
data is not in the CSV file.
I'm using an ActiveX transformation, and I've used the following code
to add a number to the ID field:
Function Main()
if isEmpty(N) then
N = 0
end if
N = N+1
DTSDestination("CDR_ID") = N
Main = DTSTransformStat_OK
End Function
However the problem with this is that it needs to start at whatever the
LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
x+3 where X is the previously highest ID.
I was hoping that SQL server would generate the ID field if I didnt put
one in, but alas it was not to be.
Thanks is advance for any help.
Matt.With the unique PK have you got auto identity set up ?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
<Matt.Mawdsley@.gmail.com> wrote in message
news:1146128307.625814.307880@.e56g2000cwe.googlegroups.com...
> Hi,
> I'm using a DTS package to import data from csv files, the destiantion
> table however has a unique primary key field that can not be null, this
> data is not in the CSV file.
> I'm using an ActiveX transformation, and I've used the following code
> to add a number to the ID field:
> Function Main()
> if isEmpty(N) then
> N = 0
> end if
> N = N+1
> DTSDestination("CDR_ID") = N
> Main = DTSTransformStat_OK
> End Function
> However the problem with this is that it needs to start at whatever the
> LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
> x+3 where X is the previously highest ID.
> I was hoping that SQL server would generate the ID field if I didnt put
> one in, but alas it was not to be.
> Thanks is advance for any help.
> Matt.
>|||Jack,
I don't belive I do, where is this set? and how? =)
Thanks,
Matt.
DTS - Send records to different text file - DTS
Hi all,
I am designing a DTS package instead of SSIS.
I have a text file and already uploaded to tableA, there is a field named NameID in tableA. The field NameID should match the NameID in tableB and update other fields of tableA, the non-match records will generate another exception text file.
How can i implatement this in DTS? Which task or tech?
thanks
Micror
You'll need to ask this in the DTS forum. http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srgDTS - Problem with Image field
DTS is not properly exporting a database between the servers when a table is containing a image field
Reason it gives as
::Error::
Transfer Status: Transferring Data: (Table '[dbo].[Here comes the table name used ]')
Data truncation occurred in table [dbo].[Here comes the table name used ], column 1.
any workaround or solutions would be more appericated
any help would be more appericated and important to find a solution
|||
Maybe there is some image data larger than 8388602 bytes being transfered using Copy SQL Server Objects task, in this case try Import/Export Wizard instead.
And there are some other considerations you should take when using DTS to transfer BLOB data (image, text, ntext), please take a look at:
Data Conversion and Transformation Considerations
|||
that is excatly the problem , DTS truncates BLOB data and shows the error.
But how to resolve it , Iam using sql server 2000 and win xp in local server.
is BLOB data is still a issue in sql server 2000 or it is solved.
|||If you use Import/Export wizard to transfer the BOLB data (you can also save this taks to DTS package), will the same error be raised?|||
Import/Export Wizard will go to DTS package only .
Ok.
What is the command to find the sql sever installed in the system and the service pack?
Help would be appericated in getting the command
|||
Try T-SQL command:
SELECT @.@.VERSION
|||
It is showing in the sql query analyser
I wanted the command to be executed in the command prompt
and how to know the version of sp installed in the system
|||Then how about to exeute the T-SQL command in osql in command prompt
|||
Can u give me the exact command.
What is the star which appears on left side along the thread
|||
Ganesh@.Nilgris:
Can u give me the exact command.
osql is a command-line utility which allows you to execute T-SQL commands
C:\Documents and Settings\Administrator>osql -E
1> select @.@.version
2> go
-----------------
---------------
---------------
---------------
--
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
Apr 14 2006 01:12:25
C
opyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Wind
ows NT 5.2 (Build 3790: Service Pack 1)
(1 row affected)
|||
Thats was a good one, It was usefull in learinig sql commands in cmd prompt
But how do u still find the service pack .
DTS - Looping in a Data DrivenQuery
Hi Guys
I am reading a table one record at a time. Within this record a field can contain multiple values.
The delimiter is a ^. The data comes from a Pick legacy system
The data looks like this :-
chargeable_item_cd quantity text_1
I77C1^I77C2 1^1 PLATES /SCREWS^1.3MM SET
I want to extract the multiple values from this field and insert a record for each set of values.
I can unravel the data easy enough. The problem I have is how to loop within a DTS Activex
Script to store each of the values extracted from the field before moving onto the next record.
Is it possible or am I better of using a SQL task an taking a fraction of the time. (I am resisting
this as my boss doesnt like SQL code)
I am doing this within a data driven query.
Thanks in advance.
Cheers
Chris
The code I have so far looks like this(but doesnt work). It gives a "No query specification returnedby transform status".
'************************************************* **********
' Visual Basic Transformation Script
'************************************************* **********
' Copy each source column to the destination column
Function Main()
DTSDestination("DHB_Key") = DTSLookups("DHB Lookup").Execute(DTSGlobalVariables("DHB_Code").Value)
DTSDestination("Health_Encounter_Theatre_Key") = DTSSource("rule_violtd_cd")
DTSDestination("Patient_Key") = DTSLookups("Patient Lookup").Execute(left(DTSSource("admit_id"), 7))
DTSDestination("Patient_Care_Episode_Key") = _
DTSLookups("Patient Care Lookup").Execute( DTSDestination("Patient_Key"), _
"*T" + DTSSource("hosp_cd") + DTSSource("scout_sheet_nbr"), _
DTSDestination("DHB_Key"))
DTSDestination("Health_Encounter_Key") = DTSLookups("Hlth Encntr Lookup").Execute( DTSDestination("DHB_Key"), _
DTSDestination("Patient_Key"), _
DTSDestination("Patient_Care_Episode_Key"), _
"TH")
' This piece of code does it for multi field values for charagble items to individual values that can be stored in the database
' Copy each source column to the destination column
DIM string1, string2, string3, sitem, sqty, sdescript, quantity, descript
string1 = DTSSource("chargeable_item_cd")
string2 = DTSSource("quantity")
Do While InStr( string1 , "^") > 0
sitem = left(string1, InStr( string1 , "^") -1 )
sqty = left(string2, InStr( string2 , "^") -1 )
sdescript = left(string3, InStr( string3 , "^") -1 )
DTSDestination("Chargeable_Items_Key") = DTSLookups("Chargeable Items Lookup").Execute(sitem)
DTSDestination("Quantity") = sqty
DTSDestination("Description_Of_Item") = sdescript
If IsNull(sqty) Then
quantity = 0
Else
quantity = sqty
End If
If IsNull(sdescript) Then
descript = "X"
Else
descript = sdescript
End If
If NOT IsNull(DTSDestination("Chargeable_Items_Key")) Then
If DTSLookups("Item Exists Lookup").Execute(DTSDestination("DHB_Key"),_
DTSDestination("Health_Encounter_Theatre_Key"),_
DTSDestination("Patient_Key"),_
DTSDestination("Patient_Care_Episode_Key"),_
DTSDestination("Health_Encounter_Key"),_
DTSDestination("Chargeable_Items_Key"),_
quantity,_
descript) = 0 Then
Main = DTSTransformstat_InsertQuery
Else
Main = DTSTransformStat_SkipRow
End If
Main = DTSTransformStat_OK
End If
string1 = Mid( string1 , InStr( string1 , "^") + 1, Len( string1 ) )
string2 = Mid( string2 , InStr( string2 , "^") + 1, Len( string2 ) )
string3 = Mid( string3 , InStr( string3 , "^") + 1, Len( string3 ) )
loop
Main = DTSTransformStat_OK
End Functionokie, this is what I would do...
write 2 sub routines......
Sub InsertItems(strItems)
Dim arrItems, iItem
arrItems = split(strItems,"^")
for iItem = 0 to uBound(arrItems)
ExecuteSQL("insert into itemTable (column) values (" & arrItems(iItem) & ")"
next
End Sub
Sub ExecuteSQL(strSQL)
On Error Resume Next
Dim oConn, oRs, strConn
Set oConn = CreateObject("ADODB.Connection")
Set oRs = CreateObject("ADODB.Recordset")
strConn = "Your connection string goes here"
oConn.ConnectionString = strConn
oConn.ConnectionTimeout = 30
oConn.Open
oConn.execute(stqSQL
set oConn = nothing
On Error Goto 0
End Sub
in your main function call InsertItems and pass it the value of chargeable_item_cd (assuming I got the name right)
Make sense??|||Hi Rokslide
I take it that this will work inside the Activex Script Transformation Properties portion of a data driven query.
It is nearly knock off time Friday afternoon in NZ so I will try it on Monday morning
.
Cheers
Chris|||Hi Chris,
Your Activex Script can have as many extra functions or sub routines as you like with no problems providing there is only 1 main function.
Nearly knowing off time?? It's only 2pm! ;) It is Friday though and the lead in to a long weekend so... ;)
Where are you working in Christchurch? I used to live and work there myself. :)|||Hi
I am based at Princess Margret Hospital by under the Port Hills but
I live way out in the country about 10 kms the other side of Rangiora
on a 52 acre farmlet.
Cheers|||Cool. :) I used to work for the Council there and then started a company with a friend. Before living in Christchurch I was living out in Cheviot.
Mmmmm country air... ;)
Let me know if you need any more help with that DTS package.
I am reading a table one record at a time. Within this record a field can contain multiple values.
The delimiter is a ^. The data comes from a Pick legacy system
The data looks like this :-
chargeable_item_cd quantity text_1
I77C1^I77C2 1^1 PLATES /SCREWS^1.3MM SET
I want to extract the multiple values from this field and insert a record for each set of values.
I can unravel the data easy enough. The problem I have is how to loop within a DTS Activex
Script to store each of the values extracted from the field before moving onto the next record.
Is it possible or am I better of using a SQL task an taking a fraction of the time. (I am resisting
this as my boss doesnt like SQL code)
I am doing this within a data driven query.
Thanks in advance.
Cheers
Chris
The code I have so far looks like this(but doesnt work). It gives a "No query specification returnedby transform status".
'************************************************* **********
' Visual Basic Transformation Script
'************************************************* **********
' Copy each source column to the destination column
Function Main()
DTSDestination("DHB_Key") = DTSLookups("DHB Lookup").Execute(DTSGlobalVariables("DHB_Code").Value)
DTSDestination("Health_Encounter_Theatre_Key") = DTSSource("rule_violtd_cd")
DTSDestination("Patient_Key") = DTSLookups("Patient Lookup").Execute(left(DTSSource("admit_id"), 7))
DTSDestination("Patient_Care_Episode_Key") = _
DTSLookups("Patient Care Lookup").Execute( DTSDestination("Patient_Key"), _
"*T" + DTSSource("hosp_cd") + DTSSource("scout_sheet_nbr"), _
DTSDestination("DHB_Key"))
DTSDestination("Health_Encounter_Key") = DTSLookups("Hlth Encntr Lookup").Execute( DTSDestination("DHB_Key"), _
DTSDestination("Patient_Key"), _
DTSDestination("Patient_Care_Episode_Key"), _
"TH")
' This piece of code does it for multi field values for charagble items to individual values that can be stored in the database
' Copy each source column to the destination column
DIM string1, string2, string3, sitem, sqty, sdescript, quantity, descript
string1 = DTSSource("chargeable_item_cd")
string2 = DTSSource("quantity")
Do While InStr( string1 , "^") > 0
sitem = left(string1, InStr( string1 , "^") -1 )
sqty = left(string2, InStr( string2 , "^") -1 )
sdescript = left(string3, InStr( string3 , "^") -1 )
DTSDestination("Chargeable_Items_Key") = DTSLookups("Chargeable Items Lookup").Execute(sitem)
DTSDestination("Quantity") = sqty
DTSDestination("Description_Of_Item") = sdescript
If IsNull(sqty) Then
quantity = 0
Else
quantity = sqty
End If
If IsNull(sdescript) Then
descript = "X"
Else
descript = sdescript
End If
If NOT IsNull(DTSDestination("Chargeable_Items_Key")) Then
If DTSLookups("Item Exists Lookup").Execute(DTSDestination("DHB_Key"),_
DTSDestination("Health_Encounter_Theatre_Key"),_
DTSDestination("Patient_Key"),_
DTSDestination("Patient_Care_Episode_Key"),_
DTSDestination("Health_Encounter_Key"),_
DTSDestination("Chargeable_Items_Key"),_
quantity,_
descript) = 0 Then
Main = DTSTransformstat_InsertQuery
Else
Main = DTSTransformStat_SkipRow
End If
Main = DTSTransformStat_OK
End If
string1 = Mid( string1 , InStr( string1 , "^") + 1, Len( string1 ) )
string2 = Mid( string2 , InStr( string2 , "^") + 1, Len( string2 ) )
string3 = Mid( string3 , InStr( string3 , "^") + 1, Len( string3 ) )
loop
Main = DTSTransformStat_OK
End Functionokie, this is what I would do...
write 2 sub routines......
Sub InsertItems(strItems)
Dim arrItems, iItem
arrItems = split(strItems,"^")
for iItem = 0 to uBound(arrItems)
ExecuteSQL("insert into itemTable (column) values (" & arrItems(iItem) & ")"
next
End Sub
Sub ExecuteSQL(strSQL)
On Error Resume Next
Dim oConn, oRs, strConn
Set oConn = CreateObject("ADODB.Connection")
Set oRs = CreateObject("ADODB.Recordset")
strConn = "Your connection string goes here"
oConn.ConnectionString = strConn
oConn.ConnectionTimeout = 30
oConn.Open
oConn.execute(stqSQL
set oConn = nothing
On Error Goto 0
End Sub
in your main function call InsertItems and pass it the value of chargeable_item_cd (assuming I got the name right)
Make sense??|||Hi Rokslide
I take it that this will work inside the Activex Script Transformation Properties portion of a data driven query.
It is nearly knock off time Friday afternoon in NZ so I will try it on Monday morning
.
Cheers
Chris|||Hi Chris,
Your Activex Script can have as many extra functions or sub routines as you like with no problems providing there is only 1 main function.
Nearly knowing off time?? It's only 2pm! ;) It is Friday though and the lead in to a long weekend so... ;)
Where are you working in Christchurch? I used to live and work there myself. :)|||Hi
I am based at Princess Margret Hospital by under the Port Hills but
I live way out in the country about 10 kms the other side of Rangiora
on a 52 acre farmlet.
Cheers|||Cool. :) I used to work for the Council there and then started a company with a friend. Before living in Christchurch I was living out in Cheviot.
Mmmmm country air... ;)
Let me know if you need any more help with that DTS package.
Friday, February 17, 2012
DT_TEXT not supported while importing from an XML file?
I have an XML file that contains a field that has over 8000 characters in it. I cannot use a (n)varchar for it, I must use a Text for it (although as a last resort I could split the string into several (n)varchar columns). I want to set the external column of the XML column to a DT_TEXT and I receive and error message that states :
Error at Import XFFD Data [xffd [1]]: The SSIS Data Flow Task data type "DT_TEXT" on the external metadata column "reviewText" (32411) is not supported for the component "xffd" (1).
I've tried converting the nvarchar into a text stream with the use of a Data Conversion Transform, but I think the Validating steps are truncating the field.
HELP. I've been beating my head against this for a couple hours a day.
Thanks,
Scott
The XmlSrc only supports Unicode text. Set the output column type of the XML source to DT_NTEXT. If you need it to be DT_TEXT downstream, you would then need to use a data conversion transform to convert to DT_TEXT.
Thanks
Mark
Subscribe to:
Posts (Atom)