?
Solved

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

Posted on 2010-08-29
7
Medium Priority
?
1,638 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

770 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