beauty_calla
asked on
SSIS: How to Concatenate input columns dynamically in Script Component by using C#
I am trying to dynamically read columns and its data type, and concatenate values by using C#. And it should generate a string of concatenate value which is delaminated by ';'.
I can't use the following from the Script Component " outputbuffer = col1 + col2" because I want to use this function in many other packages and inputs for different pacakages will be different from each other.
Here is an example of what I am trying to do.
Input
Col1 Col2 Col3
123 abc 2kie
456 dic 0ke
Output
Record
123;abc;2kie
456;dic;0ke
I can't use derived column function becuase my output data type is varchar(8000) and derived column can output max of 4000 characters.
Does anyone know how to accomplish this in SSIS? It doen't have to be in Script Component.
Thanks,
Christine
I can't use the following from the Script Component " outputbuffer = col1 + col2" because I want to use this function in many other packages and inputs for different pacakages will be different from each other.
Here is an example of what I am trying to do.
Input
Col1 Col2 Col3
123 abc 2kie
456 dic 0ke
Output
Record
123;abc;2kie
456;dic;0ke
I can't use derived column function becuase my output data type is varchar(8000) and derived column can output max of 4000 characters.
Does anyone know how to accomplish this in SSIS? It doen't have to be in Script Component.
Thanks,
Christine
Reza Rad
what is type of source and destination? OLEDB ? flat file? excel?
ASKER
Original source is OLEDB - Oracle. But, I have a data type conversion before this Script Component. Destination is OLEDB - SQL Server.
OK,
SSIS can not handle dynamic data structure in data flow actually,
this means that you need to implement it in other ways,
one of this ways is t-sql statements, but in your case source is Oracle and destination is sql server.
so you need Linked servers on Oracle and sql, don't worry, this is possible, look at this links:
http://www.sqlmag.com/article/sql-server/setting-up-an-oracle-linked-server49687.aspx
http://www.databasejournal.com/features/mssql/article.php/3290801/Linked-Severs-on-MS-SQL-Part-4-Oracle.htm
after linking servers, you can use t-sql commands to get output result you want, for example select field1+';'+field2 from table
you should combine it with an insert statement, like this: insert into sqltable select from oracletable
does it make sense to you?
SSIS can not handle dynamic data structure in data flow actually,
this means that you need to implement it in other ways,
one of this ways is t-sql statements, but in your case source is Oracle and destination is sql server.
so you need Linked servers on Oracle and sql, don't worry, this is possible, look at this links:
http://www.sqlmag.com/article/sql-server/setting-up-an-oracle-linked-server49687.aspx
http://www.databasejournal.com/features/mssql/article.php/3290801/Linked-Severs-on-MS-SQL-Part-4-Oracle.htm
after linking servers, you can use t-sql commands to get output result you want, for example select field1+';'+field2 from table
you should combine it with an insert statement, like this: insert into sqltable select from oracletable
does it make sense to you?
ASKER
I can't use your solution because I am trying to build a function that can be used for many different sources such as Oracle, SAP, Flat File, SQL Server and so on...
Once I read the source (different source will have different list of fields in its record set), I want to use a funcation to concatenate field values in SSIS package.
Once I read the source (different source will have different list of fields in its record set), I want to use a funcation to concatenate field values in SSIS package.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.