SQL Question

Static M.e.

Hey All,

Basically I am trying to create a package that will

(A) Create a table with specified datatypes

(B) Use a text Source file for the data

(C) on Success \ Completion of the "Execute SQL" transform the data from the text into the table.

Connect to DB <-- [TRANSFROM]-- Text (Source) <-- Execute SQL (Create Table)

It all seems to work now but when I run the package I get the following error

The number of failing rows exceeds the maximum specified.

TransformCopy 'DTSTransformation_6'conversion error: Conversion invalid for datatypes on column on pair 1 (source column 'Col007' (DBTYPE_STR),destination column 'Rec_Amt' (DBTYPE_CY)).

But when I go into the TransformDataTask, under transformation and test that column it all works fine, infact I tested all the columns and they all seem to work fine.

It also seems to be creating the same table twice first in the " Execute SQL" task and then again for some reason in the "DataTransform" task. I dont know if that is realted to the problem or not though.

Any idea's or suggestions I could try ?

Im very new to SQL 2000 & DTS so dont rule out any very newbie errors

Thanks

Static M.e.

Anyone any idea at all ?

I have checked through loads of SQL sites but I cant seem to get anywhere with this. I dont understand why if it works when I test it column by column then suddenly it wont work when I run the package ..

Or even if you know a good SQL book that deals with DTS Ill go have a look for that.

Any help / pointers would be appreciated.

Kai

It looks like your error has to do with converting a string to a large integer. When you run a preview all that does is output the the results to a text file, it doesnt apply any type conditions to ensure that the data is correct.

What id advise is to change the datatype of the large int to a string and do a dummy run and then look at all the entries in that field and see if any would be invalid for conversion to an integer. Do they have invalid characters or are the exceptionally big numbers.

amen

where is your data coming from?
does the first row contain column headers?(there is an option u can set to ignore column headers)

if dts doesn't work I always create the table
and then insert a row of the data to see what happens
you can normally get a better error and its easier to fix
if the DTS job runs and only some data is imported if you click on the job history and show all job details you will get a detail error message that tells you which line of the input data failed

Static M.e.

Thanks very much for the input guys.

While I was tearing my head out with the problem I decided to try and upgrade the server to SQL 2005. To cut a long story short I made alot of mistakes and more or less totalled my DB.

Yesterday I decided to just wipe my machine stick 2003 Server & SQL 2005 on it and begin the process all over again. Unfortunatly for myself the new DTS in 2005 called SSIS, is competly different than 2000 and so I am back to the start point again trying to figure out how to build a package, which is quite different to building one in 2000.

Not quite sure what to do next, had a look in Easons for some books but to no avail, I might try and see if I can get a day course somewhere on SSIS just to get me started.

Thank you both for the suggestions though, I do appreciate the help.

vidapura

Hey Static,
yeah, know where you are coming from. Had a lot of hassles getting my head around DTS in the past. And also installed sql 2005 only to find out that the had changed to the bloody SSIS stuff... a right pain.

I actually went back to sql 2000 to hell with it... and now have my own dts stuff well sorted. I couldn't find any decent info on SSIS.. but found a great site about DTS.. www.sqldts.com... its got loads of tutorial info. especially on what you were trying to do.. just load a bloody file into a table.

Believe me its easy when you know how. Have a look at sqldts.com...
Think there is a sister site to that for SSIS..oh yeah.. there it is www.sqlis.com... link off the top of the sqldts.com page...

Hope this helps...
Vida

PS: if the data you are loading does not need much in the way of transformations or transpositions.. maybe take a look at BCP.

Also in visual studio 2005 (c#) there is a bulk data load facility.. you load up your file into a dataset and then slam it out to the database.. its very fast.. search for SqlBulkCopy in msdn.

Last option, if you want to have a SQL script that runs automatically or something like that, then search on the sqlservercentral.com for articles on will do BCP or SQLBULKLOAD such as this one http://www.sqlservercentral.com/columnists/spopovski/replacingbcpwithsqlbulkload.asp

amen

are you still having problems?
Can you attach some sample data
I'm willing to help you give more info
I use DTS with SQL 2000 on a regular basis w/o any issues

Static M.e.

Hey Guys,
Sorry for late response I havent checked boards in a while this SQL crack still
taking up 99% of my life at the moment, every time I think Im making progress I get shot down again.

Although I hate to say it but Im kinda starting to like SQL 2005 the more and more I play with it, even if it is killing me.

What I was wondering is if one of you might have a DTS 2000 text package something with dates, float, int & text datatypes in it that I could try to see if I could get it working on 2005.

I know its a lot to ask but it wouldn't have to have any data in it or anything, 1 line would suffice, just to see if I can get anything working from start to finish on this.

I could attach some data here but I think that the problem I have is in the whole importing and defining datatypes for the various cloumns and Datetime is also a major nightmare.

Thanks

amen

I could attach some data here but I think that the problem I have is in the whole importing and defining datatypes for the various cloumns and Datetime is also a major nightmare

well if you think this is your problem then it probally is and giving you some code isn't really going to help especially if you have to make changes

why not desribe in english what you want to and what the type of data you are using and where it is coming from and post some of the sample data if you can. Then we can have a look and suggest what you should start to do ?

Static M.e.

OK thanks Amen will do )

