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


Top 5 Contributors of the Month
Sharad
Manish
Post New Web Links

SSIS Derived Column and Data Conversion Task

Posted By:      Posted Date: November 01, 2010    Points: 0   Category :Sql Server
 

I am passing down a variable, filename, as  string and then using Derived Column to pass it down to the destination. The filename is numeric (date) like such: 20101010. So, I need to convert the filename into a date formmat before I pass it to the destination table. Therefor, I am using a data conversion and converting it to a Date format. I tried using both database date or the date format and it keeps failing.

Here is what the failure report:

[Data Conversion [42297]] Error: Data conversion failed while converting column "DateAdded" (29685) to column "Copy of DateAdded" (42310).  The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".  

Any ideas to resolve this issue?

Thanks


Kajo


View Complete Post


More Related Resource Links

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

Data conversion in SSIS

  
I am creating an SSIS package, My source DB is ORACLE and destination is SQL Server 2005 I am moving the contents of a Table AO in Oracle DB to  Table B in SQL Server2005 A specific Column in  "Renvenue " in AO table is of data type NUMBER  , it contains  decimal values like 1040.334, 19191.33, 454545.22 I have a corresponding column 'Rev" in Table B ( in SQLserver DB) of datatype Numeric(18,2)   I have created a SSIS package using Data source Reader, DataConversion , OLEDB Destination  in the dataflow task Everything is working fine, but I am getting incorrect data for example 1040.334, 19191.33, 454545.22   for these values I am getting 1040.00 , 19191.00 , 454545.00 the decimal values are not getting transferred to the destination table. I tried converting the datatype to Numeric {DT_NUMBERIC] 18 -2   and even declimal(DT_DECIMAL] scale 2 but still not getting the decimal values   Can some one tell me where am I going wrong...      

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

Efficiency: new column in source query or derived column task?

  
Hi All, I've just started working on an SSIS package that pulls data from an OLE DB Source by a query. A new column needs to be added based on the value of a queried column. I was wondering if it's better to do that in the query or with a derived column? A simple example: I have a table that contains CustomerName and CustomerCode (this one can be V /valid/ or I /invalid/). I need to store the CustomerCodeDesc in a separate column in the destination table. Is it better to alter the query like this: SELECT CustomerName, CustomerCode, CASE WHEN CustomerCode = 'V' THEN 'Valid' ELSE 'Invalid' END AS CustomerCodeDesc FROM CustomerTable Or is it better to use a DerivedColumn task in the DataFlow? Or maybe it doesn't really matter...

SSIS Excel Connection Manager Data Type Conversion Issues with SS Agent Job

  
Hi All! I have an issue I've been trying to fix but can't seem to figure it out. I was hoping a kind person would point me in the right direction. :o) I have an SSIS package that uses an excel connection manager source, and I want to run this package through a job scheduled in the SQL server agent. The data types for the excel file fields are 2 (DT_WSTR) and 5 (DT_R8). When I run the package directly through the SSIS package (VS solution) all of the data fields are properly imported into the database table. But...when I run this package through the SQL server agent job, ONLY the string (DT_WSTR) fields in each row are being imported, all of the float fields are imported as NULL. I set the data types for these float fields as "float" in the SQL server import table (data type). Even though the excel source float fields are indicating a type of DT_R8 in the excel connection manager and I set the data types in the SQL server table to "float", I also used the data conversion component and set the type to "float" as a fail-safe. I guess I should add to that the data access mode in the excel connection manager is using a custom code to select only those columns that I needed and to trim rows that I didn't need. Here's my code that I have in the excel source editor: select f1, f2, f3, f5, f6, f7, f8 from [mdo$] where f2 <> 'Rep Name'

Writing XML data from URL to Object in SSIS Script Task

  
I have this URL where there is XML data. I have to extract that data from URL and dump it into DW table. I am using SSIS Script Task for that.

