Take the 2-minute tour ×

With the help of the following blog: I created a SQL Server Integration Services (SSIS) package. Reading from a .txt file I am able to create a relative XML file and save it in a specific path.

However since I have to loop over 50 or more txt files and generate, for each of them, a relative xml file, I would like to be able to automatically save the xml file with the same input name.

I have seen that with a foreach loop I can set ".txt" as input files, but how can I specify to dynamically take the input file name for the xml output file?
At the moment I use an hardcoded path for the File Connection manager used for set the output xml file path.

Moreover, instead of creating the xml file via VB script, would it be possible to create an XML template and use SSIS to fill specific keywords accordingly?
Something like:

share|improve this question
add comment

2 Answers

Use System.IO.Path.GetFileNameWithoutExtension in a script task:

Public Sub Main()
    Dim infile As String = CStr(Dts.Variables("InputFile").Value)
    Dim outfile As String = System.IO.Path.GetFileNameWithoutExtension(infile) + ".xml"
    Dts.Variables("OutputFile").Value = outfile
    Dts.TaskResult = ScriptResults.Success
End Sub
share|improve this answer
+1 - I ended up wrtiting the xml tags with With xmlWriter and I was able to achieve the same result. But thanks for the hint. –  Luca Oct 18 '11 at 13:30
add comment
up vote 0 down vote accepted

Here I post my solution in case it might help someone else. As said in the comment I used a script transformation to create dynamically the xml tags as wished:

With xmlWriter            
 .Write(FormatElement("rdf:Description rdf:about=""" + Me.Variables.strFileName + """ "))
 .Write(FormatElement("Date") + "" + FormatElement("Date", True))
 .Write(FormatElement("ValidFrom") + "" + FormatElement("ValidFrom", True))
 .Write(FormatElement("ValidTo") + "" + FormatElement("ValidTo", True))


End With
share|improve this answer
add comment

Your Answer


