August 23, 2004 at 4:10 pm
How do you set the Data source file name e.g.test.xls and the new table name that gets created from the convertion of the xls file to a sql table?
I want to pass them in before I load the package. Because the filename and resulting tablename will be different everytime...
code below does not work!
string
FILENAME = Session["FileName"].ToString();
package =
new Package2Class();
UCOMIConnectionPointContainer CnnctPtCont = (UCOMIConnectionPointContainer) package;
UCOMIConnectionPoint CnnctPt;
PackageEventsSink PES =
new PackageEventsSink ();
Guid guid =
new Guid("10020605-EB1C-11CF-AE6E-00AA004A34D5");
// UUID of PackageEvents Interface
CnnctPtCont.FindConnectionPoint(
ref guid, out CnnctPt);
int iCookie;
CnnctPt.Advise(PES,
out iCookie);
object pVarPersistStgOfHost = null;
string UserName="sa";
string Pass="toyota2k";
// Does not work...
package.GlobalVariables.Item("FileName").Value=FILENAME;
package.GlobalVariables.Item("TableName").Value="tester1";
package.LoadFromSQLServer(".", UserName, Pass, DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, null,
null, null, "Test", ref pVarPersistStgOfHost);
package.Execute();
package.UnInitialize();
package = null;
CnnctPt.Unadvise(iCookie); //a connection that is created by IConnectionPoint.Advise must be closed by calling IConnectionPoint.Unadvise to avoid a memory leak
August 24, 2004 at 2:58 am
I found few discrepancy from your code
1.You are using . in the place of Server name. Pls try to take the server name from app.config or hard code it.
2.You are accessing the global variable before loading the package. As the global variables are related to package. The code you run, creates an instance of package and manipulating the global variable. This not valid.
Pls find time to see the code below
public bool DownloadTable(string strPkg)
{
bool blntemp;
blntemp = false;
try
{
// Instantiate DTS Package and event handling
object objVarPersistStgOfHost = null;
DTS.Package objPackage = new DTS.PackageClass();
UCOMIConnectionPointContainer objCPContainer = (UCOMIConnectionPointContainer) objPackage;
UCOMIConnectionPoint objConnectionPoint;
Class_PackageEventsSink objEvents = new Class_PackageEventsSink ();
Guid objGuid = new Guid("10020605-EB1C-11CF-AE6E-00AA004A34D5"); // UUID of PackageEvents Interface
int intCookie;
objCPContainer.FindConnectionPoint(ref objGuid, out objConnectionPoint);
objConnectionPoint.Advise(objEvents,out intCookie);
objPackage.LoadFromSQLServer(strServerName,"","",DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection,"","","",strPkg,ref objVarPersistStgOfHost);
bool b = objPackage.FailOnError;
objPackage.FailOnError = true;
AddGlobalVariables(objPackage);
objPackage.Execute();
objPackage.UnInitialize();
objPackage = null;
blntemp=true;
intCount += 1;
return(blntemp);
}
catch(System.Runtime.InteropServices.COMException objCOMException)
{
string strTemp = objCOMException.Message.ToString();
return blntemp;
}
}
private void AddGlobalVariables(DTS.Package objPackage)
{
foreach(DTS.GlobalVariable global in objPackage.GlobalVariables)
{
switch(global.Name)
{
case TEXT_DEST_DATASOURCE:
objPackage.GlobalVariables.Remove(global.Name);
break;
case TEXT_WORK_ID:
objPackage.GlobalVariables.Remove(global.Name);
break;
case TEXT_STRING_INTIAL_CATALOG:
objPackage.GlobalVariables.Remove(global.Name);
break;
}
}
objPackage.GlobalVariables.AddGlobalVariable("DstDataSource",strAccessConnString);
objPackage.GlobalVariables.AddGlobalVariable("VarWorkId",strWorkId);
objPackage.GlobalVariables.AddGlobalVariable("strInitialCatalog",strInitialCatalog);
}
Thanks,
Ganesh
August 24, 2004 at 10:20 am
what is "TEXT_WORK_ID" ?
what is "TEXT_STRING_INTIAL_CATALOG" ?
c:\inetpub\wwwroot\ExcelUpload\Default.aspx.cs(304): The name 'TEXT_DEST_DATASOURCE' does not exist in the class or namespace 'ExcelUpload._Default'
c:\inetpub\wwwroot\ExcelUpload\Default.aspx.cs(307): The name 'TEXT_WORK_ID' does not exist in the class or namespace 'ExcelUpload._Default'
c:\inetpub\wwwroot\ExcelUpload\Default.aspx.cs(310): The name 'TEXT_STRING_INTIAL_CATALOG' does not exist in the class or namespace 'ExcelUpload._Default'
HELP...
August 25, 2004 at 12:45 am
Hi Robert,
sorry for replying very late. Those are all string constants.
You can modify for your needs. Now I gave an example as the global variables you have added are GlobalVariable1,GlobalVariable2,GlobalVariable3.
private void AddGlobalVariables(DTS.Package objPackage)
{
foreach(DTS.GlobalVariable global in objPackage.GlobalVariables)
{
switch(global.Name)
{
case "GlobalVariable1":
objPackage.GlobalVariables.Remove(global.Name);
break;
case "GlobalVariable2":
objPackage.GlobalVariables.Remove(global.Name);
break;
case "GlobalVariable3":
objPackage.GlobalVariables.Remove(global.Name);
break;
}
}
objPackage.GlobalVariables.AddGlobalVariable("GlobalVariable1",strAccessConnString);
objPackage.GlobalVariables.AddGlobalVariable("GlobalVariable2",strWorkId);
objPackage.GlobalVariables.AddGlobalVariable("GlobalVariable3",strInitialCatalog);
}
Get back to me if you find any problem sending your complete code.
thanks,
Ganesh
August 25, 2004 at 11:24 am
I might be missing the point but why wouldn't you just set the information into global variables and then use a dynamic properties task in the DTS package to configure the connection.
Seems like that would be more portable and less dependent upon the code you write to execute correctly.
Just my .02
Trey Johnson | Chief Business Intelligence Architect | Cizer Software (www.cizer.com)
Who? - Cizer - http://www.cizer.com/about.htm - Blog - http://www.sqlserverbi.com/
What? - Products enhancing Microsoft Business Intelligence - http://www.cizer.com/products.htm
Wow! - Empower your Developers.... NEW Drop In Reporting - http://www.cizer.com/cnr-drop-in-reporting.htm
How? - BI Training - http://www.cizer.com/training.htm - Cizer Solutions - http://www.cizer.com/solutions.htm
August 25, 2004 at 9:30 pm
Yep, That also sounds great only. Will modify accordingly.
Thanks for the suggestion.
Thanks,
Ganesh
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply