?
Solved

SSIS to create new excel file each time procedure runs

Posted on 2009-02-12
8
Medium Priority
?
1,324 Views
Last Modified: 2012-05-06
Is this possible?  If so how do i go about enabling it, at the moment it only appears to work if the files that are originally created are amended with the new data however due to duplications this isn't how we want our reports....  i have attached the error log should it be anything different:



Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
02/12/2009 14:49:09,Rep Reports,Error,0,SAP,Rep Reports,(Job outcome),,The job failed.  The Job was invoked by User ADVENT\Administrator.  The last step to run was step 1 (Alec Liggins).  The job was requested to start at step 1 (Alec Liggins).,00:00:02,0,0,,,,0
02/12/2009 14:49:09,Rep Reports,Error,1,SAP,Rep Reports,Alec Liggins,,Executed as user: SAP\SYSTEM. ...Version 9.00.3042.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  14:49:10  Error: 2009-02-12 14:49:10.89     Code: 0xC0202009     Source: Alec Liggins Connection manager "DestinationConnectionExcel"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft JET Database Engine"  Hresult: 0x80004005  Description: "The Microsoft Jet database engine cannot open the file '\\server1\Users\keir\My Documents\Rep Reports\Alec Liggins.xls'.  It is already opened exclusively by another user<c/> or you need permission to view its data.".  End Error  Error: 2009-02-12 14:49:10.89     Code: 0xC00291EC     Source: Preparation SQL Task Execute SQL Task     Description: Failed to acquire connection "DestinationConnectionExcel". Connection may not be configured correctly or you may not have the right permis...  The package execution fa...  The step failed.,00:00:02,0,0,,,,0
0
Comment
Question by:KeirMcCann
  • 4
  • 4
8 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23622699
The destination file exist? Do you have permissions to open it? And to get to folder?

Give more details about the structure you have here... using screenshots...
regards,
Pedro
0
 

Author Comment

by:KeirMcCann
ID: 23622733
/SQL "\Alec Liggins" /SERVER SAP  /CONNECTION DestinationConnectionExcel;"\"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\server1\Users\keir\My Documents\Rep Reports\Alec Liggins.xls;Extended Properties=""EXCEL 8.0;HDR=YES"";\"" /CONNECTION SourceConnectionOLEDB;"\"Data Source=(local);Initial Catalog=advanced;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;\"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

This is from the Command Line of the step from SQL Server Agent.  The file doesnt exist however the path is there, i want it to create the file automatically
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23622883
You have a phisical excel file in destination.
Create a template and move it to destination each time execute a SSIS package...

Check the attached package...
Add the attached package to a existent project
Update all connections and run.
Helped?

Regards,
Pedro
www.pedrocgd.blogspot.com

Package-EE74-dtsx.txt
test.xls
Source.txt
myfile-20090113-1721.xls
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:KeirMcCann
ID: 23622987
hi mate,

thanks for your patience, im pretty new with this so i dont really have much experience importing/exporting, i have so far simply used dtswizard and server agent to attempt to schedule it.  When i create the job initially it creates the spreadsheet OK and is a 100% success however when i run from SQL agent it comes with the above error
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23631643
I suggest you to downalod the simple package I attached before or read a book about SSIS.
I have a link in my blog for a free SQL 2008 book.
Regards,
Pedro

www.pedrocgd.blogspot.com
0
 

Author Comment

by:KeirMcCann
ID: 23631721
Thanks for your time pedro im sure your very busy!!

I ran your package however it came up with the following error:

02/13/2009 11:44:14,kk,Error,1,SAP,kk,test,,Executed as user: SAP\SYSTEM. ...rver Execute Package Utility  Version 9.00.3042.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  11:44:14  Error: 2009-02-13 11:44:14.48     Code: 0xC0011007     Source: {8F84CD0A-DAF8-4226-8508-29757E554B46}      Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted.  End Error  Error: 2009-02-13 11:44:14.48     Code: 0xC0011002     Source: {8F84CD0A-DAF8-4226-8508-29757E554B46}      Description: Failed to open package file "\\server1\Users\keir\My Documents\Rep Reports\Package-EE74.dtsx" due to error 0x80070005 "Access is denied.".  This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.  End Error  Could not load .  The step failed.,00:00:00,0,0,,,,0
0
 

Author Comment

by:KeirMcCann
ID: 23631724
nm i didnt update the connections sorry
0
 
LVL 22

Accepted Solution

by:
PedroCGD earned 2000 total points
ID: 23631749
also open from Visual Studio to debug and understand it
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 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