SSIS 2005 - Data Flow work to be done in a dynamic way - Need Help

Question

SSIS 2005 - Data Flow work to be done in a dynamic way - Need Help

suresh.msql

Newbie

Points: 7
More actions
January 24, 2011 at 6:23 pm

#233395

Hi,
I am new to SSIS, I got a hectic requirement at the start itself. Please help me
My requirement is like
*I have 50 Flat files with different structure.
*Each flat file name is equals to data base table name. and first row of flat file is having the column names.
*I want to automate a process to pick the file and load data into the specific table.(I am creating the table dynamically using script task)
I have done all the table creation and all others.
Now i need to get the file using source and need to load the data into SQL SERVER dynamically to the respective table.
Can any one help me,what is the best of doing this dynamically.
1. i have tried with Scirpt component as source and transmission(to convert data) and destination. But failed to code it in dynamic way of columns. i am using SSIS 2005 so i have access to only VB.NET.
2. Setusagetype is not working in script component transformation.
Can any one guide me to right way to finish my task dynamically.
I want to pick the file from source, convert data (dt_str to dt_wstr) and load data to sql server. i have everything in variable (table name, file name...). Created a table structure with empty rows.
Please reply me as quick as possible.

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

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Short answer: You can't. SSIS doesn't allow for dynamic metadata (schema) at runtime.

Longer answer: There are workarounds. Massive Script enable/disable structures on a file by file basis to activate only specific dataflows. Loading it into a table un-split, and then building your own splitter to work against the process and aim it correct in T-SQL. Others that are even more unwieldy.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

COZYROC One Orange Chip Points: 28499 More actions · Answer 2

If you can use third-party solutions, check the commercial CozyRoc Data Flow Task Plus. It is an extension of the standard Data Flow Task and it was created to solve exactly that type of requirement where you want to process source and destination columns dynamically. You can also specify mapping dictionary in-between. Another benefit is that the solution doesn't require programming skills.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

lekha_e_j Newbie Points: 3 More actions · Answer 3

You can do what you are looking for by programatically creating the SSIS package. This way you can avoid the meta data change errors that you would otherwise get.

Eg:

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/64572/