.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

otput column in script transformation editor

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server


Using script transformation editor I'm trying to generate an output column to use it again in the data flow, I can create it using the editor but when I'm going to use it to store data (string type) in C# code I only find a Buffer type object. Can you please help me with this?




View Complete Post

More Related Resource Links

How to recover from Derived Column Transformation Editor corrupting table metadata?

Hi there, In attempting to use the DCTE to replace the value in a field with a trimmed version (SSN = trim(SSN)), it seems that my meta-data has become corrupted by the Derived Column drop-down list. As a result, #1: I no longer see my incoming SSN field in the "columns" tree, and any reference to it is deemed "invalid", even though it's value does make it out of the Data Transformation process. How can I get back the reference to this field without redoing this entire task? And, #2: in the process, DCTE created new columns with "SSN" prefixed by the task name, such as "trim character fields.SSN". How can I delete these? It seems that one slip of the mouse in this form can lead to irreversible corruption of the meta-data, which the "debugger" then references and uses to "invalidate" subsequent work. I have tried everything I can think of to refresh this, including using the "Advanced Editor" and reloading the entire package. Any ideas? Thanks, Karl Kaiser

SSIS Changing Column Order during Transformation

First let me say, I really can't believe this chain of events myself--and they are happening to me. I am upgrading several DTS packages to SSIS on what will be my new production server.  These packages create tables, export them to a flat file, and ftp them off to other locations. What is happening (on the SSIS side) is that the OLE DB Source is reordering some of the columns on its own (moving them to the end of the table/file.  Then when my pickup/load routines run, the data is out of place and they fail. Can anyone please explain what is happening here with the mapping.  I have evaluated the table and the columns are in the order that I expect.  When I preview the source table in the OLE DB Source Editor the columns are in the correct order/alignment, but when them in the OLE DB Source Editor --Columns section within BIDS the order is changed arbitrarily. I have been somewhat successful (2 out of 3) in being able to re-map the data, but this last table just doesn't want to change.  Thanks in advance for any help and/or information you can provide

C# newbie stuck - trying to access column data in a SharePoint list in an SSIS script task

Hello, I'm sure this is the simplest question but I can't figure it out, even with Google's help. I am trying to stumble through some C# code in an SSIS script task and I am frustrated that I can't figure out how to do the easiest things.  I eventually want to find data in a column,and then use another list as a lookup to replace that value with another where the existing value matches a value in the lookup list.  So, the data in my (multiple choice) column might be "apples; bananas" and in another list I have a row that contains two columns, the first holding the value "Apples" and the second containing "Red Delicious" and my original column should read: "Red Delicious; bananas." But, alas, I can't even figure out how to see the data that is in a column. Here is my code: /*<br/> Microsoft SQL Server Integration Services Script Task<br/> Write scripts using Microsoft Visual C# 2008.<br/> The ScriptMain is the entry point class of the script.<br/> */<br/> <br/> using System;<br/> using System.Data;<br/> using Microsoft.SharePoint;<br/> using Microsoft.SqlServer.Dts.Runtime;<br/> using System.Windows.Forms;<br/> using Microsoft.SharePoint.Utilities;<br/> <br/> namespace ST_08becda4c05c49cd9f30ea76110076cd.csproj<br/> {<br/> [

Duplicate column error in script

Hello, I have a new database server and I created a new database and now I'm trying to run a script that's supposed to create all the tables from the previous database server. The script is an accumulation of various changes I have made to the old database since the start of the project in order to implement a variety of features, so it has this pattern: ALTER TABLE X DROP COLUMN Y; GO and later in the file: ALTER TABLE X ADD Y int NOT NULL; GO When I run the script, I get a duplicate column error message for the table and field, although I first dropped the column and then added it again. I suppose I have to do some kind of flash after the drop and before the add, but isn't the GO supposed to do that? If not, how can I flash it in the script? Thanks.  

Script Component Transformation as a Lookup

Hi Guys, I have the following code from my Script Component as a Lookup. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports System.Collections.Generic Imports System.Data.SqlClient <Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _ <CLSCompliant(False)> _ Public Class ScriptMain     Inherits UserComponent     Dim connMgr As IDTSConnectionManager100     Dim sqlConn As SqlConnection     Dim sqlCmd As SqlCommand     Private vTaxAmt As Decimal     Private vDelChgAmt As Decimal     Public Overrides Sub AcquireConnections(ByVal Transaction As Object)         connMgr = Me.Connections.Connection         sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)     End Sub     Public Overrides Sub PreExecute()         sqlCmd = New SqlCommand("SELECT ItemTypeCode FROM LkpItemDepartment WHERE (ItemDeptCode = @ItemDeptCode) AND (ItemClassCode = @ItemClassCode)", sqlConn)                 With sqlCmd.Parameters   

