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

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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 .

Answer 5

@ 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

Answer 6

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 .

Answer 7

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 .

Answer 8

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

Answer 9

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 

Answer 10

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).

Answer 11

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

Answer 12

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.

Answer 13

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

Answer 14

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

Answer 15

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

Answer 16

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

Answer 17

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

Answer 18

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

Answer 19

Thanks again, Mayorlag!  See you again in the forum.

---

BI Analyst