SSIS – Bulk Inserts dynamically into various tables depending on the input file name.

Tags:
SQL Server 2005
SSIS
I have a scenario where a folder consists of various files with the format 'ABC_DbName_Date_TableName.txt' and there are many Databases with DBNames For e.g. - DB1, DB2 DB3 etc consisting of tables created every day with a 'Date_TableName' format. I need to provide a prompt to accept a folder name and once that is accepted I must loop through each folder and bulk insert the relevant flat file details into the corresponding table of the particular Db mentioned in the file name. How can I provide bulk inserts in a dynamic way. Can I get key points so that I can look into the different features of SSIS. I am new to SSIS and am not sure what is the approach. And even how can be dialogue boxes be created on SSIS. Or must I create some menu using Windows form and link it to SSIS. Thanks New Learner

Answer Wiki

Thanks. We'll let you know when a new response is added.

You can use a .NET script via the Script Task to prompt the user for input by using an inputbox and storing that value into a .NET variable or an SSIS variable.

You can use a Foreach Loop Container to loop through the files in a folder and process them.

Within the loop you will need a .NET object which changes the connection string of the source and destination based on the data within the file names.

Changing the table that you are writing into will be the hard part.

If I was the one putting this together I would probably use a batch file to do everything. Use the FOR command to get the list of files to process. Pass the file name to another batch file which uses the FOR command again to break apart the file name and get the database name and table name. Then use this information to call BCP and load the data via BCP.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following