January 17, 2008 at 9:57 am
Creating a SSIS package with a Flat File connection to a SQL table as its destination, getting a validation error.
The column is bank_acct_nbr which is a char type. As per BOL SSIS DataType string [DT-STR] is the one to use for char sql datatypes
My DataFlow is taking the flat file, converting other columns (not bank_acct_nbr) then via OLE DB dumping into a SQL 2005 table
Is this something I need to change on my Data Conversion step?
If so I can make life more easy and not have the flat file be DT-STR
Thanks
January 17, 2008 at 1:17 pm
The problem was the destination table.
The flat source file for bank_acct_nbr was char(17) and after digging around the net, SSIS doesn't allow a char to char from outside source to SQL
So on the destination table I changed the datatype from char to nchar and I am past this problem...(however now I have datetime conversion problems)
July 22, 2008 at 9:17 pm
Sheldon , I am facing the same issue while converting text column to datetime. Did you got any solution.
July 23, 2008 at 12:16 am
karina,
check this, it may help you,
select cast('01/01/1990' as datetime) as sa_hidk
Cheers!
Sandy.
--
July 24, 2008 at 6:39 am
If you are using Unicode then you can store everything in the 'n' equivalent data-type. For example, if you would normally use varchar(10) then you could use nvarchar(10) same thing with char(1) would be nchar(1). One thing to keep in mind with nchar or nvarhcar is that Unicode requires twice as much space as ASCII does. This is because Unicode requires 2 bytes of storage verse 1 byte for ASCII.
I have the same issue here when I move data from our mainframe to SQL and just convert everything to ASCII. We don't have any need for Unicode at this time and given the space requirements (it adds up quickly) it doesnt make sense.
-Mike
October 22, 2009 at 9:20 am
Guys..My problem is little different and I am new to SSIS too.
I have excel file which I upload to my database via SSIS where i split the data for cleaning but One of the column in my excel contain information like this:
-------------------
ApproximatTimeRun
-------------------
10:00 AM Daily
So, I have time and also some character within the same column..When I imported without using "Data Conversion" it ran successfully but the which ever row of the "ApproximatTimeRun" column contain word like "Daily" come up with NULL instead of "10:00 AM Daily",
- I tried to convert to DT_WSTR, DT_NTEXT, and DT_Text(not allowed) but it didn't work.
- By default it suggest me to use DT_DATE which I dont want it.
Any help would be very appricieated...
October 22, 2009 at 9:55 am
What is the datatype of the column to which you would like to pass this data?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 22, 2009 at 9:57 am
I would get rid of the '----------' lines and then mark the first row as a header when you import it. SSIS should then ignore it and correctly associate that column as a datetime.
October 22, 2009 at 10:03 am
The column is in Excel Spreadsheet and when I checked on by doing Format in excel it want give me exact the format type but if i convert to just TEXT than the row with only time (10:00 AM) will get convert to some number (0.88399948). So, It is a filed where people put time,comments,number etc..
It is not more like format column..
Any advice...!!!
October 22, 2009 at 10:09 am
Sorry,, I wasnt very clear when i post the example.
Here is the excel example
ID, ApproximatTimeRun, Qnty, Amount
1, 10:00 AM Daily, 3, $20
2, 06:00 PM Daily, 4, $30
3, 02:00 AM Weekly, 12, $45
4, Need to be schedule, 0, 0.00
So, When I import the excel via SSIS into DB, the ID = 4 row give me Null on "ApproximatTimeRun" column but I do receive other information from the same column.
This is my final table look like when it run successfully,
ID, ApproximatTimeRun, Qnty, Amount
1, 10:00 , 3, $20
2, 06:00 , 4, $30
3, 02:00 , 12, $45
4, Null, 0, 0.00
Please advice,
October 22, 2009 at 10:12 am
My best guess is that the data is probably coming in as null from Excel (you can check this using a data viewer). Excel has 'decided' that that field contains time info and anything else is NULLed out.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 22, 2009 at 10:31 am
The issue doesn't appear to be on the excel side but instead in the DB. I think SSIS is making that column Datetime and nulling out the invalid entry. You can confirm this by checking the table definitions in SQL or in SSIS. If this is the case then you need to either tell SSIS that the column is text and not Datetime or leave and change your logic down stream.
If that column is meant to represent the scheduled time then I am not sure why you really have a problem...its not schedule and hence doesn't have a scheduled time so NULL would be correct. You can change your logic on the table so when you query it and want all the unscheduled reports it would be where that column is NULL.
October 23, 2009 at 4:50 am
It could be an Excel driver limitation as explained here:
http://sqlserversd.wordpress.com/2008/09/14/ssis-excel-values-import-as-nulls/
I've run across it a couple of times with "apparently" good data being NULLed.
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
October 23, 2009 at 10:21 am
John McC (10/23/2009)
It could be an Excel driver limitation as explained here:http://sqlserversd.wordpress.com/2008/09/14/ssis-excel-values-import-as-nulls/
I've run across it a couple of times with "apparently" good data being NULLed.
That was what I was trying to say in my post, though not so clearly, I'll admit. Good link John, next time put it in URL tags to save me the effort of cutting/pasting 😛
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 29, 2009 at 3:04 pm
Thanks Guys...for your feed back. I have requested data entry user to cleanup excel since they are entering time and string togather. Now, they will enter only time so I can make it to datatime format.
I appriciate your help guys. Thanks a Billion..
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply