.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

SSIS Changing Column Order during Transformation

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
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

View Complete Post

More Related Resource Links

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.






Order by Nemeric column


Hi all,

Thank you for advance.

the Document Rank column is numeric and contain the values are 2,1,0,4,0,5,3,0,6

we need to get the document rank with the following orders

how can i do this?

SSIS Lookup Transformation Issue

I am having a strange issue, In my data flow i have a Lookup Transformaton which will match on key columns of the fact and is followed by a condetional split that would deside if it should insert (old db destination) or go to update (oledb command) or ignore if no change. I have packages running for the last 1 year with the same logic. But in the recent packages I am experiencing a problem.  example: Key columns for join are - type_no (varchar 16) with all numeric values except one record wihh ' '(space) in it  and type_cd (decimal(18,0)) with values(0,1,2,3,4,5) It worked fine when I test the package. After couple of day running in schedule I get integrity violation and huge file with failed records which are supposed to be blocked at the condetional split as they are already in the fact. When i add a data viewer what i found is for all the llokup columns its having nulls (no match found). Workaround that is working for me for now is - I select full cash and say ok in the lookup transofrmation and again open it and set it back to no cash. Then it starts working as expected. Did anyone come accross this kind of issue? is the some standard that I have to follow to make sure this doesnot happen again  

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/> [

What is the meaning of Ascending\descending sort order of a column within an index

When I add a new index I can also specify the "sort order" as ascending or descending. What does it mean? does it affect performance? does it mean that the binary search tree will be set in a certain way?

How do I change the order of column names in EditForm.aspx

I have an Announcements list to which I've added extra columns: Publish (date), De-Publish (date), and Category (choice).  When I go to EditForm.aspx, the columns display in this order: Title , Body, De-Publish , Category and Publish . I'd like to change the order of the columns on EditForm.aspx so that it is Title, Body, Category, Publish and De-Publish . Changing the All Items view doesn't do it. How might I change the order of columns that is shown to the user?

ssis sql 2000 image to 2008 varbinary Failed to retrieve long data for column

Hi, I have a task that to migrate the image type column from sql 2000 to varbinary type in sql 2008. The source column having 3812353 max datalength size for the column. The package always failed with following error message. [OLE DB Source [13177]] Error: Failed to retrieve long data for column "attch_file_content_t". [OLE DB Source [13177]] Error: There was an error with output column "attch_file_content_t" (13209) on output "OLE DB Source Output" (13187). The column status returned was: "DBSTATUS_UNAVAILABLE". [OLE DB Source [13177]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "attch_file_content_t" (13209)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "attch_file_content_t" (13209)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. Meng Chew

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

How do I INSERT when I don't know the column order

If I know the order that columns are in (FirstName, LastName, PetName), inserting into a table is easy: INSERT INTO MY_TABLE ("Charlie", "Brown", "Snoopy") But if I only know the column names and not their order, how do I insert?

How to save and restore column order in WPF?

Hi, I am working on WPF 4 DataGrid, and I was just wondering if there is a way to save column order to xml at application shutdown and restore column order at application startup. I am thinking of to create a class with properties like header name, display index and visibility, but i don't know how to map this to datagrid columns.

changing font color on a data column field based on the contents

I am trying to change font color if the datafield has the string EMERGENCY. This is my original template field: <asp:TemplateField HeaderText="Type" SortExpression="DigType"> <EditItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Eval("DigType") %>' ></asp:Label> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Eval("DigType") %>' ></asp:Label> </ItemTemplate> </asp:TemplateField>   and this is what I thought would work: <EditItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Eval("DigType") %>' ForeColor='<%# IIf(Eval("DigType")="EMERGENCY","Black", "Red") %>'></asp:Label> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Eval("DigType") %>' ForeColor='<%# IIf(Eval(&quo

In SSIS 2005 how can I implement Slowly Changing Dimension Type 6

Hi I can't see SCD Type 6 in SSIS 2005 ,how do I get add  into my data flow task and if anybody can explain me step by step process for SCD type 6

How to redirect bad date(xxx,9999) in datereceived column in text file in ssis

I want to redirect bad date format from flate file source to log table. I tried with redirect row but it is not working. Thanks in advance

SSIS 2005 imports column as null

Hi, I am using SSIS 2005 to import excel files to sql server. I have a large excel file with many columns. I have one column -qty that not all rows have data for it-empty. For such rows a another column-value that i need to export is imported as NULL. If column QTY contains a value than column Value is imported fine but if column QTY is blank Value is NULL even if it does have a value. I have played with TypeGuessRows but it doesn't help. Any ideas? Thanks

Slowly Changing Dimension in SSIS

Hi I am new to SSIS and i have read concepts of Slowly changing dimensions ( Type 1,2 and 3). Can any one suggest some examples or tutorils to implement this in a SSIS package for better understanding how does it works in SSIS?   MAny Thanks Ravi.S

SSIS Ado >NET Destination Identity column inserts

Hi I am trying to build an SSIS package that imports the identity values from one SQL table to another.  I have an ADO .NET destination connection as the import is for SQL Azure although Im currently testing it in SQL 2008.  The connection has RetainSameConnection set to True.  Before the import I have a SQL task that runs SET IDENTITY_INSERT  activityDisplayGroup ON.  This executes but then I get an error message at the data flow task.  [Destination - activityDisplayGroup 1 [1]] Error: An exception has occurred during data insertion, the message returned from the provider is: Explicit value must be specified for identity column in table 'activityDisplayGroup' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column. In the profiler I see insert bulk "dbo"."activityDisplayGroup" ([name] VarChar(200) COLLATE SQL_Latin1_General_CP1_CI_AS) Is it at all possible to insert identies using the ADO .NET, if so what have I missed?   Thanks Dan      
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