Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Grifter
Constraint Violating Yak Guru
274 Posts |
 Posted - 2013-04-11 : 06:43:01
|
Hi AllSo I have a package with a few control flow tasks manipulating SQL server data and then I have a script task that builds a string that defines where I want to output the data from SQL server to a new excel file. My excel file name is to be dynamic so for April I have a file called MarchData.xls - the previous month's data. So my script task handles this as the code below shows:Dim strFileName As String Dim strFolderName As String Dim strMonthNameShort As String Dim strFullPath As String Dim lastMonth As New Date(DateTime.Today.Year, DateTime.Today.Month - 1, 1) strFileName = Dts.Variables("FileName").Value.ToString strFolderName = Dts.Variables("FolderName").Value.ToString strMonthNameShort = MonthName(lastMonth.Month, True) strFullPath = strFolderName & strFileName.Substring(0, strFileName.IndexOf(".xls")) & strMonthNameShort & ".xls"Now I thought that when I created a data flow task to take my data from SQL server to excel that I would set my excel connection manager connectionstring to use my @FullPath variable but I understand that initially the excel connection manager needs a default connection. I tried this but it forces you to pick a sheet in the manager and do the mappings so if you don't set them it cancels the initial connection when you close the editor window (OK is grayed out).If I pick a sheet and close excel connection editor it soon fails afterwards with:quote:
TITLE: Package Validation Error------------------------------Package Validation Error------------------------------ADDITIONAL INFORMATION:Error at Data Flow Task [Excel Destination [16]]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.Error at Data Flow Task [DTS.Pipeline]: component "Excel Destination" (16) failed validation and returned error code 0xC020801C.Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.Error at Data Flow Task: There were errors during task validation.Error at Package [Connection manager "Excel Connection Manager"]: An OLE DB error has occurred. Error code: 0x80040E4D. (Microsoft.DataTransformationServices.VsIntegration)
So is there a way to have a dynamic connection string without it falling over, I also tried the delay validation setting but no dice? finally when I use the dynamic connection how does it know what to map to in the new file, if the file has no column names, and even if it did have column names?Thanks for any advice and helpG |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-12 : 05:13:15
|
| The reason may be because you've not created sheet with columnnames before hand. What you could do is to add an Execute sql task with connection as your dynamic excel connection and inside that you add create table statement to create sheet with columnnames in excelThe create statement can be obtained by just mapping to test excel sheet at design time and click on new near to sheetname and it will automatically generate create statement for your with columns based on your source.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
Grifter
Constraint Violating Yak Guru
274 Posts |
Posted - 2013-04-12 : 10:54:19
|
Ok so here's what I have now and it fails on my data flow task:After I do all my SQL server loading tables etc I have a script task as before that loads the fullpath variable with the dynamic file name attached to the full path name.I created an excel connection manager and loaded it up and mapped it to a sheet on a workbook that has the same columns as in my SQL Server table. then a data flow task after my script task with an ole db source connected to my SQL server table and a destination using the excel connection manager I just made.I also put a default value in my fullpath and monthName variables as remember I had to do this before one time when using a for each loop.In my excel connection I have an an excel file path using @[User::FullPath] so I imagine that when the process gets here it will use the fullpath variable but since this file doesn't yet exist how does it know what the mapping is? Ok I get these errors below:quote:
Error: 0xC0202009 at Data Flow Task, Excel Destination [16]: An OLE DB error has occurred. Error code: 0x80040E37.Error: 0xC02020E8 at Data Flow Task, Excel Destination [16]: Opening a rowset for "Sheet2$" failed. Check that the object exists in the database.Error: 0xC004706B at Data Flow Task, DTS.Pipeline: "component "Excel Destination" (16)" failed validation and returned validation status "VS_ISBROKEN".Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.Error: 0xC0024107 at Data Flow Task: There were errors during task validation.Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.SSIS package "Package.dtsx" finished: Failure.
|
|
|
|
Grifter
Constraint Violating Yak Guru
274 Posts |
Posted - 2013-04-12 : 11:53:32
|
I realised that I should have my data flow last and put an execute SQL task before this but after the script task. In the execute sql task I create a table in the excel using the excel connection with the dynamic file name in the filepath variable. It is still falling over however with this statement:quote:
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "CREATE TABLE ? ( `field1` DATETIME, `field2` INTEGER, `field3` NVARCHAR(255), `field4` NVARCHAR(255), `field5` NVARCHAR(255) )" failed with the following error: "Syntax error in CREATE TABLE statement.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Execute SQL Task
I use the question mark at my create table statement and a parameter mapping for the monthName, which is set in the script task using user::@monthName, and is what I want the sheet to be called with the table in it. I also have the ResultSet in the Sql task set to single row as per online guide if you are using parameters in sql task.So the idea is create the table against the dynamic connection then populate the table in the dynamic connection. Not sure now why this is failing. |
|
|
|
Grifter
Constraint Violating Yak Guru
274 Posts |
Posted - 2013-08-27 : 11:48:30
|
Problem was that quote:
user::
should have been quote:
User::
|
|
|
|
mattpenner
Starting Member
1 Post |
Posted - 2014-02-04 : 22:57:23
|
quote:
Originally posted by visakh16...The create statement can be obtained by just mapping to test excel sheet at design time and click on new near to sheetname and it will automatically generate create statement for your with columns based on your source.
Thanks! I didn't have this issue but I was having a parsing error because I had the wrong quote marks. Your tip helped me easily generate a perfect create statement. :) |
|
|
|
|
|
|
|
|
Dynamic connection String Excel