Derived Column Transformation My Expressin is not working


Hi Folks

My expression is not working -its always returning "N" if I find the word AIWORKS I want to return "Y" otherwise if not found "N"


finally if i want to test a whole lot of conditions why is this come up in red
COMPANY == SUBSTRING(LTRIM("AIWORKS"),1,7) ? "Y" : "N" ||COMPANY == SUBSTRING(LTRIM("Google"),1,6) ? "Y" : "N

thanks in advance



Error Starting Script Editor in SSIS


I am having a problem in the Business Intelligence Development Studio. When I add and try to edit a Script Task or a Script Component, I click on the Design Script button and get one of two errors. For the Script task I get an error that states:

Cannot show the editor for this task. (Microsoft Visual Studio)

For the Script component I get:

Cannot show Visual Studio for Applications editor. (Microsoft Visual Studio)

For some reason or another it just will not start Visual Studio for Applications. I have uninstalled an re-installed several times. I have also researched and discovered that there is a bug when C# tasks are include in the project. However, this is not the case. Please help!

Script Component Transformation with Lookup


Hi Guys,

I have the following recordset in the data flow.

Col0 Col1 Col2
Itm VM RY FF    128.00

script component "transformation"


Hi All,

Can anybody show me an example how to create new Derived Column ( e.g. ABC-Column-Name = "M" - its value ) using Script Component "transformation" the same as create a using Derived column Task?




derived column transformation


Hi frdz, one small question. i am just adding new column called CallAnswer in derived transformation task in my package and it would have yes and no records. what do i use the expression for that column as its a new column?



How to validate invalid column in subquery in script

How to validate invalid column in subquery in script.
What i have a full script for all of my stored procedures and function.
In some stored procedures there are some inner/sub queries which have invalid column name but there is no error in the script when i run it or Parse it.IS tehre anyway i can find the error in my script without going in each of Stored procedures/Functions

Kamran Shahid Senior Software Engineer/Analyst (MCP,MCAD,MCSD.NET,MCTS,MCPD.net[web])

Script Component as Transformation


Hi Guys,

I have these codes that gives my the desire output before. These codes scanned the records and if the current RowType <> "TENDER" and previousC1 = "TENDER", assign the value of RowType = "TOTAL" to a variable ouput.

Before, if the RowType is "TENDER", it means that its the end of a transaction group.

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _
<CLSCompliant(False)> _
Public Class ScriptMain
  Inherits UserComponent
  Private dt As DataTable
  Private previousC1 As String = String.Empty
  Public Overrides Sub PreExecute()
    dt = New DataTable()

How to execute a parameterized stored procedure using ssis dataflow script component transformation


Hi All,

Can any body send code for how to execute a parameterized stored procedure  using ssis dataflow script component transformation using .net

I know how to execute storedproc using control flow script task, but I need using ssis dataflow script component transformation.




Derived column TRANSFORMATION Parsed Error


Hi i am getting following error message while executing a package that 
updates the column value using a derived column transformation..
[TRR_FE_Parse_File [271]] Error: The "component "TRR_FE_Parse_File" (271)" failed because truncation occurred, and the truncation row disposition on "output column "SrcFileName" (32263)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "TRR_FE_Parse_File" (271) failed with error code 0xC020902A. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
Error: SSIS Error Code DTS_E_THREADFAILED.  Thread "WorkThread0" has exited with error code 0xC020902A.  There may be error messages posted before this with more information on why the thread h

How to do Count(Column) using SSIS Transformation..?


Hi All,

I have a question....In my SSIS Package I need to do this kind of calculation : if  Count(Status==C) >= 1 ...Then Get only those records.... from the incoming flow...

My Status Column Values will be C,B,D

My input source is Oracle.... In the middle of the flow I need to do this calculation....

I know how to acheive this using look up transform.....Is there any other transformation other than lookup which I can use... inorder to acheive  count of status = c >= 1...?

If so..can you let me know ASAP...

Appreciate your responses.





ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend