Dynamic columns mapping – Script Component as Destination (SSIS)

Requirement:

In Data Flow task of SSIS, we have to define a Data Source as well as a destination to extract & load the data respectively. And as per the functionality of data flow task (DFT), metadata of the source fields has to be defined at the design time. That’s the input columns from the source must be mapped to the destination columns and for that we must know the schema of the input as well as the destination/output columns.

But in many business scenarios we come across the requirement where at runtime only we come to know about the structure of the input columns and thus at runtime only we have to decide for the destination columns, this is called dynamic mapping.

Solution:

This type of dynamic mapping for source to destination can be achieved using script component. This script component can be used in three ways in data flow task:

  1. As a Transformation,
  2. As a Data Source
  3. As a Destination

When we drag & drop the script component from tool box, a dialog box will appear to choose the required type.

Script_Component_Type

Script_Component_Type

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1.  Transformation: Script component can be used to do some transformations on the input data, processing each & every row one by one. We can write custom code/script in it to make the required operations on the data. In this case total number of output columns would be same as that of input columns.

2.  Source: If we select script component to be used as a Data Source, we can configure the component to generate the required number of rows as the output. In this case we can add as many columns we want in the output of the script component and then at runtime we can write code/script to add the rows in the output. We can also have multiple asynchronous outputs with different-2 names which can be loaded to different-2 destinations.

3.  Destination: Sometimes we don’t want to dump/load the input data directly to the destination like OLEDB destination or Flat file destination; rather we want to decide for the number of columns/rows somewhere before actually loading the data. Here we can choose script component as a destination.

Script component should be used as a source, in case we are sure about the schema/columns of the input records. But in the component’s script editor we can process the data & generate the output rows based upon certain conditions.  But here also we have to map the output of this component to the destination columns at design time only.

Now in case; we want to have dynamic mapping at runtime, we can configure Script component as a Destination. In this mode of component we can look at the input data & decide about the structure of the fields to be loaded into the final destination, based upon certain conditions which can be stored in database or variables.

Configuring Script Component as a Destination

Adding Connection Managers: Since in the script component itself, we are going to load data into the final destination, we need the connection manager(s) to connect to the final destination e.g. Flat File connection manager, OLE DB connection manager etc. On Connection Managers page of the Script Component Editor, we can add the already created connection manager or also can add new connection managers. All the added connection managers would be available in the script via Me.Connections. connectionname (vb.net) & this.Connections.connectionname (C#).

Input/ Output columns and Variables: We can configure the input columns, output columns as well as variables as is done in the normal script task or component.

Scripting the Destination Component: Its main configuration is the custom script (vb.net or C#) which is used to process the input data, decide over the number/ type of destination columns and then load them into the final destination.

There are many functions/subroutines which we can override in the script to acquire the added connection managers, process the input data, form the query at runtime & load the data into the destination etc.

Example: Let’s take an example in which we add one ADO.Net connection manager ‘myConnection’ to execute a query at the SQL server destination.

Note: Here we will connect to SQL Server via a managed connection manager (ADO.Net) because AcquireConnections function works for these only. Otherwise we’ll have to create connection in the script only; using the connection string of the defined connection manager.

Imports System.Data.SqlClient

Public Class ScriptMain

    Inherits UserComponent

Dim conMgr As IDTSConnectionManager100

    Dim sqlConn As SqlConnection

    Dim sqlCmd As SqlCommand

 Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

  conMgr = Me.Connections.MyConnection

  sqlConn = CType(conMgr.AcquireConnection(Nothing), SqlConnection)

     End Sub

     Public Overrides Sub PreExecute()

         sqlCmd = New SqlCommand(“INSERT INTO MyTable(Col1, Col2) ” & _

            “VALUES(@Value1, @Value2)“, sqlConn)

        sqlParam = New SqlParameter(“@Value1“, SqlDbType.Int)

        sqlCmd.Parameters.Add(sqlParam)

        sqlParam = New SqlParameter(“@Value2“, SqlDbType.Int)

        sqlCmd.Parameters.Add(sqlParam)

     End Sub

     Public Overrides Sub MyAddressInput_ProcessInputRow(ByVal Row As MyAddressInputBuffer)

        With sqlCmd

            .Parameters(“@Value1“).Value = Row.ID

            .Parameters(“@Value2“).Value = Row.Count

            .ExecuteNonQuery()

        End With

    End Sub

     Public Overrides Sub ReleaseConnections()

         conMgr.ReleaseConnection(sqlConn)

     End Sub

 End Class

 Thus we can process the input columns, make connections and based upon required conditions; we can build our own queries to dynamically map them to the destination. In the similar way we can write to Flat file destination as well.

Advertisements
This entry was posted in SQL Server, SSIS. Bookmark the permalink.

188 Responses to Dynamic columns mapping – Script Component as Destination (SSIS)

  1. Shaul Sondak says:

    RE: Dynamic columns mapping – Script Component as Destination (SSIS)

    Hello Munish,

    I found your blog today when searching for some SSIS related questions. I appreciate it a lot. It is clear and concise.

    Is it possible that you send me a sample DTSX file relating to this topic?

    Thanks!

    Shaul Sondak
    CIT Rail
    312-906-5745
    Shaul.Sondak@cit.com

  2. Hey Shaul,
    Thanks a lot for your comment, Will send you the .dtsx file (SSIS package) via an email soon.

    Thanks,
    Munish Bansal

  3. Praths says:

    Hi Munish,

    I’m looking at utilizing single ssis package for different interface (i.e different source file and destination table).

    Could please send me the .dtsx file to my email id.

    Thanks
    Prathibha

  4. huyiuk says:

    HI, Munish

    Very interesting your article. I need to create some kind of Dinamic DataFLow, that reads from a configuration file
    the columns to recover and the table to load.

    I think your code can help me alot.

    If you can provide me, i will thank you a lot.

    Thks very much in advance.

    Carlos

  5. test says:

    Please send me the sample code asap at my email code
    viveksh47@hotmail.com

    Thanks

  6. Anup says:

    Hi,

    Found your blog very useful. Can you send me the dtsx file in my email?

    Thanks,
    Anup.

  7. Andre says:

    Hi,

    Found your blog and is very useful. but I need extract by query that doesn’t repeat to excel.

    My problem

    I Have a table that is Query repository and with ( For Each Loop Task ) I get a Query and put in variable and Would have run a Data Flow Task ( SQL to EXCEL ) .

    Could You Help me with template .dtsx ?

    Thank you

    Andre

  8. NewSSISUser says:

    Hi Munish,
    I came accross your blog while searching for a solution and your article provided me with a hope that it can be done.

    Here is my problem.

    I have a CSV which provides a format.
    eg:
    Table_name, Field_offset, Field_Length, Field_Name
    Table1, 1, 3, Column1
    Table1, 4, 9, Column2
    Table1, 13, 5, Column3
    Table1, 18 2, Column4
    .
    .
    .

    This format file has about 200 columns.

    I have a flxed length flat file which holds the data. The name of this flat file changes daily.

    How do I set the FlatFile connection properties adding columns and its offset and length dynamically for the source and read the data from the flat file?

    Your help and any relevant example is much appreciated.

    Thank you in advance.

    NewSSISUser

  9. @NewSSISUser

    See for making the flat file connection dynamic, you can use expressions in the connection string property of the same or using ForEachLoop for File Enumerator you can read the file.
    Now you can configure the connection to get the complete row of the file into a single column with pre-defined data type of say VARCHAR(max).
    Then by means of a SP or so, you can split the data based on the actual delimiter of the columns and put them into another table.

    Thanks,
    Munish Bansal

  10. Anand Iyer says:

    Hi,

    Need an help in SSIS package as I am totally new. Requirement is simple. I have an SSIS package which sends out a mail once it is successful or failure. It accepts three flat files, does a minimum transform and loads into staging and production table. Once successful, sends out a email repprt to group.
    All email reports are generated using xslt file. It uses ADO.NET recordset, extracting data into an xml file. Pacakge then applies xslt template to the xml file to generate a
    report.
    Users finds these emails annoying. They want email only when there are errors. Errors could be either files not found or data constraint error between staging and production tables.

    how to achieve this?

  11. Hi Anand,

    The only modification, you need to do with your package is to change the places wherever you have placed ‘Send Mail’ tasks.

    So let’s say, if Flie not found error occurs, your ‘File System Task’ will fail and hence you can configure the precedence constraint for Failure and after that only put the Send Mail task.

    In other case, wherein you are getting errors in DFT (in between source & destination), you have two ways to track the error and send the emails out:
    1. You can use the Event Handlers. There you can select the OnError event and place the Send Mail task there. Or

    2. You can set one variable on occurance of an error in the DTF. And then after completion of the DTF, in Control Flow pane, you can check the value of that variable to know if some error occurred or not and send email accrodingly.

    Thanks,
    Munish Bansal

  12. Malathy says:

    Hi Munish,

    I am looking something like this for dynamic column mapping. If you could send me the sample package thru mail, it would be great help.

    Thanks in advance,

  13. kk says:

    Hi Munish ,

    I’m totally new to the SSIS stuff. I’ve posted my problem in the link below. I just want a simple data transformation/conversion component without changing much of my code in the link below (as i’m running out of time now).
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138216

    I ‘d really appreciate if you could please help me figure out how to do it.i’ve used this link also by customising my code, http://www.sqlservercentral.com/Forums/Topic554119-148-1.aspx#top

    it works but how do i map IDTSOutputColumn90 column (created in the above link) to the external metadata column?After getting the tranformed IDTSOutputColumn90 column in each loop …how do i map it to the respective destination column

    Please reply urgently…

    Regards,
    KK

  14. Kavi says:

    Hi munish,

    Iam calling the SSIS Package form .net code. I am passing a excel file each time. so my package has to create a table with a predefined table name with the columns from the Excel File and load the data in to that.

    Please help me to do this….

    Regards,

    Kavi.

  15. Hi Kavi,

    I think you should pass path (with excel file name) where the excel with a pre-defined table/sheet name is to be created. And then in the package use an ‘Execute SQL Task’ configured with the path being passed as an Excel/OLE DB connection and in the query part, put the query as (as per the structure required):

    CREATE TABLE `TableName`
    (
    `1Col1` Integer,
    ………
    ………
    )

    You can also use a ‘File System Task’ before the Execute SQL Task to delete the existing excel file at the specified path provided.

    Thanks,
    Munish Bansal

  16. Kavi says:

    Munish,

    i didnot get your solution.Let me explain my case.
    we have a predifined path of the xls file and a filename.

    everything is dynamically called from the .NET code, Even the SSIS package is invoked from the .NET code.

    The Columns in the excel sheet may vary.
    so how do i make my SSIS package to map my columns of the excel sheet to the dynamically created table.

    i create table columns of the excel sheet dynamically by reading the excel headers in the .NET code.

    I also have the path of the file

    Now how do i map my target table as the mapping for the SSIS requires the columns to be same as the excel sheets columns

  17. Stew says:

    Good blog post…interested in the dtsx file. Could you email it to me. Thanks.

  18. Shivraj says:

    Hello Munish,

    Really good post.
    Can you please mail me dtsx file.

    Regards,
    Shivraj

  19. Hello Shivraj,

    Actually the one which I created earlier is not with me now…so will get some time and create that again. And then share with you all.

    Thanks for your comment !

  20. Nita says:

    Hi Munish,

    I found this is good blog.

    Could you please email me the .dtsx/SSIS package of “Dynamic columns mapping – Script Component as Destination (SSIS)” to nita.sun22@gmail.com.

    I need it ASAP. Actually my requirement is reading data from multiple Flat files (.txt) and import it into multiple tables. So I need do column mapping dynamically using script component.

    Thanks in advance

    Nita

  21. nita says:

    Hello Munish,

    I think your blog will help me to solve my issue.

    I have similar kind of issue to solve. I need to read several .txt file(each text file has different number of columns) and these text files data to be loaded into sql server destination(temporary tables/table) dynamically.

    I took a for each loop and configured it and created a variable to store the text file names. And in the data flow task, I took single flat file source and single oledb destination. but I am unable to do the mapping in the scirpt component, as I am confused using the in using the script component. As you said, if I take the script e component as destination, do i need to give the connection magers property — destination connection manager? what do I need to give in the input/output or input of script component and I guess my flat file source should be as it is or do i need to do the flat file connection though the coding by editing the script.

    Could please help me doing this. I am trying alot, I am confused using script component and coding it it.

    Thanks alot
    nita.

  22. Samit Shah says:

    Hi Munish,

    I have package for validating the cube data of staging and publish db. Now to get the data from cube I have written an MDX query where I have an hierarchy which is ragged. I am using this MDX query in Datareader. There is different set of level for each year of the ragged hierarchy. If I am hardcoding the year in the MDX query it works fine. But when I change it to fetch the year value from variable, the column mappings for all the year is not same. Could you pls. help me how to go dynamic column mapping

    Thanks and Regards,
    Samit Shah

  23. Bimal says:

    Hi Munish

    Found your blog on SSIS Dynamic column mapping very useful.

    Can you please send me the dtsx file to my email?

    Kind Regards

    Bimal

  24. Orlando Asher says:

    Hi Munish,

    Found the blog fascinating “Dynamic columns mapping – Script Component as Destination (SSIS)”

    Could you please send the dtsx file to my email?

    Thanks,
    Orlando

  25. Shampa says:

    Munish,can you email the dtsx to me also?

  26. Hady says:

    HI Munish
    Thanks for the Post .
    can you send me the Package by e-mail ?

    Thanks

  27. Chander says:

    Munish,

    I have precisely the same requirements as in your article. I will really appreciate if you could please send me the sample package developed by you.

    Thanks
    Chander

  28. vaibhavi sawant says:

    hello
    Munish,
    i read ur bolg its very useful.
    i was having one doubt regarding ssis pkg.
    i am using simple ssis pkg for uploading excel data to sql table.
    if i dont map one particular column of sql table with excel column,it should return me null value for that column.
    but m getting mixed data if i do such thing in my package.
    Can u help me out.??
    Please do reply for this
    urgently… 🙂

  29. vaibhavi sawant says:

    hello munish,
    can u send me above dts pkg on my email id
    sawantvaibhavi22@gmail.com or sawantvaibhavi@yahoo.co.in
    c ya takecare.
    Kp posting 🙂

  30. Hi Vaibhavi,

    As far as I understood your requirement, you can simply ignore the unwanted column(s) from the source’s (DFT) selection list. Also if you want to have some specific values (like null) to be inserted in the Data table (SQL Table) for those columns, either you can use ‘DerivedColumn’ transformation to add one more column with the desired value in the package or define the default value for such columns in the database itself.

    Hope this helps…

    Thanks for your comments…
    Munish Bansal

  31. Lina Li says:

    Hi Munish,

    I am so glad I found your blog, I always wonder if we can create “Dynamic Column” for the data transferring. Can you send me a sample code relating to dynamic source?

    Thanks!
    Lina

  32. Sri says:

    Hi Munish,

    This blog was very helpful. Could you please email me the dtsx package to srivatsa.chandrasekaran@gmail.com.

    Your help is much appreciated!

    Thanks,
    Sri

  33. Gary says:

    Hi Munish,

    Could you please send me the sample code to achieve Dynamic columns mapping?

    imtangram@hotmail.com

    Thanks a lot

  34. Simran says:

    Hi Munish,

    I have 2 csv files with different columns
    and the destination table is one.

    Csvone contains label,name,description
    Csvtwo contains label,parent,name,description…..

    Destination table is one.
    How do i go abt this.
    Also send me ur sample dtsx

  35. Hi Simran,

    Since you already know the structure of both of your CSV files…it can be easily implemented…but I did not get the exact requirement since the target table is the same for both of these input files.
    Please elaborate more on the requirement front…so that I may help you on the same.

    Thanks,
    Munish Bansal

  36. Pankaj Pathak says:

    HI Munish,
    I am new to SSIS . I want to FTP , unzip & import csv file in SSIS , occasionally there might be a new filed added in the csv file . I need to read the csv file and check in the database if this records in new record or if the record is existing with name change or if the record is existing record with status change etc. based on the these condition i need to generate the different output files. The newly added field should also go in one of the output file. What is way to model this in SSIS . Could you please share some sample dtsx on dynamically adding column and handing these type of conditions.

    Please help i am stuck ..
    regs
    Pankaj.

  37. Pankaj Pathak says:

    forgot to leave the email id in previous post .
    its pathakp@yahoo.com .

    1. FTP and unzip the csv file .

    2.make an entry of CSV file , so it should not imported twice

    3. CSV file in enclosed by ” we might have to remove those .

    4. Not sure if csv file should be loded in a table ?

    5. for each records in csv file , check if its a
    – New record ( i.e does not exists in one of the table )
    – If existng records is the name changed . ( one of the field in csv )
    – If existng records is the Status changed ( one of the field in csv )
    – If existng records is the salarychanged ( one of the field in csv )

    6. based on the above condition , the output should go the different output file ( flat file ).

    7. In case a new field added it should go in out put file as well .

    8. Send email and logging .

    i am not sure how do this is SSIS , being a newbie i tried playing , but no luck so far . any help appreciated …

    Please share the dtx sample project (s) . i will have a look and try to model something similar .

    Thanks & regards.
    Pankaj.

    4.

  38. Lina Li says:

    Hi Munish,

    I am asking how we can create “Dynamic Column” for the data transferring. For example, I have a couple of input files:
    1. input_1.csv
    John, Smith, 19800502, abc
    Joan, Garcia, 19700801, efg
    David, Yang, 19600901,xyz

    2. input2_1.csv
    Mary, Pippen, 19800601, abc, 6265551112
    Jason, Wang, 19800101, ccc, 8189991112

    3. output_table
    first_name,
    last_name,
    birth_date,
    note,
    phone (Allow NULL)

    I have no problems if the input is a table, I can use a SQL sentence but I can not do for flat files. Please let me know if its possible.

    Thank you cery much!

  39. Hi Pankaj,

    Assuming, you are well aware of FTP task, so using that get the source CSV files to a local folder, probably by first deleting the existing ones.
    now you should dump the file data into some intermediatory/staging table (with one column as varchar(max)), by configuring the Flat File connection manager with column delimiter as CR-LF which to consider the whole row as one column.
    Here you can split the values based upon the delimiter and compare or examine the data as if it has changed or not probably using Lookup transformation etc.

    I could not get from your post whether you get CSV files with added/deleted fields or just with the changed values.

    Thanks,
    Munish Bansal

  40. Pankaj Pathak says:

    Hi Munish ,
    Thanks a lot for the reply. Basically in the csv file the new field will be added. Also on can you explain little more on the look up transformation on how it will write to different files based on the condition checked.

    regards~ Pankaj

  41. Hi Pankaj,

    Since new fields are added, first using some T-SQL, you can be sure which output file it should be put to, and probably set some variable accordingly using script. Then based upon that variable’s value you can have diff-2 paths to destination thorugh the lookup transformation (by deciding using Conditional Split).
    In Lookup transformation, you can ref. some table (or output file if using 2008) to check for exisiting records or so.
    you can also select overwrite option in Flat File connection for destination or can first delete the output file & create a new one everytime.

    Thanks,
    Munish Bansal

  42. Hi Lina,

    If you have got a very few no. of input file types, i would suggest you to create individual packages for all of them. That’s the best option if you are having huge data records in the input files.
    Else if you want to have one package for all of them, then as explained in some of my earlier comments, that you can dump the data into some staging table (with one column as varchar(max) having configured with column delimter as CR-LF and the using an SP, you can split them based upon the actual colum delimiter.
    And looking at columns’ number, you can then insert them into the destination table.

    Thanks,
    Munish Bansal

  43. Pankaj says:

    Munish,
    Thanks a lot man you are doing a great job . Would be of grat help if you can send me sample dtx file to refer.

    Email Id – pathakp@yahoo.com
    ~Pankaj

  44. Pankaj says:

    Hi Munish,
    One more thing in case i want to validate the csv file for
    Data Type , Length , Format , Not Null , Out of Range Value etc . What is the best way to achive the same in SSIS ?

    Thanks
    ~Pankaj.

  45. George says:

    Would really like to see a copy of this in action, will you please forward it?

    Thanks in advance.

  46. Hey Munish

    I can handle as null in this case

    .Parameters(“@Value1“).Value = Row.ID

    for example something like that:

    if isdbnull(ID) then
    .Parameters(“@Value1“).Value = null
    else
    .Parameters(“@Value1“).Value = Row.Id
    end if

  47. Jegan says:

    Hi Munish,
    Thanks for the nice post.

    Just wanted to know more on how much performant this approach is. I guess this would be as performant as .Net code and may not take advantage of SSIS itself.

    Am I missing something?

  48. Srinevasalu says:

    Hi Munish

    Could you please send me the code, it seems very usefull but looking at the article have a little confusion.

  49. Sunil Parekh says:

    Good to see SSIS articles at your posts. Keep it up.

  50. Thanks a lot, Sunil Sir.
    Good to see your comment as well 🙂

    Thanks,
    Munish Bansal

  51. Martin Kihl says:

    Hi

    Great post. Can you please send me the example too and it would help a lot.

    All the best!

    /Martin

  52. kmadh says:

    Munish,

    I am new to SSIS and i have a requirement is that a package should have the capability to log the success and failure during conversion. Also should have the ability to re-run the tool; means the package should start from stop point (the failure point). Could you please let me know how to do this.

    Thanks in Advance
    kmadh

  53. Kishore says:

    Hi Munish,

    Its great and interesting article, it gives me confidence to implement Dynamic DataFlow transformation using SSIS.

    Could you please send me your sample .dtsx for my reference.

    Thanking you in anticipation.

    Regards,
    Kishore

  54. Wayne says:

    Hi Munish,

    This looks exactly what I need to do – I just need to figure out the rest of it. I have a dynamically generated query being put into a table, and from there it is exported to an Excel spreadsheet. It looks like this will handle it?

    Since a lot of people have asked for the dtsx file to be mailed to them, it might be good to provide a link for it. Until then, can you email me the dtsx file so that I can look it over in more detail?

    Thanks,
    Wayne

  55. JVelten says:

    Hi Munish,

    I am trying to do some consolidating of reports and was looking for information of creating the dynamically when I ran across this article. Could you please email me the dtsx? I think it will be very helpful.

    Thanks,
    Joe
    candycorn_lq@yahoo.com

  56. Anand Vanam says:

    I have gone through your Article about Dynamic Column Mapping in SSIS.

    I will get 10 Flat files every day. I have 10 database tables whose schema is according to those 10 flat files.

    I am loading Data from Flat File source to SQL server Table (OLEdb Destination. I created a SSIS package with one data flow task, which dynamic flat file and OLEdb Destination.

    I want to change the name of the Flatfile Name and Table Name for every run through configuration File. When I change the Names of FlatFile and Database Table (of different schema ). I got an Error stating VS_NEEDSNEWMETADATA.

    So can you please provide me any suggestions or any dtsx package file to overcome this issue.
    Can you please send the dtsx package file and config file to my mail id.

  57. Srinivas says:

    Hi Munish Bansal,

    Looks like this is a great article. I have similar situation. Where I receive about 400 xml files daily. We have about 40 different varitey of xml files to be loaded in 40 tables. I was trying to dynamically create package, so that with one data flow task and configuration from a table can be done. Various attempts failed, but looks like your idea is good. Can you send me a sample package, that would be really helpful for me and saves time.

    Thanks
    Srinivas Daram

  58. Preen says:

    hey Munish,

    I recently started working on SSIS.
    I was searching for something related to dynamic report mapping and passing parameters into it. I guess this article will help me in accopmplishing my task.

    It would be great if you could send me a sample DTSX file relating to this topic if possible.

  59. Britto says:

    Hi,

    I’m also asking the same, can you please send the dtsx or the script for mapping to my id wenischbritto23@gmail.com

    Thanks
    Britto.S

  60. raman says:

    Munish

    I do have a similar situation. I am having my source as dynamic and destination columns would change based on the source query. since my destination consist of mosly 2007 excel more than 65000 rows or csv file, i need a dynamic script which can automatically map the source columns to the destination.

  61. Prakash says:

    Hi Munish,

    Nice article!!!

    Could you please send me your sample .dtsx to my EmailID rawatprakash@gmail.com

    Regards,
    Prakash

  62. RA says:

    Hi Munish,

    Could you please send me your sample .dtsx to my EmailID
    rubyagar@gmail.com

    Regards,
    Ruby

  63. archana says:

    Hi,

    I have a similar requirement for dynamic column mapping. Please send me the sample .dtsx to the email id jha_arch@rediffmail.com.

    I appreciate the help!

    Thanks
    Archana

  64. rajiv says:

    Hi Munish,
    I am totally new in SSIS …requirement is simple ..source is table and destination is Flat file(blank text file)….flat file is blank it does’nt have any column name ..so i have to create the column name dynamically (from sql table)…what ever column name are in sql table…need to create in flat file…..if you can email me any sample .dtsx to my email rajivkumar1324@gmail.com.

    Thanks
    Rajiv

  65. chhavi says:

    I have a question on SSIS.
    I have got my result into a table in a server. Now I want that table data to be loaded into different tables present in different servers. For example- if I have data for a country – “Canada”,it should go to a server usftw209, if I have data for a country – “Hongkong”,it should go to a server usftw052 and so on.
    Do u have any idea, how we are going to do this dynamically?

    Thanks,
    Chhavi

  66. Prakash Tamilarasan says:

    Hi munish,

    Good Stuff.

    Could you please send me the Dynamic columns mapping – Script Component as Destination (SSIS).dtsx file to prakash.tamilarasan@gmail.com ?

    Thanks,
    Prakash Tamilarasan

  67. Preen says:

    Hi Munish,

    I am working on the similar problem. Could you please send me the dtsx to me so that it would solve my problem.
    I will really appriciate your help.

    preensheen@gmail.com

    Thanks

  68. Raj says:

    Hi Munish,

    Great work!!!

    Could you please provide me your sample .dtsx to my EmailID “rajajseelan@gmail.com”.

    Many Thanks,
    Raj.

  69. mamta says:

    Hi,

    I am new to SSIS my requirement is store the XML files to Data-warehouse but the parent ID and Child IDs are Different How to map the Ids which are randomly system generated.

    and Second issue is how to store the one column from xml file to a variable.

    please help me with this scenario

  70. Mekala says:

    Hi Munish,

    I am nwe to SSIS package. Started working for the past three weeks.
    Having doubt with Logging and validation for CSV files.
    I ll my issue explain clearly ..Data flow ism
    CSV file date ->SQL Table
    1)Having 4 different CSV files and SQL tables also different.
    2) I want to validate the source file format and datatype fields. If the format is wrong i have to enter some comments(format is correct) in error log files or else i have to write “no bad file format ” message in log file.
    3) and How to validate and write the log file which the data fields in the CSV file having wrong data.

    Please help me out.
    Pls mail me if u have any sample reg this. mekalagobi.mca@gmail.com

    Thanks
    Mekala

  71. Vinay says:

    Hi munish,

    my problem is :

    1.Declare a variable called “Year” and value is 2010 (for a testing purpose)this may vary generally.
    2. using the Script Component in data flow i have to change the column names to have “Year” value at the end of each column i.e like _2010 i.e JAN_2010 FEB_2010 MAR_2010 … DEC_2010(2010 should come from variable)
    3. what needs to be coded inside a method called “ProcessInputRow” in script component.

    I’m also asking the same, can you please send the dtsx or the script for mapping to my id.

  72. rk says:

    Hello Munish,

    Good work. Can you also send me the required code and package. I am looking for similar things to accomplish.

    thanks
    rk

  73. Mustafa Lokhandwala says:

    Hi Munish,
    I would really appreciate if you could send me the DTSX file relating to this topic.

    Thanks
    Mustafa
    mustafalokhandwala479@gmail.com

  74. Radik says:

    Hi I would be very grateful if you could send me a .DTSX sample because I’m trying to solve a similar problem.

    My email is radik86@gmx.de

    Thanks Radik

  75. Jason says:

    Hell Munish,

    I am really interested to learn how this works. If you don’t mind, could you please send me a copy of .DTSX sample?

    Much appreciated!!!

    Jason

  76. Zach says:

    Hi Munish,
    Hi I would be very grateful if you could send me the .DTSX sample because I’m facing a similar challenge.

    Thanks!
    Zach

  77. Gary says:

    Munish, would also very much appreciate email of package to gmelhaff@comcast.net

    Still unsure how the @Value1 isn’t merely hardcoding each column into the script component but very much need to study this solution.

    The need…Run over a hundred delta capture staging jobs where the Oracle source and SqlServer target tables and column mappings change for every package but other than that everything else is a follows just a few patterns.

    Goal…make just a few packages, 1 for each staging pattern where the source/target are totally dynamic so the metadata is populated at runtime, over 100 unique package executions could become just a few at design time. Maybe I’m dreaming but wanted to explore the possibility.

  78. Mag says:

    Hi Munish,

    I would be very happyl if you could send me the .DTSX sample because I’m facing a similar challenge.

    Thanks!

    Mag

  79. Zi says:

    Hi Munish,

    Would you please send me the DTSX sample? Your assistance will be greatly appreciated.

    Thank you.

    Zi

  80. Karan says:

    In the SSIS package i used the excel 2007 as source and SQL 2005 as destincation for ETL .

    When i changed the source file name and also make the change in config file it fails.

  81. Karan says:

    SO please let me know how can i manage the change in source file name without changing the variable value where i provided the excel file path.

    Really appreciate you help munish

  82. SG says:

    Hi,

    I have a similar situation and will appreciate your help in that. We are storing source queries / stored procedure and mapping between source and destination column in a database and trying to create DFT which will execute the source queries and then map columns based on the mapping stored in the database. The number of source columns can vary in different sources. I was able to execute different queries when the source columns name were not changing but not able to find solution when number of source columns or their name are changing.
    Can you suggest any solution for this problem

    Thanks

    SG

  83. Lisset says:

    Hi Munich,

    If possible that you can send me the .dtsx file to my email id.

    Thanks a lot!

  84. Astha Agarwal says:

    Hi Munish,

    Great work!!!

    Could you please provide me your sample .dtsx to my EmailID “agrawalastha5@gmail.com”.

    Many Thanks,
    Aastha

  85. satish says:

    Hi Munish,

    I have a doubt ..Suppose i have a Query like
    Select Table_name from Information_Schema.tables
    The result would be like the Tables Collection.
    I have configred the Foreachloop container and defined an environment variables for looping the tables.

    I need create a csv file by execting the Query “Select * from ” [user: GettableName] .
    The problem is i am unable to map the columns to in the data flow task.

    Can u share your sample .dtsx file so that i will try to workout Satishkumar.gourabathina@gmail.com

    Thanks
    Satish

  86. Gautami says:

    Hello Munish,

    Your blog is very interesting.I found it very helpful.
    could you please tell me how to map the tables and colums dynamically.Please send me the sample that you have created for dynamic mapping.

    Thanks a lot in Advance
    Gautami

  87. Vijay says:

    Hi Munish,
    Nice blog,beautifully explained.Thank you.
    Can you please explain how to use script component as excel source so as to read the cells dynamically at run time
    Thank You,

  88. Kavita says:

    Can you please email me the dtsx file.

    I appreciate
    Thanks.

  89. sandeep pandey says:

    Hi Munish,

    While searching on SSIS i came to your blog. And It’s really very good article posted by you. And It’s matching with my requirement as well.

    I have created one SSIS solution to Read csv files from a given folder. The main thing is that csv file have 5 fix column and 1-12 dynamic column according to month for which we are processing. I wanted to do on the basis of columns in month in input file data will be processed.

    It it’s possible to you please send me your ssis package (.dtsx file) on my emailID

    er.sandeepp@gmail.com

    Thanks

  90. SethuRaman says:

    Hi,

    It is interesting to know that we can map columns dynamically

    Please send me the sample package to my email.

    I have a requirement to load the data from a flat file where the no. of columns may vary (increase or decrease)

    It will be helpful if you send me the sample package

    Thanks in advance

    Regards,
    Sethu

  91. Axel F. says:

    Hi Munish,

    is it possible to get the sample dtsx package via email or downloadlink?
    Keep up the superb postings. We all really appreciate that!

    Regards,

    Axel

  92. Mubin says:

    Hi Munish,

    It is great to know that we can map columns dynamically in SSIS

    Please send me the sample package to my email id:. mubin.shaikh77@hotmail.com

    I have a requirement to load the data from a ms excel file where the no. of columns may vary (increase or decrease) in to staging table. and after doing process i want to distribute that data across several table.Kindly guide me .

    It will be very helpful if you send me the sample package

    Thanks in advance

    Thanks & Regards,
    Mubin

  93. Kratos42 says:

    Hi Munish
    Great work, i read the comments. Almost everybody ask for the dtsx file. Can you send it me via email please ? (mehdi42180@hotmail.fr)
    I think you should upload it because it is quite interesting

    Regards

  94. Jason says:

    Hi Munish,

    I’ve been looking for a way to systematically archive data from our Data Warehouse. I believe this is what I’m looking for.

    If you don’t mind, would you be willing to send me your dtsx example?

    Thank you very much!

  95. Swetha says:

    Hi ,

    Really goood article.

    I have a problem with Data flow task performace. It takes 45 mins to execute as there are 6000 rows. It works fine for lesser number of rows.

    This DFT contains
    -one OLEDB Source which is an Access dtabase.
    – two OLEDB Commands – to update SQL Database tables with the data in source MS Access database.

    Can I use Script component to update SQL table.

    If Yes, cold you please let me know the procedure?

    Thanks in advance!

    Best Regards,
    Swetha

  96. Nigel Harris says:

    Maybe I’m missing something here, but this doesn’t seem to me to be handling data sources with different column names. In the MyAddressInput_ProcessInputRow() subroutine in your example, Row.ID and Row.Count columns are read – what if, as your introduction said, you don’t know the details of the input columns until runtime?

    I have written some code for a script component to take different numbers of differently named columns and put them into a properly normalised format for insertion into SQL Server. That works fine for the example source table used in development. The problems start, however, when you try to change the source table to one with differently named columns. Is there any way to persuade SSIS to refresh its source table metadata at runtime?

    Thanks,
    Nigel.

  97. Hi Munish,
    I am a newbie to SSIS, just starting it off…I hv five flat files which needs to be inserted as it is to the Staging database….
    After this i need to insert these tables into ODS database with validations for date, amount, valid characters and all…log the error reports into another column.
    I need to schedule this job all at one go.
    I have been successful doing for individual tables but stuck coz i need 2 do this at one go.. your help will be highly appreciated….

    Thanks,
    Saumya Shetty

  98. hitesh says:

    Hi,

    I found this is good blog.

    Could you please email me the .dtsx/SSIS package of “Dynamic columns mapping – Script Component as Destination (SSIS)” to hiteshboytoy@gmail.com.

    Thanks,
    Hitesh

  99. Ruby says:

    Hi Munish,

    Can you please send me the sample package to my email id:. rubyagar@gmail.com

    Thanks,
    Ruby

  100. JW albers says:

    Hi Munish,

    Looks like a long lasting thread here, it seems you have done something many people are interested in!

    Can you mail me the sample package too? Thanks in advance!
    j.albers@altrecht.nl

    Cheers,
    JW

  101. Vikram says:

    Hi Munshi,

    Do we have any built in system functions for getting the name of the data flow in which the execution is currently going on?

    Thanks,
    Vikram

  102. srikanth Reddy says:

    Hi Munish,

    Can you please send me the sample package to my email id:. pininty.srikanth@gmail.com

    Thanks,
    Srikanth

  103. Ashwin says:

    I want to add new column in the excel file with the column name “file_[current month]” using SSIS script task. The column should be added only at the 1st of every month. How can I do it? Please help me.

    Thanks,
    Ashwin.

  104. Sri says:

    Can you email me the dtsx to kurraraghu@gmail.com

  105. krishna says:

    can you share with me the DTSX file

  106. Mahi says:

    Hi Munish ,

    I am try to upload a csv file to the sql server database and i am using flat file source for the source and OLEDB destination

    In the csv file i have 25 colums for example , But in my table i am having the user id as well as the created on . from the asp .net application i was able to execute my SSIS packeage . but i am struck with the extra columns ? How ca i proceed futhur please suggests if you any idea for this
    ———————————-
    Thanks and regards
    Mahendran chandramoha

  107. Marbs says:

    Hi Munish,
    Your idea looks interesting and i am doing the similar things. I have 30 views which has got different number of columns and fetching the data from SQL Server 2008. Now i need to export the data into csv in a generic fashion. So i have got a control table where i just put the view names and for each loop will enumerates to select those views and export into csv. I am not expert in script task in ssis. It would be great if you can share your experience regarding this and would be nice if you can forward the code to marbin@gmail.com.

    Your help is much appriciated.

  108. Miel says:

    Hi Munish,

    Interesting article; i’m very interested in the sample dtsx package mentioned (and requested often) in the comments.

    Any chance you could share that with me?

  109. SagarReddy says:

    Hey dude….this concept is interesting,, I tried to implement bt lack of logic failed. If u share the dtsx file, thn happy

  110. Kevin says:

    Hi Munish – thank you for your continued posts on this topic. I would very much like to review the sample .dtsx file. Will you email it to me? Thanks for your time.

  111. Julien says:

    Hi Munish,
    can you send me the sample package as well to my email. Thank you.

  112. Nayeem says:

    Hi Munish,
    As im new to this SSIS, can ya pls temme how can i copy a table structure of some X table in sqlserver to new table in sqlserver itself and this should be done dynamically (programmatically)..Pls help me out 😦

  113. Roy says:

    Hi Nayeem,
    Use below very simple & most widely used TSQL query:

    SELECT * INTO NewTargetTable
    FROM SourceTable
    WHERE 1=2

    -Roy

  114. kapil says:

    Can you pls send me the code as well at kap_gemini@yahoo.com

  115. Joe Dias says:

    Hi Munish,

    I need to load data from an excel file that has following sheet layout

    Col A ColB ColC Col D ColE ……………..Col AI
    Header : month type TTL 1 2 3 4…………. 31

    Data: 1 Category
    1 Prem Room Type
    1 Tactics
    1 Forecast CS OCC
    1 Forecast ADR OCC
    1 eComm tactics
    1 Catering Seasonal Special
    1 TBD
    1 TBD
    1 TBD
    2 Category
    2 Prem Room Type
    2 Tactics
    2 Forecast CS OCC
    2 Forecast ADR OCC
    2 eComm tactics
    2 Catering Seasonal Special
    2 TBD
    2 TBD
    2 TBD

    I need to import this data unpivoted by Col E through Col AI and insert int o tabel structure listed below

    Columns E through Columns AI are transalated as F3, F4, F5……
    [PSA].[HEI_PSAData](
    [PropertyCode] [nvarchar](3) NULL,
    [PropertyPID] [int] NULL,
    [PSA_TypeID] [int] NULL,
    [AsOfDate] [datetime] NULL,
    [PSA_Date] [datetime] NULL,
    [PSAValue] [nvarchar](255) NULL
    )

    each of the unpivoted col wil be a row inserted in the table structure. I am getting the first 4 columns as derived columns the issue I am running in to is that I need to construct the PSA_Date using the value in column month + extrapolating the digit from column headers F3 thru F35 and only pick the date vlaid for that month + 4 digits of the year that I extrapolate from the file name in derived columns task. So for date validation I need to make sure that I pick up dates valid for that month meaning if month = 1 then 1/31/2011 is valid but for month = 2 it is not.

    My current dtsx layout includes:
    1) Execute SQL task to create table structure if it does not exist
    2) Execute task to get the value for first two columns and the file path
    3) For each loop task to loop over the recordset that has the filepath value to import data from the specific task
    4) Db connection manager used for my OLEDB destination
    5) Excel file connection to an existing excel file with the ExcelFilepath set up to look at the variable that get populated from the recordset.

    My DFT has th following tasks

    ExcelFileSource task pointing to the Excel file connection manager

    Derived Column Transformation task

    Data Conversion task

    Script Component

    3 unpivot tasks

    Union ALL task to merge data sets

    OLEDB Destination to insert data in to the table

    The thing I am strugglig with is to how do I get the PSA_Date and add as a row to the data set that will be inserted in to the table

    This is what I have been trying to do:

    In my script component I create three different
    outputs columns: O31 and associate it with 31 input columns, O30 and asscociate with 30 days not include day 31 and O28 for month Feb

    I then connect each of them to the three unpivots and then join the three data set using UNION.

    So long story short:
    How do i get the PSADate as a new row
    How do I configure the output colums in the script component to look at specific days based on the month being processed.

    Any insight wil be greatly appreciated.

    I can be reached at jdias@heihotels.com

    Thanks in advance for all your help

    Joe Dias

  116. Raju says:

    Hi Munish,

    can you send the .dtsx package to my e-mail

    sqlbi.raju@gmail.com. your help will be highly appreciated. Thanking you.

  117. Rean says:

    Hi Munish,
    I created a SSIS Package that uses a dataflow component, the data flow component consists of a oledb data source that populates data in to the script component which is configured as a destination.Im using the sql server 2005 version, the problem doesnot start until i run the package on the server it gives me a list of warning such as “removing this unused output column can increase data flow task performance”.I do not have a output columns specified here.my code is as below..any help would be appreciated
    Public Class ScriptMain
    Inherits UserComponent
    Dim writeFile As FileStream
    Dim sw As TextWriter
    Public Overrides Sub PreExecute()
    MyBase.PreExecute()

    ‘ Add your code here for preprocessing or remove if not needed

    End Sub

    Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As PipelineBuffer)

    While (Buffer.NextRow())
    ‘code to write text to the flat files
    End While
    End Sub

    Public Overrides Sub PostExecute()
    MyBase.PostExecute()

    End Sub

    End Class

  118. kumarmva says:

    Hi Munish,

    Your Blog is really interesting with lots of concepts.
    I am planning to write a package which accepts a table name in a variable and populate the table. This is truncate and reload from Oracle to SQL. But I am finding it hard to map columns dynamically. If you have any example. can you please email the package?

    Thanks,
    Kumar

  119. Archana says:

    Hi Munish,

    I have a requirement to read a single date value from an excel file and store it to a variable in the package.

    I tried using the execute SQL task with a query of the format select [date] from [excel tab$]. This is however giving an error of some mismatch in data types. Tried different options of data types but not successful.

    Another option was to read it in a DFT. But how do I store it into a variable after this? Can a script task achieve this?

    Please suggest best approach.

    Archana

  120. vicky says:

    Hi Munish,
    It’s Great

    can you send the .dtsx package to my e-mail

    vicky0223tw@hotmail.com.tw . your help will be highly appreciated. Thanking you.

  121. Joug says:

    Hi Munish:

    I came across your blog. I think that you are an expert on SSIS. I think that you could help me with my issue.

    I have table: Name

    id name time term test
    12 Jean 1 1 0
    12 Jean 2 2 0
    12 Jean 3 3 0
    12 Jean 4 4 0
    12 Jean 5 5 0
    12 Jean 6 6 0
    12 Jean 7 7 0
    12 Jean 8 8 0
    12 Jean 9 8 0
    12 Jean 10 11 0
    12 Jean 11 11 0

    32 QADF 1 1 0
    32 QADF 2 2 0
    32 QADF 3 5 0
    32 QADF 4 5 0
    32 QADF 5 5 0
    32 QADF 6 6 0
    32 QADF 7 7 0
    32 QADF 8 8 0

    41 RTHF 1 1 0
    41 RTHF 2 2 0
    41 RTHF 3 5 0
    41 RTHF 4 5 0
    41 RTHF 5 5 0
    41 RTHF 6 6 0
    41 RTHF 7 7 0
    41 RTHF 8 8 0
    41 RTHF 9 8 0

    need to update column: time and term of table: Name where id = 12, 32, 41 ……212. I have several hundred ID
    base on following template excel data needed to be update time and test column.

    time term test id name
    1 1 0
    2 2 0
    3 3 0
    4 10 0
    5 11 0
    7 7 0
    8 8 0
    9 8 0
    10 11 0
    11 11 0
    12 11 0

    Is any way no need to make several hundred excel files for each id but still could update table only based on one template file above?

  122. Diederick Langenberg says:

    Hi Munish,

    We are trying to rewrite a working DTS package into a SSIS package.
    It opens text files which have a dynamic count of columns; the column names are fixed for each text file (filenames are fixed), but their position within the header row is not.Every text file has the column names on the first row.
    For example:
    file A:
    column1, column2, column3, column4
    file B:
    column2, column1, column3
    file C:
    column5, column4, column3, column2, column1
    etc..

    In a nutshell:
    The DTS packge reads the header row from a text file to determine how many columns there would be needed and add just as many transformations accordingly. The next step then uses dynamically added transformations to dump the data into a dummy table (which can fit all the data from all text files). After this is done, the final step is a stored procedure which stores all the data into the desired tables (relations and correct datatypes are put in place).

    How would I ‘transform’ the following script into a SSIS package ?
    I know there is no DTS.DataPumpTransformCopy anymore, so maybe I should be doing this in a completely different way.

    Function Main()
    Dim FSO
    Dim file
    Dim fStream
    Dim line
    Dim stringArray
    Dim colCount
    Dim task
    Dim transformation

    DTSGlobalVariables.Parent.Connections(“AuditBase Dump”).DataSource = DTSGlobalVariables(“gFileName”)

    SET FSO = CreateObject(“Scripting.FileSystemObject”)

    SET file = FSO.GetFile(DTSGlobalVariables(“gFileName”))
    SET fStream = file.OpenAsTextStream(1, 0) ‘ Open file for reading
    line = fStream.ReadLine
    fStream.Close

    Set task = DTSGlobalVariables.Parent.Tasks(“DTSTask_DTSDataPumpTask_1”).CustomTask
    While task.Transformations.Count > 0
    task.Transformations.Remove(1)
    Wend

    stringArray = Split(line, Chr(9))
    colCount = 0
    For Each str In stringArray
    colCount = colCount + 1
    If Trim(str) “” Then
    Set transformation = task.Transformations.New(“DTS.DataPumpTransformCopy”)
    transformation.Name = “DTSTransformation__” & colCount
    transformation.TransformFlags = 63

    transformation.SourceColumns.AddColumn “Col” & Right(“00” & colCount, 3), 1
    transformation.SourceColumns(1).DataType = 129
    transformation.SourceColumns(1).Flags = 32
    transformation.SourceColumns(1).Size = 255

    transformation.DestinationColumns.AddColumn “Col” & Right(“00” & colCount, 3), 1
    transformation.DestinationColumns(1).DataType = 129
    transformation.DestinationColumns(1).Flags = 104
    transformation.DestinationColumns(1).Size = 8000

    task.Transformations.Add(transformation)

    Set transformation = Nothing
    End If
    Next
    Set task = Nothing

    If colCount > 80 Then
    MsgBox “Meer dan 80 kolommen in DumpBestand niet toegestaan”
    Main = DTSTaskExecResult_Failure
    Else
    SET fStream = file.OpenAsTextStream(8, 0) ‘ Open file for appending
    fStream.Write Chr(10)
    fStream.Close

    Main = DTSTaskExecResult_Success
    End If

    SET fStream = Nothing
    SET file = Nothing
    SET FSO = Nothing

    End Function

  123. Meiwen says:

    I found your blog today when searching for some SSIS — to create some kind of Dinamic DataFLow, that reads from source files which have different columns to a table.

    Your artical is very clear and concise
    Is it possible that you send me a the .DTSX file to my email ?

    Thanks!

  124. Corey says:

    Very informative post, thank you. Please, would greatly appreciate dtsx file.

  125. vijay says:

    hi can you send me the package would like to have a better understanding .
    thanks in advance

  126. amitsuthar says:

    Hi Munish,
    I have such complicated thing like i am going to update 7-8 child tables with one record input and with so many conditions like checking master record in master table and if not then add new master record and then insert row in according child reference table..
    is it possible through Script Component

  127. Enrico says:

    Hello Munish,
    I have a business issue:
    upload data frmo an excel file into a slq server table. My excel files has data in a pivot “format”, let’s say for instance:

    date | ArtCodeX | ArtCodeY | ArtCodeK | ArtCode…
    01-01-2010 8 7 6,4 5
    02-01-2010 9 7,87 5,4 3

    I wanted to normalize it with unpivot task in an SSIS package:
    date | ArtCode | Value
    01-01-2010 ArtCodeX 8
    01-01-2010 ArtCodeY 7
    01-01-2010 ArtCodeK 6,4
    01-01-2010 ArtCode… 5

    My problem is that columns names are dynamic and moreover i could have different number of columns depending on the excel file i’m uploading.

    By using your script, is there a way to dynamically map columns in the unpivot transformation task?
    can you send me a sample of the script.

    My compliments and Best Regards,
    Enrico Milani

  128. Jack says:

    Very interesting, Munish. Can you please send me the SSIS package? Thanks.

  129. girishek says:

    Hi,

    I am using excel,flat files,access files as a source and My sql server 2008 as destination.so could u plz send the dtsx and script to import data from any excel files or flat files or access files without specifying excel name or path.it must dynamically import any files to the database.

    email id : mgirishek@yahoo.co.in

    Thanks & Regards,
    Girishek

  130. shivika says:

    Hi Munish,

    My requirement is to read from a excel file which is dynamic – the columns can change/increase and i need to handle it at run time – by inserting in a run time created table – Select * into tempTable from @excelData
    Can i handle this using your script task?
    Please send me the dtsx file for the same.

    Thanks!

  131. Ahmet says:

    Munish,

    Very nice article. Can you please send me the SSIS package?
    Thanks.

  132. Nuve Nai says:

    Hi Munish,

    Can you please send me the SSIS Package for this as well?
    thank you

  133. laban says:

    Good article.Could you send me your sample package at this email mwangithuha@gmail.com

  134. Arun Kumar says:

    Hi Munish,

    Could you please send me the SSIS package for me too.

    TIA.

    Regards,
    Arun

  135. Arun Kumar says:

    Hi Munish,

    Could you please send me the SSIS package for me too. (arun.available.here@gmail.com)

    TIA.

    Regards,
    Arun

  136. Jason says:

    Hello! this looks very promising. I really appreciate the time you’ve put in. I too have a similar requirement. I am pulling data from a cube using a MDX script built using an expression. The resulting fields will go into an excel file. The problem I have is this.

    some of the field names can vary depending on which years are of interest. since the years can vary, and thus the field names, the excel connection bombs. I need to be able to dynamically build these fields and load them into the excel file.

    Would you mind sending me the dtsx?

  137. keerti says:

    can u send some sample package(if u have )in
    vermakeerti@yahoo.co.in

  138. Emil says:

    hi Munish..your package seems to be the answer to my long lost question. Kindly send it to egarcia1106@hotmail.com Thanks.

  139. greg stark says:

    Kindly send copy of project to gstark@electrorent.com Thank you very much.

  140. pavan teja chirumamilla says:

    Hi munish
    I am working on dynamic mapping currently in my project so your package would help me lot so can you please send me your package asap. Thank you very much

  141. kmn says:

    Kindly send copy of project to knuss1@cox.net Thank you very much.

  142. eka says:

    Hi Munish,

    Could you please send me the dtsx file to fromthecreatorofSapiterbang@gmail.com ? Thank you very much

  143. Vaibhav says:

    Hi Munish,
    Thanks for sharing valuable information.I was searching on this for quite a long time.
    Could you please send me the dtsx file or project solution related to this on my mail id dreamvaibhav@rediffmail.com
    Thanks a lot..

  144. bshanmukeshwar says:

    Thanks For your post and it is helpfull for me.Hi Can you send me dtsx file to bshanmukeshwar@live.com

  145. Jamal says:

    Hi Munish,
    Thanks for sharing.
    Could you please send me the dtsx or project at: jamal.usaf@gmail.com

    Regards
    Jamal

  146. Karthik says:

    Hi Munish,

    Please share the package to my mailID: sai.karthik41@gmail.com

  147. Jennifer Song says:

    Hi Munish,

    I just came across your blog, excellent work!

    I am trying to do dynamic column mapping in ssis 2008. I greatly appreciate it if you can send me the dtsx to : jennifer.q.song@gmail.com

    Thanks,
    Jennifer

  148. Vasanth says:

    Hi Munish..
    i have number of flat files. and i have to load the data from these flat files to corresponding tables..

    in database i have created a table with two columns where i saved my flat files location in one column and corresponding table name in another column..

    i created the expression to get the file location dynamically.. i had done it using foreach loop container with data flow task..

    but i finding some difficult in mapping columns in oledb destination tool.. its showing error called *NEEDSNEWMETADATA*.

    how to map the columns dynamically.. And this dynamical process should load the data from different flat files into corresponding tables…

    is there any third party tool available for this?or can we do this through any script..?

    thanks in advance

  149. Jose says:

    Hi Munish,
    Could you please send me the dtsx to my mailID: jose.aprodriguez@gmail.com

  150. rao says:

    Looks I found what I want can you please forward the sample to my email ? rao.jbom@gmail.com

  151. It’s nearly impossible to find educated people in this particular topic, however, you seem like you know what you’re talking about!
    Thanks

  152. GB says:

    Hi Munish,

    Excellent job.

    Could you please send me a sample code and dtsx to my mail?

    Regards,
    GB

  153. Hi Manish,

    This is Excellent.. and will solve my problem..

    Could you please send me a sample code and dtsx to my mail?

    Regards,
    Mahendra Jain

  154. Dave Gangler says:

    Hello Munish,

    I came across your blog today. I need to export data from SQL Server to Excel or CSV using SSIS and I won’t know what the column names are of the source data. I think your example SSIS package about dynamic columns mapping could really help me out. Could you email it to me?

    Thanks,
    Dave Gangler
    dave@gangler.org

  155. Nirmal Kumar says:

    Hi Munish,
    The concept is very clear. I need a sample code of this so that I can customize to my client requirement. My Id is nikkijyos@yahoo.com.

    Regards,
    Nirmal

  156. Ashish says:

    Hi Munish,

    I too have the similar requirement to load multiple flat files into multiple destination tables with different columns. I hope your solution can help. Could you please send the package file to me. Thanks a lot in advance.

    Regards,
    Ashish

  157. Harin says:

    Hello..

    Can u pleas send package to Harin8313@gmail.com

  158. venki says:

    Hello Munish,

    I found your blog today when searching for some Dynamic data loading. I appreciate it a lot. It is clear and concise.

    Is it possible that you send me a sample DTSX file?

    Thanks
    Venki
    grs.venki@gmail.com

  159. Lina Li says:

    I really like your blog, its just what I need this week. Can you send me the sample package too.

    Very appreciated!

  160. Ramesh says:

    Hi Munish,

    My case is little bit interesting, if possible can you please help me?

    In my case i want to execute the random queries which has different columns and column count may also varies here. So each time i execute the query i will write that information into a flat file.

    But in SSIS we need define the layout for flat file while desiging only it is creating a problem for me can you help on this?

    Thanks,
    Ramesh.

  161. John Brock says:

    Hello Munish,

    I came across your blog today. I need to export data from SQL Server to Excel or CSV using SSIS like Dave Gangler said above, also. And I also need the source data. I think your example SSIS package about dynamic columns mapping could really help me out. Could you email it to me?
    jbrock@residentialcredit.com Cc: brockmail@verizon.net Please, Please!
    Thank you so much.

  162. Megan Vee says:

    Hi Munish –
    I’m not sure if this is still active. If it is, may I also have a copy of the dtsx file?
    I have a dynamic set of columns (e.g. could be 20 columns today and 23 tomorrow) coming in that then need to be exported out to a flat file.
    Will / Could this work for me?
    Thank you,
    Megan

  163. renegrin says:

    Hi Munish,
    Can you send an example SSIS package about dynamic column mapping as well.

    Thanks in advance..

  164. Valeriy says:

    May I also have a copy of the dtsx file to? ))
    TValerka@gmail.com

    THX you very much!!

  165. Siva says:

    Hi Munish
    This is exactly i am looking for Thank you so much for clear explanation and could you please mail me the example dtsx package for Dynamic columns mapping – Script Component as Destination (SSIS).

    mail id: ssg315@gmail.com

    Thank you so much for your help .

  166. Siva says:

    HI Munish,

    Could you please send me the example SSIS package about the dynamic column mapping as well i am trying for this since long time but not getting it .

    mail id : ssg315@gmail.com

    Thank you so much in advance

  167. Sujitha says:

    Hello Munish,

    Could you please send an example SSIS package about dynamic column mapping for a .csv file.

    Regards,
    Suji

  168. swetha says:

    Hi munish
    I am working on dynamic mapping currently in my project so your package would help me lot so can you please send me your package to todupusw@gmail.com

    Thank you very much

  169. shreeya says:

    Hello Munish,

    Could you please send an example SSIS package about dynamic column mapping for a .csv file.

  170. shreeya says:

    I really like your blog,Can you send me the sample package too.

    Very appreciated!

  171. Karunya says:

    Can you pl. send sample .dtsx file for dynamic mapping of multiple excel files. karunya.padala@infotech-enterprises.com

  172. Sathish says:

    can you please share me the dtsx to m.satishbabu@gmail.com

  173. avi cohen says:

    Hi Munish,
    I really like your blog
    Could you please send an example SSIS package about dynamic column mapping

    avicsma@gmail.com

    thanks alot

  174. Gangadhar says:

    Hi Munish, Very good article.
    Hats Off to your deep knowledge on SSIS / Scripting.

    Can you send me the dtsx package – ganga411@gmail.com

    Thanks
    Ganga

  175. Prav says:

    Hi munish,
    Very nice article .
    I am tyring to load 100 tables dynamically from one server db to another server db by using only SSIS with only one data flow task. Could you please help me on this. for any better solution.

  176. Hossein says:

    Thanks Munish for the useful post.
    But there is sth in your article I cant understand. At the beginning of the post you are saying it is meant to address dynamic source and destination scenarios. But looking at your code, the input to the Script is already determined and static. I am having the problem with running a number of queries that can be generated dynamically against Access db and putting the result into csvs and I am keen to know is there any way that I can dynamically do that.Say using a loop and feeding the query to the DFT in each iteration.

  177. TJ says:

    thanks munish for the useful post
    can i get some aspx file for test ?
    Can you send me the dtsx package to hopeguru@gmail.com
    thanks.
    TJ

  178. tai wa says:

    Thanks Munish for the post.
    Could u send me the dtsx package? wutaiwa@gmail.com
    thanks
    TW

  179. satish says:

    Hi Munish,

    I got this similar requirement to be implemented in my project.
    If it possible can you please send me the dtsx package to my email id,

    satish.nadakuduru@gmail.com

    Thanks in advance.

  180. srinu says:

    Thanks Munish for the post.
    Could u send me the dtsx package? asrinu13@gmail.com

  181. Supriyo Bose says:

    Hi Manish I went thru your blog; Any idea on column mapping dynamically in between OLE DB Source editor and Flat File destination Task ?
    My requirement is in my database there are many tables, each table has different column name and data type; which I am exporting to Flat file destination using for each looping . Each table has different structure . While looping it is going next table in my database but mapping of new table structure is not being mapped in text file

  182. Vipin jha says:

    Hi All,
    I am working on an existing SSIS package.
    I am facing serious issues in dynamically loading Source and destination table
    They are having 100 of ssis package , seprate package for each table,
    that means I have 100 source table and 100 destination table.
    Now I have to create single SSIS Package which load all the 100 source and destination table.
    please suggest some good live example .

    Thanks & regards,
    Vipin jha

  183. Pooja says:

    hi
    I have a source file in excel format. Which I need to store in the database but dynamically
    Could u send me the dtsx package?

  184. Ali says:

    Thanks a lot man you are doing a great job . Would be of grat help if you can send me sample dtx file to refer.

    Email Id – ali.shahbaz9999@gmail.com

    Ali

  185. vishal says:

    Hi Munish,

    I have one requirement, In foreach loop container iam calling the execute sql task and calling the stored procedure and the data is loaded into the table – now i want to dynamically load the table data into text file dynamically .

    So for example i have 10 tables having data need to dump the 10 table data into 10 different text files dynamically. Is there any approach to do this. if you have a sample package.

    Could you please send me the dtsc packge to my email id: sarvade1.vishalkumar@gmail.com

    Thanks & Regards,
    Vishal

  186. surya says:

    Hi,

    I seen you blog. It’s very useful for me. I am new for ssis and I want to know some information please help me .
    Is it possible to load data from multiple excel files to sql server tables. The excel files have different schemas(different columns).

    Please is it possible to load the data from different schema excel files.

    Regards,
    Surya

  187. Manu says:

    Hi Manish,

    I need your help. In Data Stage, we have Run Time Column Feature where mapping gets generated at run time looking at the source & Target table. While looking online for similar feature in SSIS, I came across your blog which seems great. I have one to one table extraction & Load, No transformation requirement. Source is SQL, Oracle & SAP. Request you to please share the good for me to understand it better and implement in my current project@ neeraj1_mahajan@yahoo.com

  188. sankar says:

    HI,
    I have one requirement like , I have to copy the data from source server tables to destination server tables which are having same columns for all the tables. Server A is having X,Y,Z tables and Server B is Having same X,Y,Z tables… the data is not direct copy means to retrieve the A table’s data we have to do the some join operations with other tables like B,C..etc and the same for B,C tables data also. So i wrote one Stored procedure to pull the details from these tables (A,B,C) so the sp outcome is having 3 result sets i.e A,B,C. So to do this I took Script Component and calling this stored procedure and also i created output columns for all these tables and in the script i wrote the code to bind the sp data to these output buffers. So finally my script component is returning A,B,C tables result sets , now how do i bind these result sets to destination server A,B,C respective tables. Please help mee sirrrrrrrrrrrrrrrrrrrr

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s