Hello all, I have build a package from SSIS, Added Data flow task on control flow tab. Now in Data flow tab I have added Flat file source. I want to send that data to OLEDB destination. In between I want to do Data Conversion, I want it in a dynamic way. Can i use script component as transformation and convert the datatype. Please provide me some details. Thanks, Suresh

You could use a script component... The easiest to simply change a datatype is using the Data Conversion or Derived Column transformation. If you need to do some functions such as substring or trim to the column before converting then you'll want to use the Derived Column transformation. If this doesn't answer your question, please give more details on how you want the conversion to be "dynamic". Thanks!Brian

come up with an example of convertion and you used the word DYNAMIC, if you have dynamic as in NUMBER OF COLUMNS , No you can't do this in a DFT , but if you have dynamics as in a result of a function like Script component your answer is yes you can. Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Hi Brian, Yes I want to access all the upstream available columns dynamically and want to change the datatype to unicode before sending to downstream. I actually build entire package in SSIS. So is that possible to do it using Script component like data conversion. Is there any way i can change the column names dynamically using one of the component what i want to change is like first name --> first_name last-name --> last_name I want to do this dynamically. Can any one help me.....

Are the "upstream available columns" going to change from one package execution to another? I need more details to fully answer your question... However: If you simply want Unicode data types, then use the Data Conversion transformation at any point in the Data Flow task that you see fit. Try to give us a better understanding of how and why the columns will be "dynamic" so we can provide the appropriate response. Thanks!Brian

Is there any way i can change the column names dynamically using one of the component what i want to change is like first name --> first_name last-name --> last_name I want to do this dynamically. within DFT no, but i like to point out that if the name of your source is "first name" and wont change to anything else , you can set it to the destination field "first_name" but if in each CSV file you have the headers name change (like "First name" , "FirstName" , "FName" , "FN" , etc...) you cant use DFT in SSIS BUT if the order of the heareds never change , you can ignore the header setting in the CSV FF connection object and in the DFT redirect the header and map Source will be ---> F1 , f2 , f3 .... the above maped to (next line) Sdestination table ---- > "Fisrt_name" , "last_name" , etc..... Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Yes I want to access all the upstream available columns dynamically and want to change the datatype to unicode before sending to downstream. I actually build entire package in SSIS. So is that possible to do it using Script component like data conversion. We need examples, to come up with the right answerSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Yes, I actually has 150 different structure files. The columns and order may change frequently. So Now i need to import them to SQL Server using SSIS. Initially i thought to implement source and destination using script component. But i failed to do it dynamic way. So Now i prepared to create DFT for all the files manually. Atlease if i can make the data conversion part as dynamic way i can copy that to all the DFTs........ Is there any way i can pick the column names from flat file dynamically using script component as source. is there any way i can map columns to table dynamically using script component as destination. Many of the search result i found like this can be implemented programatically. But I have no idea where to start it SSIS or VS.... Plz help me

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/

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/

150 different structure? wow check BCP and bulk insert , what i see from what you are saying is that you realy realy are going to need dynamic every thing i prefer to use BULK INSERT and OPENROWSET etc.... you cant do this in DFT the way you discribed it your first Answer = No not in DFT , out side DFT yes, this is a big project and you need to know and provide very very detailed information and understand SSIS your second Answer = No not in DFT Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

150 different structure? wow check BCP and bulk insert , what i see from what you are saying is that you realy realy are going to need dynamic every thing i prefer to use BULK INSERT and OPENROWSET etc.... you cant do this in DFT the way you discribed it your first Answer = No not in DFT , out side DFT yes, this is a big project and you need to know and provide very very detailed information and understand SSIS your second Answer = No not in DFT Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Once again thanks for replying me all, I am still waiting for some solution. Cozyroc i already checked at my company they won't support 3rd party tools without approval i will get that approval in months time...... if i start request that. But can't wait till then.... sorry In a package SSIS, using script task(in control flow) Can i create dataflowtask and source and destination components ...programatically. Please let me know if it can be done...just give me some guide lines...i will research into it.... Plz

Add the column as a readwrite column and then just override the Input0_ProcessInputRow method with a code like this: // C# code public class ScriptMain : UserComponent { public override void Input0_ProcessInputRow(Input0Buffer Row) { // Do something with the date column Row.DateColumn = /***your code***/ } } Here are plenty of Script Component Examples: http://microsoft-ssis.blogspot.com/search/label/SCRIPT%20COMPONENT If you want to change the data type, you need to add a new column and change your source column to readonly instead of readwrite. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Add the column as a readwrite column and then just override the Input0_ProcessInputRow method with a code like this: // C# code public class ScriptMain : UserComponent { public override void Input0_ProcessInputRow(Input0Buffer Row) { // Do something with the date column Row.DateColumn = /***your code***/ } } Here are plenty of Script Component Examples: http://microsoft-ssis.blogspot.com/search/label/SCRIPT%20COMPONENT If you want to change the data type, you need to add a new column and change your source column to readonly instead of readwrite. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Can any one please provide me code for the script task which replaces a data flow task and can add components into it programatically with in the script task.

None of the above is possible with SSIS. You can't dynamically alter the column names in a data flow - it's statically set at design time. You can't dynamically change data types. You can't dynamically alter the data flow from a Script Task in the control flow. If you need to do any of the above, then: a) SSIS is the wrong tool to use, or b) Your architecture is incorrect Talk to me now on