Solved

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

Posted on 2010-08-29
7
1,580 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

737 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