.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

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

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

View Complete Post

More Related Resource Links

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

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 ?

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]

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.

How capture bad records from "Flat File Source" in SSIS


The flat file is having 10 columns so used 9 delimitor. Few records in the Flat file is having more than 10 columns. I need to redirect in to some error table and rest valid records needs to be loaded in target. Pls help.

SSIS: Error in the package when the data in the flat file source is modified


Hi All,

I have a package which loads data from a flat file source to an OLEDB destination, which is executed successfully and data is loaded perfectly.

But later when the data in the flat file source is modified i.e. if an extra column gets added to the text file, the package is throwing an error because it is unable to detect the extra added column.

How can i avoid this error??? I need my package to execute successfully ignoring the changes(added columns) happened in the flat file source.

Please provide me wth your suggestions and solutions....

Thanks in Advance!!

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 - load table from source text file with multiple record lengths


I have a text file that has mulitple records lengths that I need to load into a table.  The file has multiple record types and each record type detemines the length of the record.  Let me try to explain...the file has a balance line 1, multiple detail records, balance line 2, and a trailer record.

The first record is a balance line 1, which has a record length of 144.

Detail records with a record type of inv or crd, will have a record length of 147.  The remaining record types will have a length of 144.

The second to the last record is balance line 2, which has a a record length of 156.

And the trailer record, which has a record length of 162.

Here is a portion of my source file.  I have made each line bold where you can see the rec type.  The record starting with 9999 is the trailer.

077700001BL100000002010100120101008BALANCE FORWARD 0000000000000000001081175D0000000000{0000000000{0000000000{000001081175D00 0000000000{

077701000INV01953172010100120101008GROCERY - DRY 0000000000000000000073504I0000016678K0000000852G0000000000{000000057679D011N 0000093201E010

07779008099000000002010100820101008KC CASH REC. 0000000000000000001081175M0000000000{0000000000{0000000000{000001081175M00 00000000

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.




SSIS Flat File Destination


I have a requirement to output some large data sets as flat files.

They have specified the file format and I have no issues with all but one thing. They want the data files to end with "the standard EOF character that is used in Linux". When I look at the file I produce with SSIS in a text editor that shows the HEX values the last thing I see in the file is the newlline (0A) chactare for the last line. How can I get it to insert this EOF character they want?

Call a SQL Server function(cleans html tags) on a derived column in SSIS


I have a task to move table having "text" data type for a column from SQL Server to CSV file so while transfering the data using SSIS I want to perform Cleanup on this column such as removing HTML tags, White spaces etc and then transfer it. Note: I dont have any option to store the table in the staging database as I will be running it on the production server directly.

Can any one help me accomplish this task???





Can I Describe a Flat File Source to Save SSIS User Some Effort


I'm a complete SSIS beginner.  I suspect the answer to my question is out there I just don't know enough to construct the search that would find it.

I'm writing a program to generate fixed width format text files from proprietary databases for use as Flat File Sources in SSIS packages.  I know I can provide a header row containing column names.  But I have a lot more information about the file (obviously!).  I know how many columns there are, what the width of each column is, what the type of each column is, etc.

Is there some way I can record the information I have so that the SSIS user consuming my file doesn't have to input all that information through the Import and Export Wizard? 

For example, if I were generating these files for use with the BCP utility I could create a Format File to describe the structure of each field in my data file.  Is there some similar capability in SSIS or do the consumers of my data files have to input InputColumnWidth, Data Type etc. through the Advanced editor dialog box?

Foreach Loop Container & File System Task to copy files from target server to source server


A one-off shot at using, SSIS, I appear to be missing something. 

From what I have read here, the Foreach Loop Container in which a File System Task has been placed is how one copies files contained in a source server folder to a folder on a target server.  I have defined Source and Destination pkg variables using UNC \\servername\aharedfolder and a FileName variable that is empty.  My impression is that this is suppose to iterate through and copies all files in the source folder to the target.  I only get the first file copied.

In the FLC Collection section  I am using the Foreach File Enumerator under which I have the Folder as \\servername\sharedfolder and files as *.* .  I am not using Expressions.  The Variables Mapping reference my FileName variable.

In the FST, I both path variables set to True and reference the destination and source variables described above.  Operation is Copy File.

I appears that I need to pass a list of the source folder file names to the FileName variable, rather than some built-in logic grabbing a files in the source folder.  Is a passed filename list what I am missing?

SSIS 2008 - Question on OLEDB Destination to SQL Server 2000

Simple question, In a SSIS 2008 Package is it possible to have a OLE DB Destination pointing to another Server Machine running on SQL Server 2000 to do bulk insert operation ? . what are the potential pitfalls when while inserting into SQL Server 2000 ?

SSIS 2008 R2 Flat File Destination Producing Duplicates


I am writing to a flat file destination in a 2008 SSIS package. 99.99% of it works correctly. However, I get one duplicate record in the destination file.


Here is the basic flow of the package:

 1. Read two ISO-8859-1 encoded files and encode their text to UTF8 in memory

 2. Combine the two files together in memory and load them into a lookup cache

 3. Read another source file from disk

 4. Match an ID column

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