Two Quick Question's,
Are you better off importing data through Excel or through Text files in terms of ease of use \ Speed \ Efficency etc or does it make a differance ?
Also if I am loading data into a SQL database should I always use the "SQL Server Destination" rather than the "OLE DB Destination"
Hope Q's aren't too basic, both seem to work for me, but I just want to make sure im using the right one.
Thank you
You may find text files a little easier than excel files, because in case of excel files, you may need to do a transformation from unicode to non-unicode characters.
For your second question, SQL Server destination can be used only if you are loading the data to the local server. It can't be used for a remote server. When using a local server, SQL Server Destination, will have a performance benefit over OLEDB Destination. But if you are working with a remote server, OLEDB destination is the only one you can use.
|||Thank you very much Ranjeeta that was exactly what I was looking for :)
Just as an afterthought in the case of Excel "you may need to do a transformation from unicode to non-unicode characters. "
Is this tricky to do ? or would it be a case of using "Data Conversion" to change the datatypes from unicode to non unicode charactors
Again thanks
|||Not tricky. Just the matter of using a "Data conversion", so just an additional step.|||I would recommend not using Excel to much... You might get into trouble because of Excel interpreting fields sometimes as text sometimes as numbers... That's not always a problem (depends on the structure of you tables) and it's nothing making Excel as a source impossible but it can take you some time to get it working...|||Thanks for the help, I have opted to not use excel and have resorted instead to text files or csv files.
However I still seem to be running into the same type of problem. All the text being imported weither text, date's or numbers all come in as Data Type "DT_STR" Code 1252 (not sure if the code bit is relevant).
I have then inserted a Data Conversion task to try and change the datatypes but none of it seems to work, I keep getting the same type of error
"Error at Data Flow [SQL Server Destination[9]]: The Column "Copy of Column 0" can't be inserted because the conversion between types DT_R8 and DT_STR is not supported."
Error at Data Flow task [DTS Pipeline]: "Componant SQL Server Destination" (9) failed validation and returned status "VS_ISBROKEN"
I have also tried DT_R4, DT_u18, DT_Date etc all with the same errror.
Ive worked on this for the last few days now but it always seem to be the same error so Im second guessing everything now trying to find a solution.
Surely I should be using "Data Conversion" to change the data types inbetween my source and destination column. ?
Im sure its user error somehow but I cant understand whats going wrong
|||Did you set the field's properties correctly in the data source adapter? If you did so we have to sort out where the pipeline changes from having correct metadata to only having strings there... Check the output's settings from the data source and the pipeline's metadata just after the data source... If there are only strings (and you have set the field's properties) perhaps recreate the data source adapter for the text file...|||Sorry for the Delay in getting back to you Thomas I have been trying to figure out where I have been running into errors so I deleted all and am in the process of building them back up again.
When I rebuilt the flat file source connection I used Suggest Data Types for the following 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
and these are the data types it suggested I should use
Column 0 eight-byte signed integer [DT_18]
Column 1 string [DT_STR]
Column 2 eight-byte signed integer [DT_18]
Column 3 double-precision float [DT_R8]
Column 4 string [DT_STR]
Column 5 string [DT_STR]
Column 6 eight-byte signed integer [DT_18]
Im a little cautous of "string [DT_STR] so I thought maybe the following would be more accurate ? although I didnt use them in this instance but when I do I get the same error
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]
It then goes straight to the "SQL Destination" and create a table
CREATE TABLE [SQL Server Destination] (
[Column 0] BIGINT,
[Column 1] VARCHAR(7),
[Column 2] BIGINT,
[Column 3] DOUBLE PRECISION,
[Column 4] DATETIME,
[Column 5] VARCHAR(2),
[Column 6] VARCHAR(7),
[Column 7] BIGINT
)
Then when I run the package it gives me the following errors
"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"
Any idea's ?
And thanks for all your help on this :)
|||Did you try using DT_DBTIMESTAMP instead of DT_DATE in your source's output?|||
Yes it seems no matter what datatype I give that column
database date [DT_DBDATE]
database time [DT_DBTIME]
database timestamp [DT_DBTIMESTAMP]
date [DT_DATE]
They all come back with the same error
|||Is it possible to use something like the following to edit the date format ?
http://sqlservercode.blogspot.com/2005/09/date-formatting-in-sql-server_21.html
I tried to use it but I couldn't get it working at all
|||
Something what should work is:
- import the date as string
- use an expression in a "Derived Column" transform to cut the date into pieces, rearange it to a format wórking with SQL Server and convert to a "working" datatype
I wrote already about that: http://forums.microsoft.com/MSDN/showpost.aspx?postid=199322&siteid=1
|||Perfect Thomas thanks so much for your help
In the end the derived column between the source and destination fixed 90% of my woes :) which is excellent news for me
Again thank you, I appreciate it.
|||Also the excel worksheet has a limit of 65536 recrods where as flat file has a limit of one million.
No comments:
Post a Comment