Read TXT File - problem with importing from flat file source ssis
-
hi Guys,
I have txt file where i have 14 million rows from Qlikview and SAP exported.
Now i want to import in into SQL Server Database.
Delimeter is ";".
I want to get only "Data_Zmiany" field as Date
So I am deleting all fields, leaving only Data_Zmiany, changing string type to date and column delimeter is:
{CR}{LF}In preview i see all columns which are concatened with delimeter: ";"
It is very strange.
When I am changing delimetr into semicolon i have:onca again all fields but in a one field:
How can i do it in proiper way?
What i am doing wrong?Best Wishes,
Jacek Antek -
Don't edit the properties in the connection manager - I've rarely made alterations at that level and usually because SSIS has been very stupid (generally when importing from an Excel spreadsheet).
Read your input from the connection into a 'Data Conversion' task, where you can create a new field that is the result of a conversion of an input column.
-
Another way of doing this is to import everything as a text into a staging table and transform it from there. This makes error/exception handling easier and generally makes the load faster. An added benefit is that one can have multiple transformation rules and checks which would be tedious at the best to code in SSIS.
😎 -
Thank you Guys,
Steve,
You wrote that the best way is to get data from OLE DB Source.
But what if i have TXT file?Can i create connection to TXT file as OLE DB source ?
Sorry i am new in SQL Server.Eirikur - I tried to load text file into stagging table - source was set up as FLAT file and this method has filed as you can see in my previous posts...
Thnak you,
Jacek -
Anyone ?
Jacek
-
Use a Data Flow Task with OPENROWSET in an OLE DB Source and output to a Recordset Destination
Far away is close at hand in the images of elsewhere.
Anon. -
Thank you very much David,
I am opening OLE DB Source and trying to set up OPENRowset from flat file,
could you please help me and show what i should choose now ?Best wishes
Jacek -
Thank you Steve,
From flat file it is good when i am downloading all data as a string.
Next data converstion is used and I am converting string to float and has error:
The given value of type String from the data source cannot be converted to type float of the specified target column:In Data conversion i have Data_Zmiany as Date (it is working), PESEL (11 digits), and Company_NIP (10 digits or null values) where my error shows up.
So problem is that i want to do a flot from string value.How can I do it ?
Thank you for help,
Jacek
- jaryszek - Wednesday, November 29, 2017 6:23 AM
If you can write the data into a table as string/character data, then you can use a query with try_convert to do the conversion, much less fragile than data conversion in SSIS
😎 -
Hi Eirikur,
thank you very much!
So question here:
are SQL Administrators do often what you have suggested me?If yes, i will do that.
In Query editor there is no problem with my code:
USE QlikView
GOCREATE TABLE tbl_Slownik
(
ID INT IDENTITY(1,1),
UniqueID NVARCHAR(50),
Data_Zmiany Date,
PESEL float(53) NULL,
Person_ID NVARCHAR(50),
Nazwisko NVARCHAR(50),
Imie NVARCHAR(50),
Department NVARCHAR(50),
Company_NIP float(53),
Company_name NVARCHAR(50),
Company_shortcut NVARCHAR(50),
Systems NVARCHAR(50),
)INSERT INTO tbl_Slownik (UniqueID, Data_Zmiany, PESEL, Person_ID, Nazwisko, Imie, Department, Company_NIP, Company_name, Company_shortcut, Systems)
VALUES ('"A"--$Q>F7+O/"ZC.T(3#U,;"', '2017-11-27', 12345678911, 'PH3-30129004', 'Boross', 'Karolds', 'ITM0', 973093349933 , 'ITM Poland', '6F00', 'SAP')
But within SSIS there are different issues connected to this...Best Wishes,
Jacek - jaryszek - Wednesday, November 29, 2017 6:23 AM
Does every row in your text file contain a valid value for that column, that should be able to be converted to a float?
Eirikur is right -if you need to do a lot of data transformation then importing the file into a staging table that has every column as nvarchar is probably better. I do this all of the time - the original column and then the 'new' column next to it, where the transformed value will be. That way, if you get any strange behaviour you can see what the original data was and what your process transformed it into. It has the added benefit that the actual transformation of the data can be via a stored procedure, so you can change that without having to change the actual SSIS project. -
Thank you Steve!
Does every row in your text file contain a valid value for that column, that should be able to be converted to a float?
It seems that data is valid but still errors in SSIS occur.
I do not know how to check this using debugger.Only 3 fields need to be transformed from string to another data type - date and float from string.
Thank you Guys,Jacek
- jaryszek - Wednesday, November 29, 2017 7:13 AM
I do this all the time and have built many ETL frameworks which work on this principle, consider it to be within best practices when one has to use SSIS. Further, data conversion within SQL Server is many times more efficient than within SSIS.
😎 -
Thank you Guys,
i used :
Select
*,
CAST(Company_NIP as float) as Company_NIP2
FROM QlikView.dbo.tbl_Slownikand there is NO any problems within this.
I am shocked. Wow!Thank you!
I am closing the topic but still not know why this was not working in SSIS...Jacek
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply