Solved

SSIS: How to Concatenate input columns dynamically in Script Component by using C#

Posted on 2010-08-29
7
1,528 Views
Last Modified: 2013-11-10
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

0
Comment
Question by:beauty_calla
  • 3
  • 2
7 Comments
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33554821
what is type of source and destination? OLEDB ? flat file? excel?
0
 

Author Comment

by:beauty_calla
ID: 33555227
Original source is OLEDB - Oracle.  But, I have a data type conversion before this Script Component.  Destination is  OLEDB - SQL Server.
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33555664
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?


0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:beauty_calla
ID: 33560476
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.
0
 
LVL 30

Accepted Solution

by:
Reza Rad earned 500 total points
ID: 33561336
Absolutely, there is no way to implement a Data flow task with dynamic number of columns in SSIS.
It is better to implement it all inside script, I mean connect to different databases, fetch rows, do anything you need, and fill destination , all inside script task.

0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 35414307
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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now