Converting DataType=TEXT to DataType=STR

Hi,

I have an input text file which contains line(s) which can have more than 8000 characters.

I am able to read the lines in this file by specifying that the input column is data type = Text Stream [DT_TEXT] and I can then write the lines to another text file.

But, what I want to do is to use only the right-most 8000 characters. When I try to convert the input column to data type = String [DT_STR] using the Data Conversion transformation then I get the error:-

The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.

I cannot use the string manipulation functions e.g. RIGHT or SUBSTRING in a Derived Column transformation because these functions do not work with DT_TEXT data.
Any Ideas?!

Thanks.You'd only need *1* line of custom code in a Script component transformation to extract your rightmost 8000 characters before moving on to the data type conversion.

-Doug|||Thanks Doug ... Did you mean a Script Component which looks something like:-

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim ascii As Text.Encoding

Dim asciiChars(10) As Char

ascii.GetChars(Row.InputColumn.GetBlobData(1, 1), 0, 1, asciiChars, 0)

Dim asciiString As New String(asciiChars)

MsgBox(asciiString)

End Sub

End Class

When I run the package it fails at the ascii.getchars line with the following error message:-

Error: 0xC0047062 at Data Flow Task, Script Component [106]: System.NullReferenceException: Object reference not set to an instance of an object.

All I am trying to do is extract the first byte and convert it to an ascii char (before I go on to work out the lengths to extract the right-most 8000 chars etc.)

Is this what you had in mind? Or, is there another way to do it?

Many Thanks.

|||This should do what you want. You will need to import System.Text.Encoding.

Dim Start As Integer = Max(0, CInt(Row.InputColumn.Length) - 8001)
Dim Length As Integer = Min(8000, CInt(Row.InputColumn.Length))
Dim asciiString As String = ASCII.GetString(Row.InputColumn.GetBlobData(Start, Length)

|||Just to confirm that Jay's suggestion works. Many Thanks.sqlsql

Converting .dbf to sql server table...batch file? command line?

My client will be receiving a .dbf file which needs to be uploaded
into a sql server database table (as an append) every week. They are
NOT computer savvy and I would like to automate this process rather
than go into enterprise manager and run data transformation.

Is there any way to write a batch file or a set of command lines which
will do this?

Thanks.

Monicamonica@.datashark.net (Monica J. Braverman) wrote in message news:<d70cd025.0310081033.375fbc9b@.posting.google.com>...
> My client will be receiving a .dbf file which needs to be uploaded
> into a sql server database table (as an append) every week. They are
> NOT computer savvy and I would like to automate this process rather
> than go into enterprise manager and run data transformation.
> Is there any way to write a batch file or a set of command lines which
> will do this?
> Thanks.
> Monica
DTS package would probably be the easiest solution. Create the package
as an import from the .dbf into the approriate table and then run it
with each new .dbf received. If running DTS packages is a bit
'complex' for your users you could wrap it up in an applicaion/wizard
(eg in VB) and execute it that way.|||monica@.datashark.net (Monica J. Braverman) wrote:
>My client will be receiving a .dbf file which needs to be uploaded
>into a sql server database table (as an append) every week. They are
>NOT computer savvy and I would like to automate this process rather
>than go into enterprise manager and run data transformation.
>Is there any way to write a batch file or a set of command lines which
>will do this?

You might create a DTS package on the server, then create a batch file
to access it that calls the DTSRUN.exe program. I have a batch file
with this as the command line:
dtsrun /e /n"dtspackagename" /sSQLServerName and it works very well.
hth,
Myron|||Hi,
you can automate your process using a command prompt utility named
"bcp"; if you try to execute this command you get the syntax:

C:\>bcp
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide
character type]
[-N keep non-text native] [-V file format version] [-q quoted
identifier]
[-C code page specifier] [-t field terminator] [-r row
terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional
enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]

Another way is to use the T-SQL command "BULK INSERT".
You can find explanations on your MS-SQL documentation.

