How to extract the column names from a flat file data

Question

How to extract the column names from a flat file data

jose_anisha

SSC Veteran

Points: 209
More actions
April 8, 2009 at 1:04 am

#208049

We import data from flat files and transfer it to destination table using ssis but often data in the source flat file gets updated with new columns in it which has to be reflected in the destination table each time the source changes ....is there an easy way to acheive this without droping the destination table each time and creating a new one with the updated changes??
Plzz help!!!
🙁

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Tim Mitchell SSCoach Points: 15681 More actions · Answer 1

Yes, you could do this with a Script Component. However, if you allow incoming data files to update your metadata, you run the risk of causing some much bigger problems in your destination database. A better strategy might be to stage any metadata changes to another set of tables for review.

hth,

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

jose_anisha SSC Veteran Points: 209 More actions · Answer 2

Thanks for your help Tim, but i am an absolute beginner, could u plzz elaborate how to send the new updated columns to a temporary table and later add it to the destination table...

Worried

Snehalatha Mannar Old Hand Points: 314 More actions · Answer 3

In order to keep your destination database/ table consistent. Create a staging database and table

( take help from dba to set-up the staging env is you are new to SQL). From the script component load it to the staging tables first and from staging you can do necessary transformation and then load it to the destination.

Tim Mitchell SSCoach Points: 15681 More actions · Answer 4

My recommendation is that if you're dealing with changing metadata, use your script task or script component to manage your staging tables.

There are a couple of ways to do this. You could create the staging table using a SqlCommand object to build your CREATE TABLE statement and call the SqlCommand.ExecuteNonQuery() function to create your table. You can find detailed information about the SqlCommand object at the following link: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx

As an alternative, you can use the SQL Server SMO namespace to create your destination tables. I've got a short video over on JumpstartTV.com about this that may help you out: http://www.jumpstarttv.com/create-database-objects-with-smo_417.aspx?searchid=33299. You can also find good information about the SMO Table object here: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.table_members.aspx

hth,

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices