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.

Dynamically changing reports at runtime

Hi - I'm trying to decide among reporting options without spending too much
time going down the wrong path, so I hope someone can answer my questions
about Reporting Services capabilities...
With Reporting Services, can I dynamically (at runtime via code):
1) add or remove subreports from a report?
2) make a subreport visible or hidden?
3) add or delete columns?
4) make columns visible or hidden?
5) add or delete tables?
6) make a table visible or hidden?
Is doing any of these things fairly straightforward?
Thanks so much in advance,
SherylYes, you can use an expression for the Hidden property of respective report
elements to dynamically show/hide them at runtime.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sheryl Landon" <shland@.comcast.net> wrote in message
news:OoDKy5$pEHA.3244@.tk2msftngp13.phx.gbl...
> Hi - I'm trying to decide among reporting options without spending too
much
> time going down the wrong path, so I hope someone can answer my questions
> about Reporting Services capabilities...
> With Reporting Services, can I dynamically (at runtime via code):
> 1) add or remove subreports from a report?
> 2) make a subreport visible or hidden?
> 3) add or delete columns?
> 4) make columns visible or hidden?
> 5) add or delete tables?
> 6) make a table visible or hidden?
> Is doing any of these things fairly straightforward?
> Thanks so much in advance,
> Sheryl
>

Dynamic Where clause in Stored Procedure

Hi, I have several parameters that I need to pass to stored procedure but sometimes some of them might be null. For example I might pass @.Path, @.Status, @.Role etc. depending on the user. Now I wonder if I should use dynamic Where clause or should I use some kind of switch, maybe case and hardcode my where clause. I first created several stored procedures like Documents_GetByRole, Documents_GetByRoleByStatus ... and now I want to combine them into one SP. Which approach is better. Thanks for your help.dynamic where clauses would eliminate most of the benefits of the stored procedure.

For every ad-hoc query that's executed, a new execution plan and compiliation takes place. Furthermore, your ram goes up.

A lot of databases will slow down with usage due to this. Therefore, hardcoding your where clauses is best. (like field = @.value)