May 20, 2010 at 1:37 pm
Hello all,
I need to load 15 or 16 very large non-proprietery formatted files into a SQL Server DB. Each file has about 100 columns. Here's my issue. Of those 100 columns in the files about 70 of them have a data type of WT_STR (Unicode String). The SQL DB i'm loading to is varchar (which in SSIS is a data type of DT_STR).
What I have to do now in order to load them is use a data conversion task and convert the data type on each column that is DT_WSTR to DT_STR. This is a huge pain and is extremely time consuming.
Is there a way I can do a mass data conversion on these? Basically any data type that is DT_WSTR make DT_STR?
This would be a huge help if I can do this.
Thanks,
Strick
May 21, 2010 at 5:51 am
If it is just nvarchar to varchar casting, do the following:
create a global temp table (or a fixed staging table, it doesn't really matter) that has the exact same structure as the table you are writing to, but with nvarchar datatypes.
This can easily be done with a statement like this:
SELECT * INTO ##temp_table FROM My_Table
Make sure your connection manager to the database has to property RetainSameConnection set to true.
Then, select all the data from the temp table and insert it in your final destination table.
INSERT INTO Dest_Table SELECT * FROM ##temp_table
SQL Server will do an implicit casting from nvarchar to varchar. This way, you won't have to do it manually in SSIS.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply