none
How to use script task to create a top row string (column headers) in a delimited text file output based on the columns exported?

    Question

  • How can I create customized column headers (different from the one in the database) in a text file using a script task based on the columns exported?  

    For example, if I used the following SQL to select the columns

    select id, name, address

    And the column headers in the output should be

    ID, Full_Name, Street_Address

    I know you will need to write a conditional code to loop through the columns like

    for each selected column

    if column = "id" then "ID"

    if column = "name" then "Full_Name" and so on

    But how can I start (what task) and what will the correct code be? (Prefer VB, but C# is OK too.)


    BI Analyst

    Saturday, January 05, 2013 12:02 AM

Answers

  • Hello BIAnalyst,

    You can use the script below to write the header to file. You wont have to delete the file as it will get overwitten by the script if it already exists or its created if it does not exists. Ensure that the script is before the Foreach Loop

            Dim S As String
            S = Dts.Variables("FileName").Value
            Dim StrWrt As New StreamWriter(S)

            StrWrt.WriteLine("ID, Full_Name, Street_Address")

            StrWrt.Flush()
            StrWrt.Close()

    Also remeber to add Imports System.IO reference

    • Marked as answer by BIAnalyst Wednesday, January 09, 2013 8:30 PM
    Monday, January 07, 2013 12:58 PM

All replies

  • To create customize column headers we can use column alias e.g;

    select id as ID, name as Full_Name, addess as Street_Address
    from YourTable

    I would go into data flow tab and use above command in the oledb source and send the output to the flat file.

    Now sure why you are using script task. I hope this helps.

    Saturday, January 05, 2013 5:00 PM
  • That should work for the case that I addressed, but I would like to know how to do it any way because I need it for other different projects.  Any tips will be appreciated.

     


    BI Analyst

    Sunday, January 06, 2013 3:48 AM
  • Hello BIAnalyst,

    You can use the script below to write the header to file. You wont have to delete the file as it will get overwitten by the script if it already exists or its created if it does not exists. Ensure that the script is before the Foreach Loop

            Dim S As String
            S = Dts.Variables("FileName").Value
            Dim StrWrt As New StreamWriter(S)

            StrWrt.WriteLine("ID, Full_Name, Street_Address")

            StrWrt.Flush()
            StrWrt.Close()

    Also remeber to add Imports System.IO reference

    • Marked as answer by BIAnalyst Wednesday, January 09, 2013 8:30 PM
    Monday, January 07, 2013 12:58 PM
  • Is script task only option ? when you are creating flatfiledestination, simply click on flatfiledestination connection manager, go to advanced and change the names of the columns there. save it, remap with new columns and u should be fine

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

    Monday, January 07, 2013 1:19 PM
  • @ Mayorlag:

    What component type do I use when setting up the script component?  (I just tested using the "Source" type, is that correct?)

    Where do I place the code?  In the "Public Overrides Sub CreateNewOutputRows()" sub?

    Do I connect the script to the flat file destination?  

    In the code, the Dts is underlined and the message says that it is not declared.  What should I do?

    Do I need to set up the "FileName" variable outside of the script component?  What does it do?

    @Dia: I just like to learn how to do this using script.  :)

    Thank all for your help!



    BI Analyst

    Monday, January 07, 2013 3:38 PM
  • No Pro, I think it would be tranformation not source.. I guess

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

    Monday, January 07, 2013 4:06 PM
  • Also I guess, it will be a vraiable FileName, Then in script task it would be in ReadWrirte variable

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

    Monday, January 07, 2013 4:09 PM
  • Hello BIAnalyst,

    Use the script Task within the Control Flow. Place the

            Dim S As String
            S = Dts.Variables("FileName").Value
            Dim StrWrt As New StreamWriter(S)

            StrWrt.WriteLine("ID, Full_Name, Street_Address")

            StrWrt.Flush()
            StrWrt.Close()

    Within Main() and the imports at the top (You will see other references).

    Remeber to create a variable called FileName or any other name you like but make sure it is available to the script

    Monday, January 07, 2013 5:23 PM
  • Hi Mayorlag,

    Thanks again for your info.  

    When I run the code, I got the following error:

    Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.
     ---> System.Runtime.InteropServices.COMException (0xC0010009): The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

    I do not know what else is missing.  :(

    Maybe I did not "make it available to the script"?  How can I do so?


    BI Analyst 


    • Edited by BIAnalyst Monday, January 07, 2013 6:17 PM
    Monday, January 07, 2013 6:07 PM
  • correct me if i am wrong?

    your source is a text file, and the destination is a SQL table ,and the main question is that the TEXT column header names change? right?, but  the number of columns in the text are the same?

    please correct me if i am wrong?


    Sincerely Nik -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    Monday, January 07, 2013 6:09 PM
  • Hi Nik,

    That was not the major issue any more.  At this time, I just want to use a script task to write string on a text file, that's all.  No need to concern about the column headers or the SQL table stuff.  Sorry for the confusion.



    BI Analyst

    Monday, January 07, 2013 6:48 PM
  • Hello BIAnalyst,

    Create the variable and then when you click on the script task, add the variable as shown in the image below. Make sure you are using the exact case as the variable name.

    Tuesday, January 08, 2013 12:25 PM
  • Hi Mayorlag,

    I finally got it running!  Thank you!  

    Two spin-off questions:

    1. How can I assign value to another variable's Value property in the code?

    2. How can I have more than one variable in the ReadOnlyVariables box?


    BI Analyst

    Tuesday, January 08, 2013 1:50 PM
  • Hello BIAnalyst,

    See answers below

    1. How can I assign value to another variable's Value property in the code?

    - VB.net:-  Dts.Variable("<<Namf of your variable>>").value = <<The value you want to assign>>

    2. How can I have more than one variable in the ReadOnlyVariables box?

    - The same way you selected that variable you were able to use. Just create the variable in the package and then select it as shown in the image I posted above

    Tuesday, January 08, 2013 5:01 PM
  • Hi Mayorlag,

    I overlooked my variable in the Variable Editor because I did not scroll down the window to look for it.  Now I see it.  :)

    I created a new variable, FileName4, and tried to assign "c:\text.txt" to it, but I do not see the text in the value property after the execution.  (Package scope and string type.)

    This is what I have in the code:

    Dts.Variables("FileName4").Value = "c:\text.txt"

    I used the ReadWriteVariables option.

    Why it is not working?  


    BI Analyst

    Tuesday, January 08, 2013 8:23 PM
  • Hello BIAnalyst,

    You wont see the value assigned to the variable but the value will be available to the package during execution. To see that the value is assigned properly, see the image below. In the Variable Assigment script I have Dts.Variables("FileName4").Value = "c:\text.txt" and in the Variable Display script i have Msgbox(Dts.Variables("FileName4").Value)

    With this, you will be able to see that the value was assigned to the variable

    Wednesday, January 09, 2013 9:13 AM
  • Hi Mayorlag,

    Thank you for your info!  You are right, its value doesn't show on the Value property, but it does get passed.  

    One last question: Do I always need save the script and close all windows (so that I can go back to the Control Flow window) in order to run the script?  

    By the way, I found that the following code will also work to write string to a text file:

            Dim FILE_NAME As String = "C:\text2.txt"
            Dim objWriter As New System.IO.StreamWriter(FILE_NAME)

            objWriter.WriteLine("Hello")


    BI Analyst


    • Edited by BIAnalyst Wednesday, January 09, 2013 1:35 PM add new code
    Wednesday, January 09, 2013 1:32 PM
  • Hello BIAnalyst,

    You dont always have to save the code. You can just make changes and close the code window. Just make sure you click on Ok on the Script Editor window.

    The code you posted will do the same work. They are the same but just put together differently. You can use StreamWriter(FILE_NAME) if you add Imports System.IO as reference

    Wednesday, January 09, 2013 4:36 PM
  • Thanks again, Mayorlag!  See you again in the forum.



    BI Analyst

    Wednesday, January 09, 2013 8:29 PM