SSIS 2005 - Data Flow work to be done in a dynamic way - Need Help
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.
January 24th, 2011 7:58pm

I believe that CozyRoc may have a task that can handle this, but you will most likely end up paying for the component that gets installed on the server. Another option would be to use Bulk Insert Task, but I have not had much luck with it. If your flat file to SQL table column name mapping is absolutely one-to-one, you might have a shot. Use the Variables at your disposal to configure the Bulk Insert Task at run-time.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
January 24th, 2011 8:06pm

yes, columns names exactly matches the table columns. Right now i can't do bulk insert. Coz i am getting multi code page files some times.
January 24th, 2011 8:18pm

Can i implement that CozyRoc without installing... I mean by adding references to project as class files. I have tried to keep schema.ini file like ODBC driver.... its not working. otherway is at source reading the columns without header. and using script component as transmission tried to change the columns name as per the first record or as per the variable which is having all the columns name with saparator. but failed to do ........ is there any way which i can do programatically..... i actually got confused with scripting. I did google search. many of the code samples provided were building entire package programatically. I have already done all other areas of checks using some tasks in control flow. Now adding data flow part is left. Now tried to add dataflow task through script task but imports were not supporting like pipeline. So tried to added the dataflow task directly from toolbar, and in data flow tried to add three script components as source, transformation and destination....in this case getting an error Component metadata can't be modified .... when i tried with some function like setusagetype.... Can any one help me on this...please provide me any code samples in VB.Net....
Free Windows Admin Tool Kit Click here and download it now
January 24th, 2011 8:25pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics