Dynamically create text file as destination

I am trying to create a text file from an SQL query on a SQL table. I would like the SSIS package to prompt for the file name and path. The text file is tab delimited and the text qualifier is a double quote.

Thanks,

Fred

SSIS by itself won't be able to prompt you. You'll have to write a custom executable perhaps to get this to work.|||

I think you could use the Windows.Forms.SaveFileDialog object in a Script Task.

|||

Thanks for the suggestions but I am new to programming. I have "played" a little with VBA in Excel. Can anyone get me started with some more VB code?

I would think this has been done before but I can not find any code in any SSIS forum for it.

Thanks,

Fred

|||

Couldn't you create a package variable called FilePath (for example) of type string and then read into it through a script task?

Something like the following would give you an input prompt and then store the value into the variable created:

Public Sub Main()

Dts.Variables("FilePath").Value = InputBox("Enter your file path", "Prompt").Trim()

Dts.TaskResult = Dts.Results.Success

End Sub

Then, you could just set the ConnectionString property in Expressions to the FilePath variable.

|||

SaveFileDialog would give the same result than InputBox except you are able to browse...

Dim fSaveFileDialog As New Windows.Forms.SaveFileDialog
fSaveFileDialog.ShowDialog()
Dts.Variables("fileName").Value = fSaveFileDialog.FileName

|||

Thanks for the suggestions but I am still lost.

I have a Data Flow Task which has a SQL Server Source. In the Data Flow Task I connect the SQL Server Source to what I believe is next - a Destination Script Component. I did not see anywhere you code put code in a Data Flow Destination - Flat File Destination.

In the Script Component it allows you to add code in Script Design box which is Visual Studio's designer window. How do I assign the filename to the new file I create?

Thanks for any help.

Fred

|||

Use expressions to assign the filename to the Flat File Destination (property Connection = User::variable)

Add a Script task before your data flow task. This script task will prompt for the file location and set the User::variable.

|||

Can I just question this approach? Is this for end users? If so then I don’t think using SSIS in this way is appropriate. SSIS is a server, so it is licensed as part of the full SQL Server license, and is not something you can install on client desktops as part of the normal Client access License. Each machine needs a full SQL Server license. To have a task offering a UI to the user means that package, and therefore SSIS itself, must be installed on the user's machine, making each user machine a full SSIS server install.