Solved

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

Posted on 2010-08-29
7
1,570 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
I have a large data set and a SSIS package. How can I load this file in multi threading?
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
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.

685 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