Solved

SSIS creates 0 KB PDF files

Posted on 2011-03-01
5
1,284 Views
Last Modified: 2012-05-11
A co-worker has left our company and now I am responsible for maintaining our SQL Server Integration Services (SSIS) code.  I have a project that runs perfectly normal on my coworkers computer, but when I copy all the files to my computer, it errors out.

Specifically, the package creates a PDF report for a list of users, and then emails each report to each user.  On my computer all of the PDF reports are coming back at 0 KB and when the package attempts to email the file, I get the following error:

Error: 0x1 at Script Task: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.IOException: The process cannot access the file 'C:\FC\adam.hargis.pdf' because it is being used by another process.

The way the package works, we have a script task that references some VB.NET code.  This code calls a SQL Server Report.  After all reports are created, we then look through the users and send them each their report.

Given that it works on one computer, and not another, I am thinking its some security or environmental issue and not code.  Problem is I am relatively new to SSRS and not sure where to look.  From what I can tell all the folder permissions are the same for the default "User" group on each computer as well as for the WindowsId I am using on each machine.

Any other thoughts are suggestions would be appreciated.

Thanks,
-Tim
0
Comment
Question by:straubtm
  • 3
  • 2
5 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
Is the VB.NET code rendering the report and then exporting to PDF? Can you show us that code?
0
 
LVL 1

Author Comment

by:straubtm
Comment Utility
CodeCruiser -

Thanks for the response.  Below is a copy of the code.  Since the code works fine on another machine, and even when I copy the package to the production environment, I kinda doubt the error is here.  I am going to look more closely at the package configuration.

Thanks,
-Tim


' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
	Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

	Enum ScriptResults
		Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
		Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
	End Enum
	

	' The execution engine calls this method when the task executes.
	' To access the object model, use the Dts property. Connections, variables, events,
	' and logging features are available as members of the Dts property as shown in the following examples.
	'
	' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
	' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
	' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
	'
	' To use the connections collection use something like the following:
	' ConnectionManager cm = Dts.Connections.Add("OLEDB")
	' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
	'
	' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
	' 
	' To open Help, press F1.

	Public Sub Main()
        Dim url, destination As String

        destination = Dts.Variables("DestinationPath").Value.ToString + Dts.Variables("UserName").Value.ToString + ".pdf"
        url = Dts.Variables("URL").Value.ToString + Dts.Variables("AccountID").Value.ToString + "&TimeFrame=r&rs:Format=PDF"

        SaveFile(url, destination)
        Dts.TaskResult = ScriptResults.Success
	End Sub

    Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)
        Dim loRequest As System.Net.HttpWebRequest
        Dim loResponse As System.Net.HttpWebResponse
        Dim loResponseStream As System.IO.Stream
        Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
        Dim laBytes(256) As Byte
        Dim liCount As Integer = 1
        Try

            loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
            loRequest.Credentials = System.Net.CredentialCache.DefaultCredentials
            loRequest.Timeout = 600000
            loRequest.Method = "GET"
            loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
            loResponseStream = loResponse.GetResponseStream
            Do While liCount > 0
                liCount = loResponseStream.Read(laBytes, 0, 256)
                loFileStream.Write(laBytes, 0, liCount)
            Loop
            loFileStream.Flush()
            loFileStream.Close()
        Catch ex As Exception
        End Try
    End Sub
End Class

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
It looks like the problem is with a dependency. Have you installed all the runtimes etc such as the SQL SMO?
0
 
LVL 1

Accepted Solution

by:
straubtm earned 0 total points
Comment Utility
Actually, I figured out the issue.  The code set the credentials as:

loRequest.Credentials = System.Net.CredentialCache.DefaultCredentials

...and this takes the current user you are logged in as and passes it to the server to authenticate in order to generate the report data via filestream.

After reviewing the security logs on the server, I realized that my WindowsId has a different PW on my local machine and server whereas my former co-worker's Id and PW were the same in both locations.  I changed the code to what is attached below and it worked.

-Tim
Imports System.Net
Dim myCredentials As New NetworkCredential("WindowsId", "password")
loRequest.Credentials = myCredentials

Open in new window

0
 
LVL 1

Author Closing Comment

by:straubtm
Comment Utility
I resolved the issue on my own.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now