How to create empty .txt file using ssis without scripting

Question

My first task is File system task. This task creates a folder. Now I'm not able to figure out how to create a blank text file in the folder just created by File system task.

I have created 2 variables - mypath (c:\sample\files) and myfilename (test.txt).

I have created new file connection and selected create file option from usage type and under file: I have given @[user::mypath] + @[user::myfilename].

But I am getting error as:

Package validation error.

Can someone help me how to create a new text file?

Solution 1

The easiest way that comes to mind is to use the Execute Process Task. The process you will use is cmd.exe This is the command line. From there you'll spawn a new instance and terminate /c and the thing you'll do there is run the echo command. We'll specify echo off so that nothing is printed. We'll push the results of that into a file using the standard output redirection operator >

The command line call would look like

C:\Users\billinkfc>cmd.exe /C echo off > \tmp\tmp\t2.txt

SSIS Variables

I created two variables

The first, CurrentFileName, contains a path to what I want to create. C:\ssisdata\so_32748216.txt

The second uses an expression to build the InputParameters variable's value. "/C echo off > " + @[User::CurrentFileName]

You fill in the correct value for the first variable, or it's set during run time, and this second one will do the right thing for creating an empty file at at that location.

Using it

Add an Execute Process Task onto your Control Flow. Configure it like this

In the Expressions tab, specify the Arguments are @[User::InputParameters]. This ensures that if you the value for CurrentFileName changes, the task changes location with it.

Biml

Biml, the business intelligence markup language, is something everyone doing SSIS should know. There are many reasons why but in this case, it's because the following code will create an SSIS package for you with the variables as described above along with an Execute Process Task - how cool is that?

All you need to do is install bids helper for your version of visual studio/bids/ssdt-bi. In your integration services project, right click on the project and select Add new biml file. Paste the following into and then right click and select Generate SSIS Package.

Win.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="so_32748216">
            <Variables>
                <Variable DataType="String" Name="CurrentFileName">c:\ssisdata\so_32748216.txt</Variable>
                <Variable DataType="String" Name="InputParameters" EvaluateAsExpression="true"><![CDATA["/C echo off > "]]> + @[User::CurrentFileName]</Variable>
            </Variables>
            <Tasks>
                <ExecuteProcess 
                    Executable="C:\Windows\System32\cmd.exe" 
                    Arguments="/C echo off > OverrideViaExpression.txt"
                    Name="EPT Create file">
                    <Expressions>
                        <Expression ExternalProperty="Arguments">@[User::InputParameters]</Expression>
                    </Expressions>
                </ExecuteProcess>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Solution 2

AFAIK You cannot create an empty text file using File system task.

If you don't want to use script task,

1. Create an empty text file and create a File Connection_Manager pointing to that file. Do this at Project level connections(from SQL Server 2012 onwards).

2. Parameterize the connection string for the newly created file connection manager.
3. Now, use the "File System Task" to copy this empty file to the required destination folder.

4. Reuse this connection manager for generating empty file throughout the project