.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

Saving Date column from XML Source to SQL Destination

Posted By:      Posted Date: September 29, 2010    Points: 0   Category :Sql Server

Hi, I'm trying to load data from an xml file to SQL Server table. I've columns of type DateTime in my xml file. I've used Data Conversion to convert the datetime column to DT_Date and trying to save/update the data using Slowly Changing Dimension, which is throwing an error:

    "[Derived Column [928]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (928)" failed because error code 0xC0049067 occurred, and the error row disposition on "input column "DATEVAL" (973)" 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."

I've been trying to fix this thing by using all the data types that are related to dates(DBTIMESTAMP,DT_DBDATE, etc) but in vain. Can someone suggest me how to fix this.

Thanks in advance,


View Complete Post

More Related Resource Links

Problem in Date Format While Exporting To Excel Destination From Flat File Source In SSIS



I have a flat file(.csv) which contains data(strings incuding date in yyyy-mm-dd format).This i am using in Flat File Source and want to export those data to an Excel Destination.Before exporting to Excel Destination i have created a Derived Column component where i am using an express to get day,month,year part and creating a date sting in dd/mm/yyyy format then conveting it to DT_DATE datatype and then exporting to Excel Component.But while exporting to Excel this date format is getting changed to mm/dd/yyyy format.

The expression i used in derived column is

(DT_DATE)(SUBSTRING(date,9,2) + "/" + SUBSTRING(date,6,2) + "/" + SUBSTRING(date,1,4))

where date is a column from Flat File Source.

Even I changed the Locale to English(United Kingdom) in Flat File Connection Manager for the above .csv file.But still it didnt work.

I searched a lot in google and failed to get any solution on this.

Can anyone help me in solving this issue ?

SqlBulkCopy: the locale id '0' of the source column '<column name> ' and the locale id '1



I have same issue, can anyone help?


The locale id '0' of the source column 'StandardName' and the locale id '1033' of the destination column 'StandardName' do not match.

I have check source and destination table both are same. Please help me.

BDC Entity as a Data Source for a Lookup column

Is it possible to create a custom lookup field type to have a BDC entity as the data source?

Saving JQuery date back to SQL Server


Hi..my web app uses lots of asp:textboxes and I can save this data back to SQL Server easily using a stored procedure..

however my JQuery datepicker uses an input textbox, how do I save the date entered into this control.

<input id="datepicker"type="text" /> back to server and can I use the same stored procedure that I use for the other data?



XML Source (Data is not loading to Destination ,Package Runs sucessful)

Hi First I run execute SQL Task SELECT TOP(10) [Message],opportunity_Id  FROM SBP FOR XML PATH ('GS_Opportunity'), ROOT('GSOpportunities') That will make 10 different file into single file then i have assigned this output to variable and it will output as XML Now I put Data flow task ,in it I put XML task and in source i have put XML Data From Variable and I select XSD file location,now XML task is showing me different source table and i have selected appropriate destination table but when i run this package it will not transform data from source to destination,Can you suggest me what is the problem i try to put data viewer it is not transforming any data  while insted of if i select direct file location instead of variable it is running good    

Filter items with a date column with parameters start date and end date on a sharepoint list?

If I have alist with a view that has the columns Title, Status and Status Date and the view has some items. How Can I add a filter with two parameters, start date and end date, to only show records with status date equal to or greater than parameter start date and status date is less than or equal to parameter end date? This is a sharepoint online site and I can't create and deploy custom code, can use SPD though. Do I need to use SPD or is this something I can do in the list settings? Thanks in advance.   Edit: I had a look here http://www.endusersharepoint.com/2009/09/29/sharepoint-date-filter-filtering-a-list-by-greater-than-or-equal-to-date/ but I don't have the Date Filter web part.

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

Unable to load data into a LookUp column in sharepoint using sharepoint destination

I have loaded details of a customer on a list and was able to load the data with out any problem using sharepoint list destination. We have a lookup column called customerid looking at customer list  . This lookup column is in another list, called Customer Main, so I am trying to load additional data for customer in this customer Main  list which contains a lookup column called customerID. I was not able to load data into this cutsomerid, whereas rest of the data is coming into sharepoint with out any problem. How should I load data into a lookup column. Thanks  

Load and parse a file where the file path is located in a column of a flat file source

Hi, I have a situation where I have a CSV that contains a bunch of data that populates a bunch of related tables with FK constraints directly.  Thats no problem.  The problem is that one of the columns specifies a filepath to an XML file that is supposed to contain data to be populated in a table with an FK constraint.  How do I specify the file name to the XML source component at runtime? For example TableA has columns: TableAID GroupName TableB has columns TableBID TableAID (FK to Table A) MemberID (the data in question) TableA has a 1 to many relationship with Table B. The CSV has a bunch of columns including: TableAID File path to list of members for each group. I don't know how to solve this problem.  I tried using a ForEach ... but I couldn't get it to work. Thanks in advance for any assistance.

Weird Date vs Date/Time issue using a calculated column

I'm attempting to use the fab 40 attendance template. I don't need the time to show - Im able to hide that on the forms with jquery (endusersharepoint.com thank you!!) I WAS ATTEMPTING to create a calculated column called Start Date where the formula simply reads '=[Start Time]' When it's set to display 'Date Only' the date is off by a day. If I switch it to 'Date & Time' I get the correct date. Huh?

SharePoint List Source and Destination BIDS adapters not working for our sharepoint instance

It was with great anticipation I installed the SharePoint List Source and Destination BIDS adapters (from CodePlex, http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652). Unfortunately, the adapters are not working for our MOSS sharepoint instance.  We're a DOD (department of defense) site, so the level of security may be higher than normal and may be affecting the adapters. The error message was lengthy and I couldn't copy it and paste here.  I did screen shots and put the complete error message at http://www.christianbahnsen.com/sharepoint_source_error_message.pdf If I can get this to work I think it would be very useful.  I'd played around with KPI Lists, but one cannot create an Indicator directly from SQL Server 2008 (cf. my related thread at http://social.msdn.microsoft.com/Forums/en/sharepointbi/thread/bcc95f65-012e-4b8a-8850-b03fb0f16b0f).  I was hoping that these adapters would provide a work-around by populating sharepoint lists that could be the source for KPI Indicators. As always, thanks in advance for any assistance. Christian Bahnsen

SSIS - XML Source to SQL Server Destination. How to handle new tags in xml file?

Hi, I've created a SSIS package, which reads my XML file using XML Source, using SCD(Slowly Changing Dimension) to identify whether to do an Insert/Update and everything's fine so far. But there are chances that my xml file will have new tags added apart from what are already there. So In that case, i'm not able to figure out whether to alter the table to add new column or something like that. Need your suggestion. Thanks in advance Vijay

Dynamic Column in Excel Source

Hi,I am having Excel Source Which needs to be imported into Sql Server Table using SSIS.In the Excel Source I dont have Month and Year Column.But in Table I have Month and year column and both the columns are Primary Key columns.So i am not able to Import data from Excel to Table.So is there any possiblities to add Columns Dynamically in Excel source inorder to  get the Year and Month

Copying a Source DB objects into Destination DB with CDC feature...Please help

Hi,     I have a source database 'DB_Srce'. Now, my work is to create a package.If I run the package for the first time,it loads all the objects (tables/SPs/ and etc)  into the DB_Destn databse and the subsequent runs (may be schedules time),it fetches only the modified records (inset/update/delete) to the destination database. I think it involves CDC feature. I am new to SSIS.Could you please help with the steps,please?

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

How to have dynamic column names in an Excel Destination?

I have a situation where both the column names in the source (SQL) and destination (Excel) are being created dynamically. The number of columns, their positions, and datatypes remain the same. How do I change the OLE DB Source and Excel Destination meta-data to handle these changes at run-time?

Saving Page Source

I am wondering if it is possible to programatically save a web page's page source.  Example:From website www.MyTestWebsite.com I want to save www.Google.com's Page Source to a string using C#.  Any suggestions?-NUStudent
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