SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Dynamic connection String Excel
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Grifter
Constraint Violating Yak Guru

264 Posts

Posted - 04/11/2013 :  06:43:01  Show Profile  Reply with Quote
Hi All

So 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 help

G


Edited by - Grifter on 04/11/2013 06:58:07

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 04/11/2013 :  07:51:54  Show Profile  Reply with Quote
Refer this link
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/360d179f-fa37-430b-9064-fc38f9face05

I think the problem is with setting for 32-bit ( not the 64-bit)

If that is not the problem,
http://blogs.msdn.com/b/ramoji/archive/2009/03/10/the-acquireconnection-method-call-to-the-connection-manager-failed-with-error-code-0xc0202009.aspx

Edited by - bandi on 04/11/2013 07:54:32
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/12/2013 :  05:13:15  Show Profile  Reply with Quote
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 excel

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.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Grifter
Constraint Violating Yak Guru

264 Posts

Posted - 04/12/2013 :  10:54:19  Show Profile  Reply with Quote
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.


Go to Top of Page

Grifter
Constraint Violating Yak Guru

264 Posts

Posted - 04/12/2013 :  11:53:32  Show Profile  Reply with Quote
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.

Edited by - Grifter on 04/12/2013 11:55:54
Go to Top of Page

Grifter
Constraint Violating Yak Guru

264 Posts

Posted - 08/27/2013 :  11:48:30  Show Profile  Reply with Quote
Problem was that
quote:
user::
should have been
quote:
User::
Go to Top of Page

mattpenner
Starting Member

USA
1 Posts

Posted - 02/04/2014 :  22:57:23  Show Profile  Reply with Quote
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. :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000