SQL Table Issue
-
Hi everyone,
In below code I am creating a table and inserting some english and non-english text in it.
DROP TABLE IF EXISTS Translations;
IF NOT EXISTS (select * from sysobjects where name='Translations' and xtype='U')
begin CREATE TABLE Translations(id int identity(1,1), not_english nvarchar(500) COLLATE Arabic_CI_AI_KS_WS, english nvarchar(500)) end;
INSERT INTO Translations VALUES
(N'شخص إرهابي','terrorist person'),(N'جواز سفر','Travel permit'),(N'الرقم الموحد','Unified number')
SELECT * FROM Translations;When executing above code from sql the result is like this:
But when executing the above code from a SSIS package using Execute SQL Task (File Connection), the table is created like this:
Why is this happening and how can I fix it? I need to create this table from my ssis package with correct data...
Thanks.
-
Seems you are using a
varcharsomewhere, or you've defined the column as a DT_STR rather than a DT_WSTR in your SSIS task.Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A wrote:
Seems you are using a
varcharsomewhere, or you've defined the column as a DT_STR rather than a DT_WSTR in your SSIS task.No I haven't change anything in SSIS package.
I have saved the above "create table and insert code" in .sql file.
In ssis package I have selected a "Execute SQL Task" and selected that .sql file via FileConnection:
-
I can see you've got it set to "code page 1252" there; that's a strong suggestion the file is being read as a DT_STR not a DT_WSTR.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A wrote:
I can see you've got it set to "code page 1252" there; that's a strong suggestion the file is being read as a DT_STR not a DT_WSTR.
What should be selected here then?
-
What encoding is the file saved in? UTF-16? UTF-8? Something else?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
-
Then the code page needs to be 65001. The data imports fine with me with that setting:
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Not directly related to your issue, but there are a couple of good-practice items I suggest you consider:
- Always qualify your tablenames with their schema
- Try not to include DDL operations (eg, CREATE TABLE) in ETL code. It makes it difficult to ensure that your database is in step with your source control system. This in turn makes CI/CD difficult.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin wrote:
Not directly related to your issue, but there are a couple of good-practice items I suggest you consider:
- Always qualify your tablenames with their schema
- Try not to include DDL operations (eg, CREATE TABLE) in ETL code. It makes it difficult to ensure that your database is in step with your source control system. This in turn makes CI/CD difficult.
The project that I am designing in my ssis project requires me to constantly change my tables and their column types. Its a big project so when I initially design a table or a function or a stored procedure, I am not sure about the data types I am using wont cause me problems later. So I have included "create_tables.sql" file which contains all tables information and each time I run this project, it drop creates some tables again....
- Jobs90312 wrote:
The project that I am designing in my ssis project requires me to constantly change my tables and their column types. Its a big project so when I initially design a table or a function or a stored procedure, I am not sure about the data types I am using wont cause me problems later. So I have included "create_tables.sql" file which contains all tables information and each time I run this project, it drop creates some tables again....
Then I would suggest you have a design problem; your tables' definition shouldn't be changing so frequently. The fact the the data type for a column are also in constant flux is the sign of some very serious underlying design issues.
Honestly, I think you might need to reconsider the whole process.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A wrote:Jobs90312 wrote:
The project that I am designing in my ssis project requires me to constantly change my tables and their column types. Its a big project so when I initially design a table or a function or a stored procedure, I am not sure about the data types I am using wont cause me problems later. So I have included "create_tables.sql" file which contains all tables information and each time I run this project, it drop creates some tables again....
Then I would suggest you have a design problem; your tables' definition shouldn't be changing so frequently. The fact the the data type for a column are also in constant flux is the sign of some very serious underlying design issues.
Honestly, I think you might need to reconsider the whole process.
Thank you for this advice, ok let me explain my project a little bit.
Its basically to design a database in which different kind of "Lists" data will be saved.
Lets say I have received "List 1" in which there are names and addresses of some people.
Names max length in this list is lets say 500, and address is 1000.
In next few days I have received some more lists in which there are more names and addresses but length size is different and there is new data as well like date of birth and passport numbers.
These lists are random, like I cannot tell what size the data would be and in what form. Sometimes these are xml files and sometimes csv files etc.
So what would you suggest that how should I alter my whole process?
-
Design your tables to be appropriate for the data you need to store. If you need to store things like names, addresses, DOB, and passport Numbers, create all those columns initially. Then, when you get your data,
INSERTthat into the appropriate columns in your database. If the data you receive has columns you don't need, don'tINSERTthem into your database.Ideally, as well, address the process that is creating this data; have it create standardised extracts. Otherwise you're going to need to have different ETL processes for each format, and you'll never going to be able to automate the process. If these are coming from third parties, work with them to get them to standardise that data. That might be that some third parties will submit in a different format to other, but have them at least be consistent in their submissions; it sounds like you'll just accepting the data in "any old format", and then you are (unsurprisingly) having a nightmare dealing with it, because there is no consistency.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply