May 30, 2017 at 10:34 pm
I am creating metadata driven SSIS package OLEDB Source to Excel Destination where Data conversion task required and need to be set dynamically .
Can anybody suggest how to achieve by task or Script task level.
May 31, 2017 at 6:30 am
subratnayak09 - Tuesday, May 30, 2017 10:34 PMI am creating metadata driven SSIS package OLEDB Source to Excel Destination where Data conversion task required and need to be set dynamically .
Can anybody suggest how to achieve by task or Script task level.
If you're talking about changing the XML in your .dtsx package at run time, I don't think that's possible. You can generate a package with BIML, but I still don't think that's the route you want. Can you explain more under what conditions you need to do your conversion? Can you do something with the expression in a derived column task?
You don't give enough to be of more help -- sorry.
Rob
May 31, 2017 at 8:25 am
robert.gerald.taylor - Wednesday, May 31, 2017 6:30 AMsubratnayak09 - Tuesday, May 30, 2017 10:34 PMI am creating metadata driven SSIS package OLEDB Source to Excel Destination where Data conversion task required and need to be set dynamically .
Can anybody suggest how to achieve by task or Script task level.If you're talking about changing the XML in your .dtsx package at run time, I don't think that's possible. You can generate a package with BIML, but I still don't think that's the route you want. Can you explain more under what conditions you need to do your conversion? Can you do something with the expression in a derived column task?
You don't give enough to be of more help -- sorry.
Rob
Hello Rob,
I have created SSIS package which will get all metadata information from Database such as (Source/Traget column Name, Data Types,Sql queries e.t.c). Package has to get metadata information and loop through Foreach container to feed the OLEDB source and Excel destination information inside DFT dynamically.Finally DFT should load resultset to target excel sheets .
Now metadata information successfully fetching from DB and looping through foreach loop container. Problem is that , when I am fetching data from OLEDB source to Excel destination inside DFT task getting error as "Can't convert Unicode to Non-unicode data type". To resolve the issue
data conversion task is needed. If I set the Data conversion task can't change the metadata at runtime. So any possible to avoid Data conversion task for excel destination or Data conversion can be done dynamically at run time using column name/Data type metadata.
June 1, 2017 at 6:29 am
Kind of a klugey idea, but what if you wrote to a table that uses nvarchar() (or Unicode aware) fields. Then SELECT from the table, and write to your Excel destination. That may not work if you've got dynamic fields.
Just a thought,
Rob
June 5, 2017 at 7:33 am
robert.gerald.taylor - Thursday, June 1, 2017 6:29 AMKind of a klugey idea, but what if you wrote to a table that uses nvarchar() (or Unicode aware) fields. Then SELECT from the table, and write to your Excel destination. That may not work if you've got dynamic fields.Just a thought,
Rob
Rob, All the fields are varchar() so I am unable to proceed without data conversion task.
June 5, 2017 at 7:57 am
No, from your error ( "Can't convert Unicode to Non-unicode data type".) your data is not varchar/non-Unicode, but it is the double wide nvarchar/Unicode. Which is why I recommended writing to a temp table that will accept Unicode characters.
Good luck,
Rob
June 6, 2017 at 7:18 am
"Can't convert Unicode to Non-unicode data type".
On reviewing this, I'm still unsure what the overall goal is. But when I import from Excel, I create a staging table with all the fields set to data type nvarchar(255). This matches the data type for an Excel cell. The first duty in importing data is to just get the data and own it. You can then make changes from there.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply