How to Create flat file dynamically from SQL Server Table or View in SSIS Package - SSIS Tutorial

Scenario: Download Script

We have a requirement to create an SSIS Package that should create an output file dynamically from a Table or View.
The columns of View or Table can change anytime but we don't want to open package and remap column/columns. In short our SSIS Package should not care about the number of columns returned by Table or view.

Solution:

As the definition of Table or View can change anytime, It will be good idea to use Scirpt Task and handle the situation. If we use builtin source and destination Transformation, they will fail in case name or number of columns will change in source table or view.


Let's processed with step by step approach

Step 1:
Create the following variables as shown below, the values for the variables can be configured by using SSIS configuration,
This will give us freedom to change the name of file, directory path, delimiter ("," or "" etc.) and file extension (txt or csv etc.) without making modification to SSIS Package.

VarDelimiter :  Provide the delimiter that you want for your flat file
VarDirectoryPath :  Provide the directory path where you would like to create flat file dynamically
VarFileExtension : Provide the extension of file you like to create
VarFileName : Provide the name of file that you would like to create dynamically from SQL Server Table
VarLogFolder: Provide the path for Log folder in case error occur in Script Task
VarObject :  Create Object type variable to save records from SQL Table or View.



Create variables in SSIS Package to create flat file dynamically from SQL Server Table or View


Step 2:
Create OLE DB Connection manager by going to Connection Manager pane. You have to provide SQL Server Instance Name and then choose the database. After creating, I have renamed to DB_Conn_TechBrothersIT as shown below.

Create OLE DB Connection manager in SSIS Package to generate flat file dynamically


Step 3:

After creating variable, Drag Execute SQL Task in control flow pane and configure as show below. Please notice that we are using Select * here. that means get all the columns data from table or view.



Get data from SQL Server Table or View and save to Object type variable in SSIS Package to create flat file dynamically


Map the full result set to Object type variable in Execute SQL Task in SSIS Package as shown below.


Save the query results in Object type variable by using Execute SQL Task in SSIS Package


Step 4:

Bring Script Task to Control Flow Pane and then open by double clicking. We will be using C# as scripting language in our demo, you can use visual basic if you like.
Go to ReadOnlyVariable and then add the user variables those we have created above in Step 1.



Add variables to Script Task in SSIS Package to create flat file dynamically in SSIS Package from SQL Server Table or View


Step 5:

Click on Edit Script button, paste following code and save the the changes and Finally OK button.


Under #region Namespaces, I have added below code

using System.IO;
using System.Data.OleDb;


Under public void Main() { 
I have added below code.
            string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
            try {

                //Declare Variables
           string FileDelimiter = Dts.Variables["User::VarDelimiter"].Value.ToString();
           string DirectoryPath = Dts.Variables["User::VarDirectoryPath"].Value.ToString();
           string FileName = Dts.Variables["User::VarFileName"].Value.ToString();
           string FileExtension=Dts.Variables["User::VarFileExtension"].Value.ToString();
                

                OleDbDataAdapter A = new OleDbDataAdapter();
                System.Data.DataTable dt = new System.Data.DataTable();
                //Get Data From Object Type variable
                A.Fill(dt, Dts.Variables["User::VarObject"].Value);
                
                //Construct File Full Path by using Varaible values
              string FileFullPath = DirectoryPath + FileName +"_"+datetime+ FileExtension;

          int i = 0;
          
           StreamWriter sw = null;
           sw = new StreamWriter(FileFullPath, false);
           
                // Write the Header Row to File
           int ColumnCount = dt.Columns.Count;
           for (int ic = 0; ic < ColumnCount; ic++)
           {
               sw.Write(dt.Columns[ic]);
               if (ic < ColumnCount - 1)
               {
                   sw.Write(FileDelimiter);
               }
           }
           sw.Write(sw.NewLine);

           // Write All Rows to the File
           foreach (DataRow dr in dt.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();
                Dts.TaskResult = (int)ScriptResults.Success;
        }
             catch (Exception exception)
            {
                // Create Log File for Errors
                using (StreamWriter sw = 
File.CreateText(Dts.Variables["User::VarLogFolder"].Value.ToString()
                    + "\\" + "ErrorLog_" + datetime + ".log"))
                {
                    sw.WriteLine(exception.ToString());
                    Dts.TaskResult = (int)ScriptResults.Failure;
                }

            }



Step 6:
Save the script and then Run your SSIS Package, it should create flat file in destination folder with date-time. You can change the type of extension , delimiter and can test by adding or dropping/adding columns to source table or view if all works fine.

I executed with by provide extension csv, delimiter as , and it created below flat file from my table.
How to create flat file dynamically from SQL Server Table or View in SSIS Package


Check out our other posts/ Videos on related topics

11 comments:

  1. Hey Aamir, Nice blog... Keep it up ... :-)

    ReplyDelete
  2. Hello Aamir,

    I am getting error in the for loop area i=0;i<>

    Thanks,
    Ram

    ReplyDelete
  3. I also found it is not writing the top header rows. Please help

    ReplyDelete
  4. Hello Aamir,

    Please rectify your code:

    int i = 0;


    StreamWriter sw = null;


    sw = new StreamWriter(filePath, false);

    for (i = 0; i < dt.Columns.Count; i++)
    {
    sw.Write(dt.Columns[i].ToString() + Dts.Variables["User::VarDelimeter"].Value.ToString());

    }

    sw.WriteLine();


    foreach (DataRow row in dt.Rows)
    {
    object[] array = row.ItemArray;


    for (i = 0; i < array.Length; i++)
    {
    sw.Write(array[i].ToString() + Dts.Variables["User::VarDelimeter"].Value.ToString());

    }

    sw.WriteLine();

    }


    sw.Close();

    This one works perfectly fine for all tables and writes with the header.

    Regards,
    Ram

    ReplyDelete
  5. Hi ALL,
    Thank you for first of all, I have follow all the steps but unfortunately i facing this error which I have no idea what is that about. Anyone can help me on that?

    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.

    at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables100.get_Item(Object Index)
    at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
    --- End of inner exception stack trace ---
    at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
    at ST_a68df577fb0d433da91649b419571c91.csproj.ScriptMain.Main()
    --- End of inner exception stack trace ---
    at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
    at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
    at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
    at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
    at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

    ReplyDelete
  6. Hey,

    This is a fantastic post to produce dynamic flat files. But I am running into problems when I set my VarDelimeter value as comma when i have any table's values with commas.

    Eg: If i have a column with value "Alberta" it is fine. But when i have something like "Alberta, CA" then the problem starts. In the output it is resulting as Alberta as one column and CA as one column.

    Can any one suggest how can I get rid of this?

    Thanks

    Sai

    ReplyDelete
  7. I'd add a variable for a configurable text qualifier and throw it on other side of the [text qualifier] + array[i].tostring() [text qualifier] if the column string contains the delimiter.

    ReplyDelete
  8. Finding out that r-alpha lipoic acid canada is available in Canada is exciting for health-conscious people. The supplement is fascinating to examine due to its purported advantages, including its position as an antioxidant and possible support for metabolic functioning. Which Canadian sources or brands are the best? I would appreciate it!

    ReplyDelete