SSIS 2008: Write to a text file.

I am troubleshooting Data Flow in an SSIS package.  I want to write to a text file.  I am taking the package in steps.  As an example:

1)  Connection Manager
2)  If successful - write passed to text file else write error to text file

Then test additional steps:

1)  Connection Manager
2)  Export Data to Excel
3)  If successful - write passed to text file else write error to text file

Thanks


LVL 2
CipherISAsked:
Who is Participating?
 
CottonSwabCommented:
The simplest way to accomplish this is to add a Script Component inside a Data Flow Task with a type of 'transform' and add some code such as the following in the Input0_ProcessInputRow method.  Sub in whatever connection type you are trying to use for SqlConnection as appropriate:

using( StreamWriter writer = new StreamWriter( "C:\test.txt" ) )
        {
            try 
	        {	        
                using (SqlConnection conn = Connections.MyConnection.AcquireConnection(null) as SqlConnection)
                {
                    writer.WriteLine("connection succeeded");       
                }
            }
	        catch (Exception ex)
	        {
        		writer.WriteLine("connection failed - exception was " + ex.Message);
	        }
        }

Open in new window


As far as viewing the results in Excel, you shouldn't need to go through the work of passing it out to a csv file yourself, add a dummy component as the next step in the flow and add a data viewer in between them.
0
 
CipherISAuthor Commented:
while that would work - i am troubleshooting a package that somoene else wrote.  It is failing.  So I cant to just write a script after their component runs to let me know if what happened.  So the above script wouldn't work for what i'm tyring to do
0
 
CipherISAuthor Commented:
i did just try to create a script and connect a task i receive the following error:

"The destination component does not have any availalbe inputs for use in creating a path"
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
CottonSwabCommented:
The type of script you need to create (source, destination, or transformation) depends on where in the data flow it is sitting.  Perhaps I misled you in having you create a script transformation, as that would be most appropriate if you already had some data coming in to the script.  If your script is first, try creating a source transformation instead.
0
 
CipherISAuthor Commented:
my script is not first.  Its doing a database connection then I want to write to the text file.  I am writing to troubleshoot so I can find the error.
0
 
CipherISAuthor Commented:
I used the dataviewer which provided me with what i needed.
0
 
CipherISAuthor Commented:
Data Viewer is the solution to the problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.