none
creating a tab delimited file using script task in ssis

    Question

  • I created some comma delimited text files from some tables in a database using script task but I want the files to be tab delimited .

    In the package I created a variable called FileDelimiter.

    and implemented it in this line of code below

    string FileFullPath = DestinationFolder + "\\" + SchemaName + "_" + TableName + "_" + datetime + FileExtension;
    
                        //Get the data for a table into data table 
                        string data_query = "Select * From [" + SchemaName + "].[" + TableName + "]";
                        SqlCommand data_cmd = new SqlCommand(data_query, myADONETConnection);
                        DataTable d_table = new DataTable();
                        d_table.Load(data_cmd.ExecuteReader());
    
                        StreamWriter sw = null;
                        sw = new StreamWriter(FileFullPath, false);
    
                        // Write the Header Row to File
                        int ColumnCount = d_table.Columns.Count;
                        for (int ic = 0; ic < ColumnCount; ic++)
                        {
                            sw.Write(d_table.Columns[ic]);
                            if (ic < ColumnCount - 1)
                            {
                                sw.Write(FileDelimiter);
                            }
                        }
                        sw.Write(sw.NewLine);
    
                        // Write All Rows to the File
                        foreach (DataRow dr in d_table.Rows)
                        {
                            for (int ir = 0; ir < ColumnCount; ir++)
                            {
                                if (!Convert.IsDBNull(dr[ir]))
                                {
                                    sw.Write(dr[ir].ToString());
                                }
                                if (ir < ColumnCount - 1)
                                {
                                    sw.Write(FileDelimiter);
                                }
                            }
                            sw.Write(sw.NewLine);
    
                        }
    
                        sw.Close();

    So what happens is ;

    What ever I substitute in the package output variable that will be the row delimiter .

    I tried using \t .But it used that as the common seperator

    e.g

    Man, Woman, Son for "," delimiter

    Man \t woman\tSon\t for "\t" delimiter 

    Thursday, April 20, 2017 5:53 PM

Answers

  • If I understood correctly, you are passing "\t" to the SSIS package variable and then trying to use that value from within the C# script task. This doesn't work as you expect, because the translation of the escape "\t" into a TAB character is done by the C# compiler (not at runtime!). But since the \t was entered within SSIS, the C# compiler never saw it, and it doesn't mean anything special at runtime, so it gets sent "as is" to the output.

    To achieve the desired effect, I suggest the "kludge" of translating the value at runtime by a Replace function call at the begining of your script:

    FileDelimiter = FileDelimiter.Replace("\\t", "\t");

    Thursday, April 20, 2017 9:11 PM
    Moderator
  • Since the SchemaName is used later for accessing the table, you can´t simply replace it with something else. Instead, copy it to another variable and make the replacement there:

    string prefixForFile = null;
    // Load a value in prefixForFile according to your criteria
    // For example, the following switch selects a value depending on a fixed set of values for SchemaName
    switch (SchemaName)
    {
        case "DRE":
            prefixForFile = "CHAR";
            break;
        case "GHI":
            prefixForFile = "JKL";
            break;
        // etc.
    }
    
    string FileFullPath = DestinationFolder + "\\" + prefixForFile + "_" + TableName + "_" + datetime + FileExtension;
    

    • Marked as answer by DreSql Thursday, May 25, 2017 9:11 PM
    Thursday, May 25, 2017 6:49 PM
    Moderator
  • only the first part of the file is been replaced

    But what is your criteria to decide which is the replacement?

    As stated in the comments in my code, the switch was only one example showing how the replacement could depend on the original value. Of course, if you have a different criterion to decide what is the replacement, you would write it instead of the switch.

    Note that this code ONLY replaces the first part of the file, which is what you wanted.

    • Marked as answer by DreSql Thursday, May 25, 2017 9:12 PM
    Thursday, May 25, 2017 8:52 PM
    Moderator
  • If I understood your example, the only change that you need is to change into lower case the name of the table. This can be accomplished by means of a call to the function ToLower():

    string FileFullPath = DestinationFolder + "\\" + prefixForFile + "_" + TableName.ToLower() + "_" + datetime + FileExtension;

    • Marked as answer by DreSql Friday, May 26, 2017 8:49 PM
    Friday, May 26, 2017 6:24 AM
    Moderator
  •  I rather prever the name coming out in thr previous month.

    If you are getting the month like this:

    string datetime = DateTime.Now.ToString("yyyyMM");

    Then the easiest way to get the previous month is to use date arithmetic on the raw date before converting it to string:

    string datetime = DateTime.Now.AddMonths(-1).ToString("yyyyMM");

    • Marked as answer by DreSql Monday, June 12, 2017 6:48 PM
    Saturday, June 3, 2017 6:17 AM
    Moderator

All replies

  • If I understood correctly, you are passing "\t" to the SSIS package variable and then trying to use that value from within the C# script task. This doesn't work as you expect, because the translation of the escape "\t" into a TAB character is done by the C# compiler (not at runtime!). But since the \t was entered within SSIS, the C# compiler never saw it, and it doesn't mean anything special at runtime, so it gets sent "as is" to the output.

    To achieve the desired effect, I suggest the "kludge" of translating the value at runtime by a Replace function call at the begining of your script:

    FileDelimiter = FileDelimiter.Replace("\\t", "\t");

    Thursday, April 20, 2017 9:11 PM
    Moderator
  • Thanks Alberto,

    It works

    Thursday, April 20, 2017 10:00 PM
  • Hi Alberto,

    I have a new challenge.

    refrencing the below portion from the code above

     string FileFullPath = SubFolder + "\\" + SchemaName + "_" + TableName + "_" + datetime + "_v1" + FileExtension;

    I want to rename the schema portion.

    When it creates the file.

    How do I go about that in script task.

    Presently its writing the name out from the schema, & table e.g

    DRE_LOB_201705_v1.txt.

    I want to rename all the schema portion of the files to a diffrent name

    CHAR_LOB_201705_v1.txt

    Thursday, May 25, 2017 6:18 PM
  • Since the SchemaName is used later for accessing the table, you can´t simply replace it with something else. Instead, copy it to another variable and make the replacement there:

    string prefixForFile = null;
    // Load a value in prefixForFile according to your criteria
    // For example, the following switch selects a value depending on a fixed set of values for SchemaName
    switch (SchemaName)
    {
        case "DRE":
            prefixForFile = "CHAR";
            break;
        case "GHI":
            prefixForFile = "JKL";
            break;
        // etc.
    }
    
    string FileFullPath = DestinationFolder + "\\" + prefixForFile + "_" + TableName + "_" + datetime + FileExtension;
    

    • Marked as answer by DreSql Thursday, May 25, 2017 9:11 PM
    Thursday, May 25, 2017 6:49 PM
    Moderator
  • Thanks for the response.

    But there is no case in my scenerio only the first part of the file is been replaced

    DRE_member_201705_v1.txt

    DRE_LOB_201705_v1.txt.

    DRE_claims_201705_v1.txt

    Thursday, May 25, 2017 8:28 PM
  • only the first part of the file is been replaced

    But what is your criteria to decide which is the replacement?

    As stated in the comments in my code, the switch was only one example showing how the replacement could depend on the original value. Of course, if you have a different criterion to decide what is the replacement, you would write it instead of the switch.

    Note that this code ONLY replaces the first part of the file, which is what you wanted.

    • Marked as answer by DreSql Thursday, May 25, 2017 9:12 PM
    Thursday, May 25, 2017 8:52 PM
    Moderator
  • Thanks alot Alberto.
    Thursday, May 25, 2017 9:12 PM
  • One more question

    Presently it comes out the format below

    CHAR__MEMBER_201705_v1.txt

    CHAR_LOB_201705_v1.txt.

    CHAR_CLAIMS_201705_v1.txt

    but, I desire the files to be in the format below.

    CHAR__member_201705_v1.txt

    CHAR_lob_201705_v1.txt.

    CHAR_claims_201705_v1.txt

    Thursday, May 25, 2017 9:21 PM
  • If I understood your example, the only change that you need is to change into lower case the name of the table. This can be accomplished by means of a call to the function ToLower():

    string FileFullPath = DestinationFolder + "\\" + prefixForFile + "_" + TableName.ToLower() + "_" + datetime + FileExtension;

    • Marked as answer by DreSql Friday, May 26, 2017 8:49 PM
    Friday, May 26, 2017 6:24 AM
    Moderator
  • It worked.

    You have been very helpful.

    Friday, May 26, 2017 8:50 PM
  • Hi Alberto,

    Is it possible to get the row count of each files in the script and log both name and rowcount  in a different file like a 'control file' from insider the same script? 

    i.e my script will produce the file name I generated along side the row count.

    Header: FileName                                     | RowCounnt

    body  

                 CHAR__member_201705_v1.txt    | 200kb

                  CHAR_lob_201705_v1.txt.               |  2kb

     

    Tuesday, May 30, 2017 6:04 PM
  • Hi Alberto,

    Peresently I have the file been produced by the script coming out in the current month's as part of the file name but I rather prever the name coming out in thr previous month.

    for example we are in June the file should be in

    CHAR_lob_201705_v1.txt.

    CHAR_claims_201705_v1.txt

    and not 

    CHAR_lob_201705_v6.txt.

    CHAR_claims_201706_v1.txt

    Friday, June 2, 2017 8:49 PM
  • I meant 

    CHAR_lob_201705_v1.txt.

    CHAR_claims_201705_v1.txt

    and not 

    CHAR_lob_201706_v1.txt.

    CHAR_claims_201706_v1.txt

    Friday, June 2, 2017 8:51 PM
  • Is it possible to get the row count of each files

    To get the row count you can open the file, read all lines, and tally the total. The shortest way I know to write it is this:

    int numRows = SYstem.IO.File.ReadAllLines(fileFullPath).Length;

    Once you have the number, you can of course concatenate it to the filename and a separator and add the resulting text to your "control file".

    Saturday, June 3, 2017 6:14 AM
    Moderator
  •  I rather prever the name coming out in thr previous month.

    If you are getting the month like this:

    string datetime = DateTime.Now.ToString("yyyyMM");

    Then the easiest way to get the previous month is to use date arithmetic on the raw date before converting it to string:

    string datetime = DateTime.Now.AddMonths(-1).ToString("yyyyMM");

    • Marked as answer by DreSql Monday, June 12, 2017 6:48 PM
    Saturday, June 3, 2017 6:17 AM
    Moderator