Solved

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

Posted on 2010-08-29
7
1,508 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
Comment Utility
what is type of source and destination? OLEDB ? flat file? excel?
0
 

Author Comment

by:beauty_calla
Comment Utility
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
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:beauty_calla
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 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

11 Experts available now in Live!

Get 1:1 Help Now