Showing posts with label hair. Show all posts
Showing posts with label hair. Show all posts

Wednesday, March 28, 2012

importing text files and converting datatypes

Hi ya... About to tear my hair out.

I thought i fixed this problem, as it WORKED about two days ago, but now I'm getting errors.

I have a series of text files, some are delimited, some are fixed width. I previously was able to import these files thru a dts package by creating the table in a stored procedure. Exple:
CREATE TABLE [Pol_Dump073104]
(
[Product_Type] varchar (12) NULL,
[Benefit] float NULL,
[Base_Premium] varchar (12) NULL,
[Rider_Premium] float NULL,
[Contract_Value] float NULL,
)

I then import the text file into the table and then cast the float datatypes as money in a select into statement to do aggregate functions on the money fields. this worked previously, but now I'm receiving this: 'Error converting datatype char to money.' I tried to convert to float and received the same error, only with float where the word money was.

Please help! :mad:

ThanksI'm not totaly clear on this: so the data is inside the table 'Pol_Dump073104' and a convert(money, benefit) fails with "Error converting datatype char to money"?|||Yup. This particular DTS package works with most of my text files, and it used to work with this particular text file, but now all of a sudden it won't convert imported varchars (or chars, or nchars, or nvarchars) to floats, or monies. And to be clear, I tried importing the file as all varchars, and then in a later step in my dts packaged, tried to cast those particular fields as floats. And that's when I get that error.

It's frustrating!!!

Am I making any sense?|||I think the varchar column may have values that sql cannot convert to money/float. You may have to check the varchar column prior conversion with functions as ISNUMERIC or a where statement that checks (not like '%[^0-9]%'). You may need to consider this in your package, possibly have it send a notification. The rows that do not fit, what are the options?|||Thanks so much kaiowas. I found the bad rows. Now, how can I tell SQL to send me an exception report with the missing rows?|||Kaiowas, your help saved me. I figured everything out, and it's working beautifully... exception report and all. Just needed to be pointed towards the right track. Many thanks.sql

Monday, March 19, 2012

Importing Excel File in SSIS

Okay... I am now about to pull my hair out: something that worked VERY EASILY in Server 2000 doesn't seem to work at all in 2005. I am trying to pump an Excel table into a 2005 database. I go into the Visual Studio Integration Services Project (this is so much easier... cynicism) and set up a project. I have my data source (Excel), I have my destination (SQL Natve Server, database). I set it up the same way that it worked (perfectly) in DTS and I run it... it grinds away and reports back that all is well... no errors. I go looking for the table... not there. I try with an SA login VS windows authentication... not there. I try with a different table... no there. I try with a different database... not there.

I am certain you can imagine the frustration... that is, if you are a user, not a programmer at Microsoft.

Okay, okay... I won't launch in to abuse here... but hey, how do I make this very complicated process now work?

Thanks...

Use the OLE DB Destination.

The SQL Server destination is for databases local to the package.|||

Thanks Phil... I did figure it out with more fidling (as is usually the case, when you breakdown and ask for help, you discover the answer and just how much of a dolt you are): you have to SAVE the package before it runs correctly. [so, what was previously a ONE STEP operation, is now 8 steps... joy, joy, joy]

Thanks, and adios...