Basically I am trying to Import a text file into SQL, but when I import it, it doesnt pick up the correct datatypes which leads to trouble further on, because I have to mess with transforms and that kind of thing.

I have made the text files easier to import by making them csv now so save all the hassle with specifying the column widths etc.

So the first error comes up when I try to import a column with a date in it in the form of 21/04/1992. I get the error

"Error at data flow task SQL Server Destination 75: The Column "Column 4" can't be inserted because the conversion between types DT_DATE and DT_DBTIMESTAMP is not supported.

"Error at Data flow task [DTS PIPELINE] :Compnant "SQL Server Destination" 75 Faileds validation and returned validation status "VS_ISBROKEN"

Sample Data
10012 C961910 1 380.92 12/05/1997 IE HELENA 1
10013 C961711 3 3174.35 27/07/1999 FR MARYM 7
10013 C961711 3 2459.23 25/05/1999 FR MAURA 6

Static M.e.

More info the data is either coming from Csv or text files

This is the choice of data types I can use
although I dont have to have the smallest size for each or anything, eight-byte will do instead of single-byte etc

Boolean [DT_BOOL]

byte stream [DB_BYTES]

currency [DT_CY]

database date [DT_DBDATE]
database time [DT_DBTIME]
database timestamp [DT_DBTIMESTAMP]
date [DT_DATE]

file timestamp [DT_FILETIME]

decimal [DT_DECIMAL]

eight-byte signed integer [DT_18]
eight-byte unsigned integer [DT_U18]
four-byte signed integer [DT_I4]
four-byte unsigned integer [DT_UI4]
two-byte signed integer [DT_I2]
two-byte unsigned integer [DT_UI2]
single-byte signed integer [DT_I1]
single-byte unsigned integer [DT_UI1]

float [DT_R4]
double-precision float [DT_R8]

image [DT_IMAGE]

string [DT_STR]

text stream [DT_TEXT]

Unicode string [DT_WSTR]
unicode text string [DT_NTEXT]

unique identifier [DT_GUID]

Static M.e.

Sorry more info for that Sample data I choose the following

eight-byte unsigned integer [DT_U18]
text stream [DT_TEXT]
eight-byte unsigned integer [DT_U18]
currency [DT_CY]
date [DT_DATE]
text stream [DT_TEXT]
text stream [DT_TEXT]
eight-byte unsigned integer [DT_U18]

Static M.e.

I came across the following which might solve the problem but I cant figure out how to use it, I tried putting it inbetween the source and destination but it didnt work.

http://sqlservercode.blogspot.com/2005/09/date-formatting-in-sql-server_21.html

Or perhaps something like this

declare @dtmDate datetime

select @dtmDate ='20050111' -- OK
select @dtmDate ='2005/01/11' --OK
select @dtmDate ='2005-01-11' --OK

select @dtmDate ='01112005' -- problem
select @dtmDate ='01-11-2005' --OK

Kai

Hi Static,
I had a similar problem to the one your describing and it was down to the format of the date eg.
01/11/2005 Could be in DD/MM/YYYY or MM/DD/YYYY (american) format. The DTS package probably defaults to the american standard wich works fine for all dates < 12 of each month. But for dates 13/11/2005 it will get an error as there is no 13th month.

I got around it by going into the the properties of the transform step and it should be set to datetime string. You can then set the format of the source date string and also the destination format. Try setting the Source string to 'dd/MM/YYYY' to let the package know its in that format and place the same in the destination format.

Thats what worked for me anyway.

Static M.e.

Thanks Kai,

I remember being able to do that in DTS 2000 but I cant find any option under SSIS 2005 to change the datetime format

Ive come across a bunch of scripts to do it (I think) but I cant seem to get them working.

Ie.
1> declare @date char(8)
2> select @date = '01061992'
3> select convert(smalldatetime, (SUBSTRING(@DATE,5,4) + '-' + SUBSTRING(@DATE,3,2) + '-' + SUBSTRING(@DATE,1,2)))
4> GO

---

1992-06-01 00:00:00

Does anyone where code like this should be in the dataflow ?

Kai

Im still using 2000 so i cant help you with the SSIS part directly but heres an interesting article:
http://blogs.conchango.com/jamiethomson/archive/2005/04/26/1337.aspx

It looks like you can switch the default language to english and the date settings should be correctly formatted. Iif you cant do this in the script try looking into the server properties. But that could affect other databases on that server so be careful.

amen

I think this switching of languages on running DTS is a bad idea.
What you have to ask youself is what language/codepage should you be using and what is the current language/codepage?

If SQL server is running the incorrect code page then it is possible to chagne it using a few sps (not too hard)

Static M.e.

THanks for the help guys I eventually found a solution with a friend

We used a derived column inbetween the source and destination to change the datatypes so it would pick up the date, it looks so simple when its done can't beleive it almost killed me !

This is the command
(DT_DBTIMESTAMP)[Column 21]

Which has sorted out 90% of my problems.

Seriously thanks for all the help guys, you helped me loads, I appreciate it a lot