Easier way to convert Non-Unicode to Unicode

I have built a large package and due to database changes (varchar to nvarchar) I need to do a data conversion of all the flat file columns I am bringing in, to a unicode data type. The way I know how to do this is via the data conversion component/task. My question is, I am looking for an easy way to "Do All Columns" and "Map all Columns" without doing every column by hand in both spots.

I need to change all the columns, can I do this in mass? More importantly once I convert all these and connect it to my data source it fails to map converted fields by name. Is there a way when using the data conversion task to still get it to map by name when connecting it to the OLE destination?

I know I can use the wizard to create the base package, but I have already built all the other components, renamed and set the data type and size on all the columns (over 300) and so I don't want to have to re-do all that work. What is the best solution?

In general I would be happy if I could get the post data conversion to map automatically to the source. But because its DataConversion.CustomerID it will not map to CustomerID field on destination. Any suggestions on the best way to do this would save me hours of work...

Thanks.

If SSIS has to use a namespace to identify a column...this happens when you have Source1.CustID and DataConversion1.CustID it can't do the mapping. If you changed the names in the source it could, but that might be more trouble than it's worth.

Dynamic table name from varchar field

Hi,
How can i execute folowing T-SQL properly ?
Error given due to so.name is a varchar value.
Select Distinct so.name as TableName,(Select count(*) from so.name) as
RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id where
so.xtype='U'
The output will be "
TableName RecCount
-- -- --DMP wrote:
> Hi,
> How can i execute folowing T-SQL properly ?
> Error given due to so.name is a varchar value.
> Select Distinct so.name as TableName,(Select count(*) from so.name) as
> RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id
> where so.xtype='U'
> The output will be "
> TableName RecCount
> -- -- --
Erland covers this here:
http://www.sommarskog.se/dynamic_sql.html
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||You can't execute dynamic SQL inline like that, read up on EXECUTE()
fortunately a rowcount is available in sysindexes that you can use without
traversing each table anyway:
SELECT SysObjects.Name,
SysIndexes.Rows
FROM SysObjects
JOIN SysIndexes ON SysIndexes.ID=SysObjects.ID AND SysIndexes.IndID IN
(0,1)
WHERE SysObjects.xtype='U'
for reference IndID in (0,1) eliminates all indexes but the base tables
0=heaped, 1=clustered. Note that queries on the system tables are likely to
fail if you upgrade to a new version of SQL.
Mr Tea
http://mr-tea.blogspot.com
"DMP" <debdulal.mahapatra@.fi-tek.co.in> wrote in message
news:eEI%236wTAFHA.1084@.tk2msftngp13.phx.gbl...
> Hi,
> How can i execute folowing T-SQL properly ?
> Error given due to so.name is a varchar value.
> Select Distinct so.name as TableName,(Select count(*) from so.name) as
> RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id where
> so.xtype='U'
> The output will be "
> TableName RecCount
> -- -- --
>