April 9, 2008 at 5:24 pm
Hi everybody, I have a DTS that export data of the SELECT from my server PERU8\PERUEIGHT and save the result in a excel D:\Recuperados.xls. How I can doing for the excel have the date of the past week ?
By example: Recuperados170308-230308.xls, Recuperados240308-300308.xls, ...
Thanks by your help
P.D: The object that I'm using is in the attachment.
April 12, 2008 at 4:25 pm
Under Connection Managers modify Excel connection's properties. Use Expressions property - > add property expression for the ConnectionString - >
"C:\\Recuperados"+REPLACE(SUBSTRING((DT_STR, 30, 1252) DATEADD("dd",-7,GETDATE()) ,1,10) ,"-","")+"-"+ REPLACE(SUBSTRING((DT_STR, 30, 1252) GETDATE() ,1,10),"-","")+ ".xls"
This will generate file name as C:\Recuperados20080405-20080412.xls. You can use substring function to change it to the format in your example.
April 15, 2008 at 8:52 am
It may not be an option but I would recommend building reports of such a nature with SQL Server Reporting Services and creating a subscription to automate the delivery of it.
Ben Sullins
bensullins.com
Beer is my primary key...
May 7, 2008 at 10:57 am
Hi, thanks by your answer. I has add to Expressions property, the ConnectionString inserting the code that give me, but when run the package show this error:
Nonfatal errors occurred while saving the package:
Error at Package [Connection manager "Excel Recuperados"]: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.
Error at Package: The result of the expression ""C:\\Recuperados"+REPLACE(SUBSTRING((DT_STR, 30, 1252) DATEADD("dd",-7,GETDATE()) ,1,10) ,"-","")+"-"+ REPLACE(SUBSTRING((DT_STR, 30, 1252) GETDATE() ,1,10),"-","")+ ".xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
May 7, 2008 at 11:47 am
Place the expression in the ExcelFilePath property. Connection strings are for OLEDB type of connections, whereas the excel file path is the physical path of where the file actually is/will be located. I had to learn this the hard way myself.
Marvin Dillard
Senior Consultant
Claraview Inc
May 7, 2008 at 3:31 pm
Hi, I has doing that you wrote, but show this error:
Error at Data Flow Task [Excel Destination Recuperados [1570]]: An OLE DB error has occurred. Error code: 0x80040E37.
Error at Data Flow Task [Excel Destination Recuperados [1570]]: Opening a rowset for "Hoja1$" failed. Check that the object exists in the database.
Error at Data Flow Task [DTS.Pipeline]: "component "Excel Destination Recuperados" (1570)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
I has read in others post that it isn't neccesary that exist the excel dinamyc, only the excel that indicate in the Excel connection setting -> Excel file path->D:\Recuperados.xls. This file exist and is directioning to Hoja$1, that too exists.
May 7, 2008 at 4:14 pm
Lisset
What is happening is that you need to remap the source to target mappings, once that is accomplished the error should disappear.
Marvin Dillard
Senior Consultant
Claraview Inc
July 1, 2008 at 6:44 am
How do i map the source to target file. I am getting the following error if i try to change the Data access mode to : Table name or view name variable. When a select the variable name the following error is produced
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [Excel Destination [3675]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
Error at Data Flow Task [Excel Destination [3675]]: Opening a rowset for "ALH" failed. Check that the object exists in the database.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
How do i go about this problem.
December 3, 2008 at 9:16 am
I was able to get this working through the sample/help posted on MS forums here:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2042582&SiteID=17
HTH,
Shari
July 4, 2012 at 12:08 am
can any one help me with this error please.
i need to create dynamic excel file depending upon the nos of rows in the table. Example: if there are 4 rows then 4 excel files
[Excel Destination [23]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
[Excel Destination [23]] Error: Opening a rowset for "ExcelDestination" failed. Check that the object exists in the database.
[SSIS.Pipeline] Error: "component "Excel Destination" (23)" failed validation and returned validation status "VS_ISBROKEN".
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply