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

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

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

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :Sql Server
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?

View Complete Post

More Related Resource Links

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    

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

SSIS 2005 - XML Source no data loading to sql server database destination

Hello I am trying to load data from xml source to sql server destination, but the task executes with no data loaded to destination. i really appreciate any guidance in working and troubleshooting xml sources. Regards Harris

Does Transfer SQL Server Objects task transfer objects created in the source AFTER the package has b

I created an SSIS package which contains a Transfer SQL Server Objects Task. I configured this task to copy table objects, stored procedures, and object permissions to the destination. Between the time I created my SSIS package and the time it was run, someone created a new table object in the source, and changed permissions on a stored proc in the source. My question is this, at the time the SSIS package is created, behind the scenes, does SSIS create a list of objects to transfer? I had hoped that it creates the list of what specific objects (of the pre-defined type) to transfer at runtime so that whatever changes were made to the source database would be included at runtime.

Data Flows in SSIS - Mapping Multiple source tables to Destination table **Newb question**

Hi I am new to SSIS and had a basic question. I have around 30+ tables in a db that needs to be migrated to a newer schema in the DB. The data flow task seems to be ideally suited for my requirement. My question is do I need to create 30+ different data flow tasks for this which will get executed one after the other or is there a better way to migrate large number of tables. Also how are referential constraints taken care of during such migration. Thanks and Regards, Ganesh Ranganathan
Ganesh Ranganathan
[Please mark the post as answer if it answers your question]

Saving Date column from XML Source to SQL Destination


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,


How to ensure all the fields from a source file is mapped to a destination file \ table


I am trying to verify if the SSIS package created by someone else has all the fields from source mapped to a destination table columns.

How to do that?

Also are there any best practices for testing for correctness of SSIS package.

Status of Accessing Nested Objects in Data Source of Local Report Does Not Function


We recently upgraded out platform to .NET 4.  We are also using Microsoft Reporting dlls.  Unfortunately, we found that the upgrade broke all of our reports.  Researching the cause, we found this article:


It seems that this issue has been open for some time now and I have not seen a good workaround (except for the shim class which is not really feasible for us).  Can I please get a status update for this?  Is there some other type of resolution?

Thank you,

Benjamin Strum 

Ben Strum

row count for source and destination in ssis



I need an efficient way of counting number of rows coming from source and also going to destination after passing through all the transformations.I know row count transformation is there but as it aggregates and makes the flow very slow with heavey volume, i dont want to use it.

Pls suggest me some good way of counting rows, source can be flat file/excel/access/sql server



SSIS : Insert, Update, Delete records from destination table by comparing records in source table.


Requirement: Inse

rt, Update, Delete records from destination table by comparing records in source table.

Update: if records exist in both the table compare them, and update value in destination table if value is different.

Insert: if record doesn't exist in destination table, add new record in destination table.

Delete: if record exist in destination table but not in source table, delete record from destination table.

----> I have created an SSIS package that does all this task but in order to perform delete operation, I have to store all records of destination table into a temporary table.
And since it's not the most efficient way, I have created another SSIS package by using Lookup & conditional Splits
method, now it works fine with inserting and updating information but I am not sure on how to handle removing record operation!

I would really appreciate if you could help me with this.

(In order to archive inserting and deleting records, I have referred following thread!
http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx )

SSIS Object model - dynamically identifying the source and destination tables of the data flow/ tran


Hello All,

I would like to identifying the source & destination tables of the data flow or the transformation task.

I need to create a custom component which i can put into existing packages which can do some validation.

Please suggest ways i can accomplish this.




OLE DB Source & Destination -cannot convert between unicode and non-unicode string data types


Requesting all  for a solution for this problem.

My Package Components:

  1. OLE DB SOURCE - Connected to Oracle 11g server using Oracle Provider for OLEDB.
  4. OLE DB DESTINATION - Connected to SQL server 2008 R2 -Windows 2008R2 64 bit.
Oracle Source Table:

  [MST_ID] NUMBER(2340089,1) NOT NULL,
  [USER_ID] [varchar2](40) NULL,
  [INS_DT] date NULL,
  [SAVED_FLG_TYPE] [varchar2](20) NULL

SQL Server DestinationTable:

  [MST_ID] [int] IDENTITY(2340089,1) NOT NULL,
  [USER_ID] [varchar](40) NULL,
  [TMP_XML] [varchar](max) NULL,
  [TMP_EDT_FLG] [int] NULL,
  [INS_DT] [datetime2](7) NULL,
  [PAT_ID] [int] NULL,
  [SAVED_FLG_TYPE] [varchar](20) NULL

Error: cannot convert between unicode and non-unicode string data types


Feature Event has 'null' objects

public class Feature1EventReceiver : SPFeatureReceiver { 
 public override void FeatureInstalled(SPFeatureReceiverProperties properties) 
 string sContextNull = (SPContext.Current == null) ? "Context is NULL" : "Context is OK";
 string sFeatureNull = (properties.Feature == null) ? "Feature is NULL" : "Feature is OK";

 // Some code here 

The feature has successfully installed (without error in logs).
My problem is that sContextNull always returns "Context is NULL". And sFeatureNull always returns "Feature is NULL" too.  
Is there a way to get not null values of SPContext.Current and prop

Should I use SSIS or just stored procedure to move data; both source and destination are SQL Server


Please comment the pros and cons of both methods. 


Thanks in advance.

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 ?

Microsoft StyleCop: Source Code Analyzer for C#

StyleCop is similar in many ways to Microsoft Code Analysis (specifically FxCop), but there are some important distinctions. FxCop performs its analysis on compiled binaries, while StyleCop analyzes the source code directly. For this reason, FxCop focuses more on the design of the code, while StyleCop focuses on layout, readability and documentation. Most of that information is stripped away during the compilation process, and thus cannot be analyzed by FxCop.
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