Solved

SSIS creates 0 KB PDF files

Posted on 2011-03-01
5
1,415 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35020999
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
ID: 35027220
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
ID: 35035328
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
ID: 35036570
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
ID: 35081144
I resolved the issue on my own.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
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 …
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

690 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