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


Post New Web Links

ssis date conversion

Posted By:      Posted Date: October 12, 2010    Points: 0   Category :Sql Server
 

Hi,

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.

Thanks, 




View Complete Post


More Related Resource Links

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


dimrd_SQL

Conversion from string to type 'Date' is not valid

  

Hi

Im receiving a strange error Error Message: Conversion from string "15/08/2010 22:21:35" to type 'Date' is not valid.

I know this is generally down to cultural date formats etc, however the reason this is strange is it just randomly occured twice in the last week, the website has been running since october and nothing has changed,

The error constantly occured until the IIS was reset and recompilled the site, its been running fine since, however im just trying to figure out what could cause such an error to occur.

I've narrowed it down to a line of code which is:

bktime = DReader.Item("Bktime").ToString

bktime = Year(CDate(bktime)) & "-" & fmt(Month(CDate(bktime)), 2) & "-" & fmt(Day(CDate(bktime)), 2)


Has anyone else encountered this randomly occuring and is there anything i can put in place to prevent this in future?


Cheers


Mike


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'

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

Conversion failed when converting date and/or time from character string. SQL 2008

  
The below simple query giving me the "Conversion failed when converting date and/or time from character string." error. Because I've  datatype of "Created" & "Finished" column in below table VARCHAR(100) select * from dbo.mytable where CONVERT(datetime,Created) >= convert(datetime,'9/1/2010') and CONVERT(datetime,Finished) <= convert(datetime,'9/9/2010') In my table i've date stored in below varchar(100) formats. examples: '2010-09-01 11:14:54.000' --Created '2010-09-03 13:43:37.000' --Finished I tried to convert the date in following way but NO luck??? Could you please advise me??? convert(datetime,convert(varchar,convert(datetime,Created),101)) >= convert(datetime,'2010-09-01') and convert(datetime,convert(varchar,convert(datetime,Finished),101)) <= convert(datetime,'2010-09-09') Much appreciated your help!! thanks, pbrathod pbrathod

Using LEFT command with Date Conversion

  
Hi There, I have a select statement that I use to seperate a datetime field in to a date and time column. select (convert(varchar(30),DateTimeField,107)) as Date,(convert(varchar(30),DateTimeField,108)) as Time FROM mytable I want to be able to trim off the last "00's" from the time column, so I have tried to use the LEFT command - which I am unable to get to work. I would have expected it to be: select (convert(varchar(30),DateTimeField,107)) as Date,LEFT((convert(varchar(30),DateTimeField,108)),5) as Time FROM mytable But I can not get it to return a value, Can anyone see where I am going wrong? Thanks Matt

Conversion failed when converting date and/or time from character string. SQL 2008

  
The below simple query giving me the "Conversion failed when converting date and/or time from character string." error. Because I've  datatype of "Created" & "Finished" column in below table VARCHAR(100) select * from dbo.mytable where CONVERT(datetime,Created) >= convert(datetime,'9/1/2010') and CONVERT(datetime,Finished) <= convert(datetime,'9/9/2010') In my table i've date stored in below varchar(100) formats. examples: '2010-09-01 11:14:54.000' --Created '2010-09-03 13:43:37.000' --Finished I tried to convert the date in following way but NO luck??? Could you please advise me??? convert(datetime,convert(varchar,convert(datetime,Created),101)) >= convert(datetime,'2010-09-01') and convert(datetime,convert(varchar,convert(datetime,Finished),101)) <= convert(datetime,'2010-09-09') Much appreciated your help!! thanks, pbrathod pbrathod

Date Conversion from dd/mm/yyyy to yyyy-mm-dd

  
Hi guys,   I need a expression to convert date format from dd/mm/yyyy to yyyy-mm-dd by using SUBSTRING, FINDSTING  COLUMN NAME: ExtractionDate   I tried something like this to get year first and later on to add mm and DD, but its not working   SUBSTRING(@[User::ExtractionDate], FINDSTRING((@[User::ExtractionDate], "/", 2)),4)   Any help much appreciated   Thanks,        

date time Conversion

  
select [RowID]=ROW_NUMBER() over (order by b.userid),b.userid [Data Capturer's],
COUNT(distinct(g.boxnumber)) [Count] from box b join General_SAM g 
on (g.BoxNumber=b.boxnumber) where b.code=5000 and b.department=02 and b.origindate 
between '2010-07-28 00:00:00.000' and '2010-09-28 00:00:00.000' group by b.userid 

My computer is set to UK - dd-mm-yyyy. 

Is there a way that i can supply date using the above format i.e. dd-mm-yyyy


Urgent Expression Request - Date Conversion

  

Input Date : 4/08/2010 6:27:00 AM

DATA TYPE : STRING

Output Required: 2010/08/04 

 

Expression used : SUBSTRING(@[User::ExtractionDate],1,FINDSTRING(@[User::ExtractionDate],"/",1) - 1) + "-" + SUBSTRING(@[User::ExtractionDate],FINDSTRING(@[User::ExtractionDate],"/",1) + 1,2) + "-" + SUBSTRING(@[User::ExtractionDate],FINDSTRING(@[User::ExtractionDate],"/",2) + 1,4)

Current Date is getting: 4-08-2010

 

URGENT HELP GUYS I NEED THE OUTPUT IN THIS FORMAT

2010/08/04 


SQL error: Conversion failed when converting date and/or time from character string.

  
I hope someone can advise on the issue I'm having.

This SQL worked beautiful until I added in @startdt and @enddt and this section >> AND [Selldate] BETWEEN ' + @startdt + ' AND ' + @enddt + '

By adding in this condition, I now get the following error:

Msg 241, Level 16, State 1, Line 8
Conversion failed when converting date and/or time from character string.

I have tried changed variables to type = date and various iterations of date formatting, casting with no success. The date in the table is clean as a whistle.

(The CONVERT function is being used in setup for a PIVOT SQL which needs  VARCHAR for column headers)

Thanks in advance for your help, 
JR

declare @itemID varchar(20)
declare @startdt varchar(12)
declare @enddt varchar(12)
set @itemID = ''
set

remote execution of ssis package using wcf doesn't like date parameter

  

I have a ssis package remotely executed by a C# windows application that runs ok in context of SSIS installed on the desktop; I created a wcf windows service that the C# application executes, creates a copy of the package with the date in the variable, but errors on the first SQL task component that requires the date. The copy executes using the SSIS utility. What is possibly the issue with wcf and the date type in the SSIS package? Here is the error:


OnError,SRVR2BLADEA3,MIC\1347259,Clear CII ImportDate,{AB3CF5BA-C114-40F6-9ECB-4C7620D97829},{8B1C10CB-580F-4BFE-BC7D-0B97C181E307},9/27/2010 3:29:56 PM,9/27/2010 3:29:56 PM,-1073548784,0x,Executing the query "" failed with the following error: "The type initializer for '<Module>' threw an exception.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Here is the code:

case @"E:\SSIS Packages\CIISCImport.dtsx":

//string dtValue = (prms[iPrms - 1].Split('=').GetValue(1).ToString()) + " 12:00";

vars["User::ImportDate"].Value = DateTim

Conversion from string "09/28/10 9.00AM" to type 'Date' is not valid.

  

I am getting the below error. Please see my code below and let me know what is wrong.

 

dim sDatetime as string = startdate & " " &starttime & "AM"

dim dt as Date = CDate(sDatetime)

I am getting the Conversion from string "09/28/10 9.00AM" to type 'Date' is not valid error in this line. 


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