Re: Modify SSIS package programmatically

Tech-Archive recommends: Fix windows errors by optimizing your registry

OOPS I am using SQL 2005 SSIS and when I wrote DTS packge I should have
written IS Package...

Thhank you for your answer and sorry for the mistake.

Anyway I found the solution for SSIS and if you anyone is interested, here
is the an exploded version of the code I used:
(I guess it's easier to understand the object model with the exploded
version of the code)

Microsoft.SqlServer.Dts.Runtime.Connections packageConnections =
package.Connections; // package is a Microsoft.SqlServer.Dts.Runtime.Package
object
Microsoft.SqlServer.Dts.Runtime.ConnectionManager packageExcelConnection =
packageConnections["Excel Connection Manager"]; // name of the Excel
connection or u can use an index instead

Microsoft.SqlServer.Dts.Runtime.DtsProperties connectionProperties =
packageExcelConnection.Properties;

Microsoft.SqlServer.Dts.Runtime.DtsProperty connectionExcelFilePathProp =
connectionProperties["ExcelFilePath"];

connectionExcelFilePathProp.SetValue(packageExcelConnection, "C:\DG.xls");

Best regards,

Francois.



"Darren Green" <darren.green@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:uRakCLSIGHA.2212@xxxxxxxxxxxxxxxxxxxxxxx
> Francois Malgreve wrote:
>> Hi All,
>>
>> I have a DTS package that I launch from a C# application.
>> The DTS package contains an Excel Connection. AS the location of the
>> Excel file can change dynamically, I need to be able to change the value
>> of the Excel Connection's "ExcelFilePath" property from my piece of C#
>> code.
>>
>> The problem is that I don't know how to access ExcelFilePath from the C#
>> code.
>> I have seen a class
>> Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerExcelClass that
>> has a "ExcelFilePath" prperty but the doc says : This class supports the
>> SQL Server 2005 infrastructure and is not intended to be used directly
>> from your code. Also I don't know how to get an instance from it.
>>
>> I can get an instance of the class
>> Microsoft.SqlServer.Dts.Runtime.ConnectionManager but that class is a
>> generic class for every connection managers and does not have a
>> ExcelFilePath property.
>>
>> Is there anyone who knows how I can change that property from an external
>> piece of code?
>>
>> Thanks in advance,
>>
>> Best regards,
>>
>> Francois Malgreve.
>>
>>
>
> The Microsoft.SqlServer.Dts.Runtime stuff is all SQL 2005, so no good
> whatsoever for DTS.
>
> To change the file path set the DataSource property of the connection
> object.
>
> oPkg.Connections(0).DataSource = "C:\DG.xls"
>
>
> --
> Darren
> http://www.sqldts.com
> http://www.sqlis.com


.

Relevant Pages

  • Re: DTS Global Variable problem - type mismatch
    ... But the problem is that the package still fails to run. ... of the properties within the DTS and run it from there then it works. ... > reference to the connection I have in my DTS. ...
    (microsoft.public.sqlserver.dts)
  • Re: DTSRun and Batch file
    ... >> install the DTS DLLs on the local machine. ... So the DTS package is running ... >> user to start a SQL Agent Job that runs the package. ... Darren Green (SQL Server MVP) ...
    (microsoft.public.sqlserver.dts)
  • Re: Error when running SQL 2000 dts Package in SQL Server 2005
    ... Lotus SQL Driver works fine. ... created a package in SQL 2000 and trying to call it in SQL 2005'S Legacy Data ... I am able to create a new table using this DTS ... OK so if this fails when you use the package in an Execute DTS 2000 Package ...
    (microsoft.public.sqlserver.dts)
  • Re: Scheduling DTS Packages on 64-bit SQL Server
    ... My understanding is that DTS components are not available for 64-bit SQL ... > I found the following comment on Microsoft's website in regards to scheduling> a DTS package on a 64-bit SQL Server installation. ... do I need> to buy a 32-bit copy of SQL Server to store my packages on to be able to> schedule them? ...
    (microsoft.public.sqlserver.dts)
  • RE: Same connection manager for multiple control flow tasks
    ... In the Execute DTS 2000 Package Task Editor, ... Select Existing Connection radion button. ... to be available as listed within the Connection Managers pane. ...
    (microsoft.public.sqlserver.dts)