.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

DTS to SSIS conversion

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :


How to convert this code:  should I use recordset and foreach loop container or script task or SSIS activex script?

Function Main()
 Dim cin, patient, asOfDate
 cin = CDbl(DTSSource("Col001"))
 patient = CDbl(DTSSource("Col002"))
 asOfDate = CDate(DTSSource("Col003"))
 DTSGlobalVariables("conaudit_count").Value = DTSGlobalVariables("conaudit_count").Value + 1
 DTSGlobalVariables("conaudit_sum").Value = (DTSGlobalVariables("conaudit_sum").Value + cin + patient)
 DTSGlobalVariables("conaudit_as_of_date").Value = asOfDate
 Main = DTSTransformStat_SkipInsert
End Function

Please advice.


View Complete Post

More Related Resource Links

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 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'

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?

Uniqueidentifier field failing during conversion in SSIS



I am trying to convert a table from one schema to another and the destination table (SQL CE) contains a uniqueidentifier field. This field can accept nulls and is optional. I am not supplying any values for it.

But when I run the package it throws an error.

SQL Server Compact Destination [107]: Data conversion failed. [ OLE DB status value (if known) = 2 ]

If I map a dummy value for this column then the error doesnt occur. Any help would be appreciated. Thanks,

Ganesh Ranganathan
[Please mark the post as answer if it answers your question]

DateTime Conversion Issues in SSIS



I have a Weird issue with SSIS. I have some input data of dates like '01/01/1999' and '01/01/0207. First one is a valid date and later is invalid. I have to use DataConversion inorder to convert it to datetime and re-direct the errored row to Flat-file destination.

Now the Issue is:

If I convert to DateTime (DateTimeStamp) in Data Conversion, it is treating both the dates as Valid and failing in my SQL Destination as the later is an invalid date.

If I convert to DateTime2 (DateTimeStamp2 with Precesion) in Data Conversion, it is treating both the dates as Invalid?

How do i fix this in SSIS (In a simple way) to throw 1 as error and other as Valid? Any help is appreciated.



ssis date conversion



We are trying data conversion  using ssis  and facing the following issues: 

1. How to default values to table (sql server) through SSIS in the data transfer step. (I used to run an update after the data transform task, but here the table schema does not allow nulls at all during the intial data transfer). It looks to me may be there is an option somewhere that i need to check for the default values.

2. We want to transfer data to a identity column, knowing that it does not let me export data into that column i actually added a  SQL step before the data flow task saying "SET identity_insert off" on that table but still the package fails saying that cannot insert on identity column.

3.we also want generate a script that shows how the data transformation is done like which table and column is mapped to which destination table and column.

please advice.


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

SSIS Derived Column and Data Conversion Task


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?



Date to String Conversion in SSIS Variable


Hi All -

I have an SSIS variable whose value is a date formatted as a string in YYYYMM. For example 201011 (meaning Nov 2010). I need to change the value to 201012 (meaning Dec 2010). My thought was to convert 201011 to a date, use dateadd, then convert back to a string. Something like:

(DT_STR)DATEADD(mm, 1, (DT_DBDATE) @[User::strVar] + '01'), 6, 1552)
Can someone point out what I am doing wrong?

date format conversion from oracle to sql using SSIS


HEllo friends

I am migration oracle tables to sql using SSIS. While doing this i am trying to use Derived column transfrom to convert Date format in oracle to sql. But i do not have any clue that how should i use this. In oracle side date is in mm/dd/yy format and in sql i want that in yy/mm/dd format. 

Please give me directions ASAP?

Thanks in advance


data type conversion using ssis



i am migrating from oracle to sql i am getting data type format error while importing data from oracle to sql for date datatype in oracle. I was using SSIS with data conversion task.

IS there any method i can use to resolve the error with date format?

Thanks in advance


Program for Format Conversion of An Image

.NET provides extensive support for image conversion. Any image can be processed from one format to another. Most common formats to which .NET have support for are .BMP, .EMF, .GIF, .ICO, .JPG, .PNG, .TIF and .WMF.

Using Conditional Split data Transfer in SSIS 2008

This article uses the Integration Services Conditional Split Data Transformation element to filter and transfer data from a set of flat text files to SQL Server database table. The concept can be easily extended to apply to any other source or destination such as Microsoft Excel. This scenario is useful in creating denormalized database tables in a reporting and analysis situation.

Conversion of text files from ANSI to UTF-8

reading and writing text files in ANSI format and
writing html-files in Charset ISO-8859-1 (Western Europe).

WebMatrix conversion failed when converting from a character string to uniqueidentifier?


I am trying to retrieve xml from a small function in my .cshtml page. Its throwing me an error. This however, runs fine in a console/form environment.

It takes 3 parameters and 1 exception.

myDll.GetXML(year, username, uniqueidentifier, out ex);

When I run this page I get the error, "Conversion failed when converting from a character string to uniqueidentifier."

Or a stack of.

System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting from a character string to uniqueidentifier.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

   at System.Data.SqlClient.SqlDataReader.get_MetaData()

   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBeha

C# conversion to VB and properties


I am trying to implement image caching for a web application and I found some code on the Internet that implements an HttpHandler that does this. The code was in C# and since my web app is written in VB, I decided to convert the code to VB. I managed to convert everything except the following code (in bold):

public class FileExtensionCollection : ConfigurationElementCollection
        public override ConfigurationElementCollectionType CollectionType
                return ConfigurationElementCollectionType.AddRemoveClearMap;

        public FileExtension this[int index]
            get { return (FileExtension)BaseGet(index); }

Conversion of List to DataTable and ViceVersa


hi ,

           Iam having a requirement where I wil get datatable or Dataview from the DataAccessLayer I need to convert them into GanaricList of entites and ViceVersa from GenericList to Datatable or DataView.

             I tried it but I cant. Can anybody help me out.

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