Showing posts with label load. Show all posts
Showing posts with label load. Show all posts

Sunday, March 11, 2012

Convert String to smalldatetime

Hi,

I am new to SSIS and have been trying to load a date from a Table1.ColumnA (varchar(50)) to a Table2.ColumnB(smalldatetime).

I tried using the Derived Column and Data Conversion controls but I get the below error.

Error converting data type DBTYPE_DBTIMESTAMP to datetime

What do I do to load this data.

Thanks,

Noel

Noel Fernandez wrote:

Hi,

I am new to SSIS and have been trying to load a date from a Table1.ColumnA (varchar(50)) to a Table2.ColumnB(smalldatetime).

I tried using the Derived Column and Data Conversion controls but I get the below error.

Error converting data type DBTYPE_DBTIMESTAMP to datetime

What do I do to load this data.

Thanks,

Noel

Try changing Table2.ColumnB to datetime rather than smalldatetime and see if it works.

-Jamie

|||

What type of database are you writing to? I can't replicate this against a SQL Server 2005 db.

If you can't change the datatype of the destination, you might try casting the column to a smalldatetime when you read it from the source.

|||

Hi Jamie,

In my case, I have a flatfile that has a string type column (value: 20070610) which when mapped to datetime column type of a table in SQL Server 2005 database. It displays "error while converting DB_STR type to DB_DATATIMESTAMP type". As the client wants to run packages in BC mode, we donot know how to cast this in DTS as well wonder how to write a transformation mapping..

Thanks

Subhash Subramanyam

|||

Subhash512525 wrote:

Hi Jamie,

In my case, I have a flatfile that has a string type column (value: 20070610) which when mapped to datetime column type of a table in SQL Server 2005 database. It displays "error while converting DB_STR type to DB_DATATIMESTAMP type". As the client wants to run packages in BC mode, we donot know how to cast this in DTS as well wonder how to write a transformation mapping..

Thanks

Subhash Subramanyam

Hi Subhash,

Well now I'm confused. First of all you said the error was:

Error converting data type DBTYPE_DBTIMESTAMP to datetime

Then you said it was:

error while converting DB_STR type to DB_DATATIMESTAMP type

Which is it?

-Jamie

|||

Subhash512525 wrote:

Hi Jamie,

In my case, I have a flatfile that has a string type column (value: 20070610) which when mapped to datetime column type of a table in SQL Server 2005 database. It displays "error while converting DB_STR type to DB_DATATIMESTAMP type". As the client wants to run packages in BC mode, we donot know how to cast this in DTS as well wonder how to write a transformation mapping..

Thanks

Subhash Subramanyam

Use this expression in a Derived Column transform:

Code Snippet

(DT_DBTIMESTAMP)(SUBSTRING(DateValue,1,4) + "-" + SUBSTRING(DateValue,5,2) + "-" + SUBSTRING(DateValue,7,2))

The date needs to have the dashes inserted for the cast to work.

|||

Jwelch,

Thanks for your support. That resolved our issue. I was unable to mark this as an answer, however I have marked that as a helpful post.

Regards

Subhash Subramanyam

Friday, February 10, 2012

convert data file type from unix to pc using stored procedure?

Hi, I have a script written in ASP to load data file (.csv) to ms sql. In the script, I have a portion of script looks like taht :

....

Do While NOT oInFile.AtEndOfStream
oOutFile.WriteLine Replace(oInFile.Readline, chr(13), vbcrlf)
Loop

....

After that, I will use a BULK INSERT to input data to ms sql.

I am wondering how do I convert each row (data) to vbcrlf in Stored Procedure? Coz' I did not compose the convertion part, and I got no error when running BULK INSERT, but no rows are inserted :( HELP!!!!

I guess it's because the file is not being converted into a correct format??

Can you give more information? e.g.: sample text used in BULK INSERT, BULK INSERT command you're using, and the schema of the destination table.