Bye.

monica@.datashark.net (Monica J. Braverman) wrote in message news:<d70cd025.0310081033.375fbc9b@.posting.google.com>...
> My client will be receiving a .dbf file which needs to be uploaded
> into a sql server database table (as an append) every week. They are
> NOT computer savvy and I would like to automate this process rather
> than go into enterprise manager and run data transformation.
> Is there any way to write a batch file or a set of command lines which
> will do this?
> Thanks.
> Monicasqlsql

Convert System.Net.NetWorkCredentials to IReportServerCredentials

Hello, I have a reportviewer and I want to set its credentials with this line but I have that error.

rptViewer.ServerReport.ReportServerCredentials = new System.Net.NetworkCredential(usuario, clave, dominio);

Error 18 Cannot implicitly convert type 'System.Net.NetworkCredential' to 'Microsoft.Reporting.WebForms.IReportServerCredentials'. An explicit conversion exists (are you missing a cast?) C:\Inetpub\wwwroot\GescomDllo\Protected\01_Administradores\rptdefinicioncompetencias.aspx.cs 40 58 http://localhost/GescomDllo/

any idea?

I tried this with no luck

rptViewer.ServerReport.ReportServerCredentials = (Microsoft.Reporting.WebForms.IReportServerCredentials)new System.Net.NetworkCredential(usuario, clave, dominio);

Unable to cast object of type 'System.Net.NetworkCredential' to type 'Microsoft.Reporting.WebForms.IReportServerCredentials'.

|||There is a property on the IReportServerCredentials interface that allows you to get or set an object that implements ICredentials. And the NetworkCredential class of the .Net Framework implements this interface, so you can set your NetworkCredential instance to this property. For example,

rptViewer.ServerReport.ReportServerCredentials.NetworkCredentials = new System.Net.NetworkCredential(usuario, clave, dominio);

|||

Ian,

This would work just fine, but ... NetworkCredentials is read only ...

|||Are you using the WebForm or WinForm version of the control? With the WebForm version, the ReportServerCredentials property returns the IReportServerCredentials interface that has the read-only NetworkCredentials property, but the WinForm version returns a ReportServerCredentials class that has a read/write NetworkCredentials property.

Convert System.Net.NetWorkCredentials to IReportServerCredentials

Hello, I have a reportviewer and I want to set its credentials with this line but I have that error.

rptViewer.ServerReport.ReportServerCredentials = new System.Net.NetworkCredential(usuario, clave, dominio);

Error 18 Cannot implicitly convert type 'System.Net.NetworkCredential' to 'Microsoft.Reporting.WebForms.IReportServerCredentials'. An explicit conversion exists (are you missing a cast?) C:\Inetpub\wwwroot\GescomDllo\Protected\01_Administradores\rptdefinicioncompetencias.aspx.cs 40 58 http://localhost/GescomDllo/

any idea?

I tried this with no luck

rptViewer.ServerReport.ReportServerCredentials = (Microsoft.Reporting.WebForms.IReportServerCredentials)new System.Net.NetworkCredential(usuario, clave, dominio);

Unable to cast object of type 'System.Net.NetworkCredential' to type 'Microsoft.Reporting.WebForms.IReportServerCredentials'.

|||There is a property on the IReportServerCredentials interface that allows you to get or set an object that implements ICredentials. And the NetworkCredential class of the .Net Framework implements this interface, so you can set your NetworkCredential instance to this property. For example,

rptViewer.ServerReport.ReportServerCredentials.NetworkCredentials = new System.Net.NetworkCredential(usuario, clave, dominio);

|||

Ian,

This would work just fine, but ... NetworkCredentials is read only ...

|||Are you using the WebForm or WinForm version of the control? With the WebForm version, the ReportServerCredentials property returns the IReportServerCredentials interface that has the read-only NetworkCredentials property, but the WinForm version returns a ReportServerCredentials class that has a read/write NetworkCredentials property.