This is how the data looks like:
<currency>
<csymbol>AED</csymbol>
<cname>United Arab Emirates Dirhams</cname>
<crate>3.6732001305</crate>
<cinverse>0.2722421770</cinverse>
</currency>
−
<currency>
<csymbol>AFN</csymbol>
<cname>Afghanistan Afghanis</cname>
<crate>44.0000000000</crate>
<cinverse>0.0227272727</cinverse>
</currency>
−
<currency>
<csymbol>ALL</csymbol>
<cname>Albania Leke</cname>
<crate>104.4100000001</crate>
<cinverse>0.0095776267</cinverse>
</currency>



This is the code i'm using to load it into some Object type or something. But i dont know how to do that.

   
public void Main()
  {
   String URLString = "http://www.xe.com/dfs/datafeed2.cgi?beeline";
   XmlDocument doc = new XmlDocument();
   XmlTextReader reader = new XmlTextReader(URLString);
   doc.Load(reader);

   XmlNodeList currencynodes = doc.SelectNodes("currency");
   foreach(XmlNode currency in currencynodes)
   {
    XmlNode csymbol = currency.SelectSingleNode("csymbol&quo

Is there any Data Flow Task in SSIS to merge 3 different recordsets (from 3 different tables) and se

  
I have a requirement, that has 3 different Execute SQL Task that returns 3 different recordsets which differ from each other.  I want to know whether these 3 recordsets can be sent as Input to a Script Component.  I know that Script Component accepts only 1 Input... But is there any way, to have this done

need hint for ssis data flow task...

  

hai,

In my project, i have two data sources tables lets say oledb1 and oledb 2 in data flow task.

i want to load the data into destination (oledb3) by using the following logic...

select col1, col2, col3 from oledb1 where col1 not in (select col1 from oledb2);

Please help me...

thanks

sunny


Accessing data from Data Conversion task

  

Hi, 

 

I am new to SSIS. I am creating a package which does the following:

1. Get data depending on a SQL Query (in SSIS I did this as a OLEDB Source with SQL Command)

2. Data conversion 

3. Then insert the data into a SQL table. 

For Step 3, which task Should I use. Id I Use OLE DB destination, into a table, it is giving me Errors.

So Can I access the Output of the Data conversion step in Execute SQL task, if I use it instead of OLEDB destination?


Data Flow Task failing to drag-and-drop onto SSIS Control Flow

  
This morning, I started having problems with Visual Studio 2005 on XP when I tried to create an SSIS project; I was getting an error message about the failure to create an SSIS runtime object, with the reference "Unable to cast COM of type 'Microsoft.SqlServer.DTS.Runtime.Wrapper.PackageNeutralClass' to interface type". Digging here and elsewhere on the Net, I used regsvr32 to re-register msxml3.dll, msxml6.dll, and dts.dll. This allowed me to create a new SSIS project, but I was unable to add a data flow task to the Control Flow pane -- when I dragged the control onto the pane, nothing happened, and when opened the Data Flow pane and clicked on the 'No data flow task has been defined. Click here to create a data flow task" link, I got a 'Failed to create the task. The designer could not be initialized." error. Searching here and on the Net, I uninstalled and reinstalled the SQL Server 2005 workstation tools.

After doing this, I am still unable to create a data flow task by dragging the task out of the toolbox; the pointer assumes the right form for the drag-and-drop, as it does for other tasks, but when I release the mouse button, nothing happens. However, when I go to the Data Flow pane and click on the 'click here to create a new data flow task', it creates the task, and I am able to use copy-and-paste to make copies of the data flow task

SSIS Data Conversion without using the Data Conversion Transformation

  
how can i convert a record from a flat file to a different data type without using the data conversion task?

Visual Studio 2008 crashes on SSIS Data Flow Task opening

  
Hi,

Visual Studio 2008 crashes on SSIS Data Flow Task opening.
This is a random behavior on random SSIS packages on Data Flow Task opening on packages that I've finished developing last week. The packages does not executes with error.

Error Description from Event Viewer:
Faulting application devenv.exe, version 9.0.30729.1, stamp 488f2b50, faulting module msdds.dll, version 9.0.30729.1, stamp 488f2e31, debug? 0, fault address 0x00012b6a.

I'm runnig SSIS development against SQL SERVER 2008 ENT 64.

There is already a fix for this ?




My environment description:
Microsoft Visual Studio 2008
Version 9.0.30729.1 SP
Microsoft .NET Framework
Version 3.5 SP1
Installed Edition: Professional

Microsoft Visual Basic 2008 91605-270-4167253-60984
Microsoft Visual Basic 2008
Microsoft Visual C# 2008 91605-270-4167253-60984
Microsoft Visual C# 2008
Microsoft Visual Studio 2008 Tools for Office 91605-270-4167253-60984
Microsoft Visual Studio 2008 Tools for Office
Microsoft Visual Web Developer 2008 91605-270-4167253-60984
Microsoft Visual Web Developer 2008
Crystal Reports    AAJ60-G0MSA4K-68000CF
Crystal Reports Basic for Visual Studio 2008
+
KB944899, KB945282, KB946040, KB946308, KB946344, KB946581, KB947171, KB947173, KB947180, KB947180, KB947180, KB94

how to improve/optimize ssis pacakge data flow task

  

in my SSIS package, i have a Dataflow Task,

it reads data from text file and store into db table, in this data flow task i have only flatfile source and oledb destination nothing else (such as derived columns etc)


is there any way that i can improve the performance, each feeds has 13 columns with 70,000 rows in it


could you please tell me how to optimize this dfd step if exist any

thanks in advabce
asitta


SSIS Error in Script task: "Conversion from string "C002" to type 'Integer' is not valid"

  

Hi All,

I have a script component in my Dataflow that generates Error Description string.

But I am not trying to convert anything in my Script as you can see below.

Here is the Code inside the SCR_component.

-----------------------------------------------

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper


Public Class ScriptMain
    Inherits UserComponent
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
     'Use the incoming error number as a parameter to GetErrorDescription
    Row.ErrorDescription = ComponentMetaData.GetErrorDescription(Row.ErrorCode)
   End Sub
End Class

-------------------------------------------------

When I run the package , it fails at this script task with the following error:

Description: System.InvalidCastException: Conversion from string "C002" to type 'Integer' is not valid.

 

The value "C002" is actually a column in the metadata and its type is "DT_STR". I am not trying to convert it in my Script as you can see

Delimiter File Read Task Installer failed to show in SSIS Data Flow Items

  

I downloaded the Delimiter File Read and followed the instruction and installed it. The file is on the correct directory (>:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents) The file DelimitedFileReader.dll is there.

However, when I go to BI Development Studio and try to add the new task I can't see it in the SSIS Data Flow Items...it is not visible. Does anyone know why I can add/see it?

Thanks


Kajoo

SSIS data flow task not loading all rows from sybase server

  

Hi,

Using a data flow task I am trying to load data from a table in sybase server to sql server. There are approx 10000 rows in the sybase table, however for some reason which I am not able to find out, at random certain no of rows are getting inserted. e.g. in one run 800 rows then 200 rows etc.

Can somebody please guide me on this?



Data Conversion Task Error

  

Hi All, i have a table in excel as below. and i am trying to move records from excel to SQL.

Code     Template      ExportType

A002      A002XM08    DST           

---           ------           ---

I have simple SSIS package with Excel Source, data conversion task and OLEDB Destination. and in data conversion i have given the data type string(DT_STR) for each of the column and length of data type same as each column is having in sql database but still it fails at data conversion task and gives the following error: 

 

Error: 0xC02020C5 at Data Flow Task, Data Conversion [2329]: Data conversion failed while converting column " ExportType" (3519) to column "Copy of ExportType" (3533). The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

Error: 0xC020902A at Data Flow Task, Data Conversion [2329]: The "output column "Copy of ExportType" (3533)" failed because truncation occurred, and the tr

Categories: 
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