Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SSIS creates 0 KB PDF files

Posted on 2011-03-01
5
Medium Priority
?
1,498 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

618 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