How to create SQL Server Express server (INSTANCENAME) programmatically?

In deploying SQL Express, how do I create a new server (INSTANCENAME)
programmatically?
What would be the best approach to accomplish this.

Thank you so much,(ianicbass@.yahoo.com) writes:
> In deploying SQL Express, how do I create a new server (INSTANCENAME)
> programmatically?
> What would be the best approach to accomplish this.

You should look in Books Online under installing SQL Server, and
particularly unattended installation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How to create report parameter programmatically?

Hi,

I'm working on custom report manager. It manages "report entities" and

"report templates" (actually, RDLs uploaded on the server) and stores

one-to-many relation between them. The task is to store

"MasterEntityID" report parameter in every RDL and keep it up in actual

state whether RDL is being assigned to another entity or new RDL is

being uploaded and assigned. I've covered the first issue with

SetReportParameters() web method, but how should I deal with the second

one? Uploaded RDL may be short of the param, so I have to add it

programmatically while uploading.

Thanks,

AnatolyWhat exactly is the second issue? it looks like you have a custom application layer which can have complete control over the parameters.|||Yes, but actually I'm interested in adding new report parameter via web service. My application should do this if a developer of the uploading report hasn't defined necessary parameter manually. Is it possible?|||

The new parameter must be added to the report definition under the <ReportParameters> element. SetReportParameters won't let you configure the new parameter if it is not prior added to the report definition. This makes sense considering that you will probably need also to link the parameter to the report query. In fact, you won't need SetReportParameters for new parameters b/c once you add the parameter to RDL and call SetReportDefinition, the parameter will be created automatically for you.

As a side note, if you your application requires extensive reading/writing to RDL, you may benefit from buidling an object model on top of RDL that knows how to deserialize/serialize from/to RDL to avoid tedious XMLDOM programming and XPATH references.

|||

Teo, thanks for your clear reply. I'd think of object model. Mostly because I'm going to generate some rdl templates (with datasources, datasets and queries definitions) programmatically in future.

Thank you once again and Happy New Year!)

|||Happy New Year to you too.

How to create an excel connection manager programmatically ?

I am following the samples in the online books. I've got an OLEDB connection manager and source component defined. I now want to create an Excel connection manager and destination component (see below). How can I do this?

Dim conMgr As ConnectionManager = package.Connections.Add("OLEDB")

conMgr.ConnectionString = "Provider=SQLOLEDB.1;" "Data Source=FLL-EIS;Initial Catalog=DataLoad;" & _

"Integrated Security=SSPI;"

conMgr.Name = "SSIS Connection Manager for OLE DB"

conMgr.Description = "OLE DB connection to FLL-EIS."

' Create and configure an OLE DB source component.

Dim source As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

source.ComponentClassID = "DTSAdapter.OleDbSource"

' Create the design-time instance of the source.

Dim srcDesignTime As CManagedComponentWrapper = source.Instantiate

srcDesignTime.ProvideComponentProperties()

' Assign the connection manager.

source.RuntimeConnectionCollection(0).ConnectionManager = _

DtsConvert.ToConnectionManager90(conMgr)

' Set the custom properties of the source.

srcDesignTime.SetComponentProperty("AccessMode", 2)

srcDesignTime.SetComponentProperty("SqlCommand", Dts.Variables("User::gsQuery").Value.ToString)

' Connect to the data source,

' and then update the metadata for the source.

srcDesignTime.AcquireConnections(Nothing)

srcDesignTime.ReinitializeMetaData()

srcDesignTime.ReleaseConnections()

' Create and configure an OLE DB destination. (This is where I need help, as this code does not work)

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' Where can I find documentation about the various ComponentClassIds and Properties?

destDesignTime.ProvideComponentProperties()

destDesignTime.SetComponentProperty("ExcelFilePath", Dts.Variables("User::gsExcelFile").Value.ToString)

destDesignTime.SetComponentProperty("TableName", Dts.Variables("User::gsSheetName").Value.ToString)

Here are a couple of wrapper functions I have for creating connections, including an Excel connection-

Code Snippet

private static ConnectionManager AddExcelConnection(Package package, string filename)

{

return AddConnection(package, "EXCEL", String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\"", filename));

}

private static ConnectionManager AddSqlConnection(Package package, string server, string database)

{

return AddConnection(package, "OLEDB", String.Format("Provider=SQLOLEDB.1;Data Source={0};Persist Security Info=False;Initial Catalog={1};Integrated Security=SSPI;", server, database));

}

private static ConnectionManager AddConnection(Package package, string type, string connectionString)

{

ConnectionManager manager = package.Connections.Add(type);

manager.ConnectionString = connectionString;

manager.Name = String.Format("{0} Connection", type);

return manager;

}

Call it like this -

Code Snippet

Package package = new Package();

// Add the Excel connection

ConnectionManager excelConnection = AddExcelConnection(package, @."C:\Temp\Export.xls");

Is your destination code correct? I the properties look wrong, I always used -

componentInstance.SetComponentProperty("AccessMode", 2);

componentInstance.SetComponentProperty("SqlCommand", query);

|||

Thanks for the info, Darren. So, now I have the following code, but how do I specify the destination table (sheet) name?

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

|||

Code Snippet

componentInstance.SetComponentProperty("AccessMode", 0);

componentInstance.SetComponentProperty("OpenRowset", sheetName);

I was wrong above, it is OpenRowset for teh table/sheet not SqlCommand.

|||

Thanks for the reply, Darren. The first SetComponentProperty worked, but not the second, which gets a runtime exception. Is the property under a different name? Thanks for your help!

' Create and configure an OLE DB destination.

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

conDest.Name = "Excel File"

conDest.Description = "Excel File"

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' The ProvideComponentProperties method creates a default input.

destDesignTime.ProvideComponentProperties()

destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)

destDesignTime.SetComponentProperty("AccessMode", 0)

'runtime Exception here

destDesignTime.SetComponentProperty("OpenRowSet", "functions")

|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2175638&SiteID=1

How to create an excel connection manager programmatically ?

I am following the samples in the online books. I've got an OLEDB connection manager and source component defined. I now want to create an Excel connection manager and destination component (see below). How can I do this?

Dim conMgr As ConnectionManager = package.Connections.Add("OLEDB")

conMgr.ConnectionString = "Provider=SQLOLEDB.1;" "Data Source=FLL-EIS;Initial Catalog=DataLoad;" & _

"Integrated Security=SSPI;"

conMgr.Name = "SSIS Connection Manager for OLE DB"

conMgr.Description = "OLE DB connection to FLL-EIS."

' Create and configure an OLE DB source component.

Dim source As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

source.ComponentClassID = "DTSAdapter.OleDbSource"

' Create the design-time instance of the source.

Dim srcDesignTime As CManagedComponentWrapper = source.Instantiate

srcDesignTime.ProvideComponentProperties()

' Assign the connection manager.

source.RuntimeConnectionCollection(0).ConnectionManager = _

DtsConvert.ToConnectionManager90(conMgr)

' Set the custom properties of the source.

srcDesignTime.SetComponentProperty("AccessMode", 2)

srcDesignTime.SetComponentProperty("SqlCommand", Dts.Variables("User::gsQuery").Value.ToString)

' Connect to the data source,

' and then update the metadata for the source.

srcDesignTime.AcquireConnections(Nothing)

srcDesignTime.ReinitializeMetaData()

srcDesignTime.ReleaseConnections()

' Create and configure an OLE DB destination. (This is where I need help, as this code does not work)

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' Where can I find documentation about the various ComponentClassIds and Properties?

destDesignTime.ProvideComponentProperties()

destDesignTime.SetComponentProperty("ExcelFilePath", Dts.Variables("User::gsExcelFile").Value.ToString)

destDesignTime.SetComponentProperty("TableName", Dts.Variables("User::gsSheetName").Value.ToString)

Here are a couple of wrapper functions I have for creating connections, including an Excel connection-

Code Snippet

private static ConnectionManager AddExcelConnection(Package package, string filename)

{

return AddConnection(package, "EXCEL", String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\"", filename));

}

private static ConnectionManager AddSqlConnection(Package package, string server, string database)

{

return AddConnection(package, "OLEDB", String.Format("Provider=SQLOLEDB.1;Data Source={0};Persist Security Info=False;Initial Catalog={1};Integrated Security=SSPI;", server, database));

}

private static ConnectionManager AddConnection(Package package, string type, string connectionString)

{

ConnectionManager manager = package.Connections.Add(type);

manager.ConnectionString = connectionString;

manager.Name = String.Format("{0} Connection", type);

return manager;

}

Call it like this -

Code Snippet

Package package = new Package();

// Add the Excel connection

ConnectionManager excelConnection = AddExcelConnection(package, @."C:\Temp\Export.xls");

Is your destination code correct? I the properties look wrong, I always used -

componentInstance.SetComponentProperty("AccessMode", 2);

componentInstance.SetComponentProperty("SqlCommand", query);

|||

Thanks for the info, Darren. So, now I have the following code, but how do I specify the destination table (sheet) name?

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

|||

Code Snippet

componentInstance.SetComponentProperty("AccessMode", 0);

componentInstance.SetComponentProperty("OpenRowset", sheetName);

I was wrong above, it is OpenRowset for teh table/sheet not SqlCommand.

|||

Thanks for the reply, Darren. The first SetComponentProperty worked, but not the second, which gets a runtime exception. Is the property under a different name? Thanks for your help!

' Create and configure an OLE DB destination.

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

conDest.Name = "Excel File"

conDest.Description = "Excel File"

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' The ProvideComponentProperties method creates a default input.

destDesignTime.ProvideComponentProperties()

destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)

destDesignTime.SetComponentProperty("AccessMode", 0)

'runtime Exception here

destDesignTime.SetComponentProperty("OpenRowSet", "functions")

|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2175638&SiteID=1

How to create an excel connection manager programmatically ?

I am following the samples in the online books. I've got an OLEDB connection manager and source component defined. I now want to create an Excel connection manager and destination component (see below). How can I do this?

Dim conMgr As ConnectionManager = package.Connections.Add("OLEDB")

conMgr.ConnectionString = "Provider=SQLOLEDB.1;" "Data Source=FLL-EIS;Initial Catalog=DataLoad;" & _

"Integrated Security=SSPI;"

conMgr.Name = "SSIS Connection Manager for OLE DB"

conMgr.Description = "OLE DB connection to FLL-EIS."

' Create and configure an OLE DB source component.

Dim source As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

source.ComponentClassID = "DTSAdapter.OleDbSource"

' Create the design-time instance of the source.

Dim srcDesignTime As CManagedComponentWrapper = source.Instantiate

srcDesignTime.ProvideComponentProperties()

' Assign the connection manager.

source.RuntimeConnectionCollection(0).ConnectionManager = _

DtsConvert.ToConnectionManager90(conMgr)

' Set the custom properties of the source.

srcDesignTime.SetComponentProperty("AccessMode", 2)

srcDesignTime.SetComponentProperty("SqlCommand", Dts.Variables("User::gsQuery").Value.ToString)

' Connect to the data source,

' and then update the metadata for the source.

srcDesignTime.AcquireConnections(Nothing)

srcDesignTime.ReinitializeMetaData()

srcDesignTime.ReleaseConnections()

' Create and configure an OLE DB destination. (This is where I need help, as this code does not work)

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' Where can I find documentation about the various ComponentClassIds and Properties?

destDesignTime.ProvideComponentProperties()

destDesignTime.SetComponentProperty("ExcelFilePath", Dts.Variables("User::gsExcelFile").Value.ToString)

destDesignTime.SetComponentProperty("TableName", Dts.Variables("User::gsSheetName").Value.ToString)

Here are a couple of wrapper functions I have for creating connections, including an Excel connection-

Code Snippet

private static ConnectionManager AddExcelConnection(Package package, string filename)

{

return AddConnection(package, "EXCEL", String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\"", filename));

}

private static ConnectionManager AddSqlConnection(Package package, string server, string database)

{

return AddConnection(package, "OLEDB", String.Format("Provider=SQLOLEDB.1;Data Source={0};Persist Security Info=False;Initial Catalog={1};Integrated Security=SSPI;", server, database));

}

private static ConnectionManager AddConnection(Package package, string type, string connectionString)

{

ConnectionManager manager = package.Connections.Add(type);

manager.ConnectionString = connectionString;

manager.Name = String.Format("{0} Connection", type);

return manager;

}

Call it like this -

Code Snippet

Package package = new Package();

// Add the Excel connection

ConnectionManager excelConnection = AddExcelConnection(package, @."C:\Temp\Export.xls");

Is your destination code correct? I the properties look wrong, I always used -

componentInstance.SetComponentProperty("AccessMode", 2);

componentInstance.SetComponentProperty("SqlCommand", query);

|||

Thanks for the info, Darren. So, now I have the following code, but how do I specify the destination table (sheet) name?

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

|||

Code Snippet

componentInstance.SetComponentProperty("AccessMode", 0);

componentInstance.SetComponentProperty("OpenRowset", sheetName);

I was wrong above, it is OpenRowset for teh table/sheet not SqlCommand.

|||

Thanks for the reply, Darren. The first SetComponentProperty worked, but not the second, which gets a runtime exception. Is the property under a different name? Thanks for your help!

' Create and configure an OLE DB destination.

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

conDest.Name = "Excel File"

conDest.Description = "Excel File"

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' The ProvideComponentProperties method creates a default input.

destDesignTime.ProvideComponentProperties()

destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)

destDesignTime.SetComponentProperty("AccessMode", 0)

'runtime Exception here

destDesignTime.SetComponentProperty("OpenRowSet", "functions")

|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2175638&SiteID=1sql

how to create a role in AS 2005 with AMO programmatically

hi All,

I am working on some migration project.it is from AS 2000 to AS 2005.

I have to create a role in AS 2005.Is this one is similer functionality like in AS 2000.

Plz let me know.

I am using AMO for making connection i can able to connect and now i have to create a role,In my previous development we used to create a role in database then after in cube with the help of DSO.Could you please help on this.

if possible if you have any sample to create a role in AS 2005 with AMO please send to me.

Thanks in Advance.

vishu

Hi,

There is a similar discussion about creating roles with AMO in this thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1795829&SiteID=1

Adrian Dumitrascu

|||

Hi Adrian,

Thanks for u r quick response,I gone through the link and i checked with the code but it is throwing error finding the database it self.

could u please tell me what could be the problem.because i checked with the same code which u provided in the Post.

Thanks in advance.

|||

If you get an exception in this line:

Database db = s.Databases["the database id"];

then you need to specify the ID of your database instead of the string "the database id". To get the ID of your database, use SQL Management Studio to see the database properties (right click on the Database -> Properties).

Or, if you know the Name of the database, but not the ID, you can use the GetByName method:

Database db = s.Databases.GetByName["the Name of your database"];

Adrian Dumitrascu

|||

Hi

I used below code.FYI.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.AnalysisServices

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()
'
'Connect to SSAS Instance
'--
Dim MyServer As New Server
Dim MyConnectionString As String = "localhost"
MyServer.Connect(MyConnectionString)

'Get Reference to Database
'--
Dim MyDatabase As Database = MyServer.Databases.GetByName("Adventure Works DW")

'Get Reference to Role
'--
Dim MyRole As New Role = MyDatabase.Roles.GetByName("My Role")

'Add Users
'--
Dim MyRoleMember As New RoleMember("TestDomain\TestUser1")
MyRole.Members.Add(MyRoleMember)
MyRole.Update()

'--
MyDatabase.Update() 'This may be optional
MyServer.Disconnect()

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

In above code am getting the error in Dim MyDatabase As Database = MyServer.Databases.GetByName("Adventure Works DW") this line.

is the problem with above code or anything i missed out please let me know.

Thanks in advance.

Regards

vishu.

|||

Is the code running under an account that has permissions to read the "Adventure Works DW" database ? Try to temporary add the account as a server admin or as a database admin (you can do that with SQL Management Studio, right click on the server item -> Properties -> Security tab).

If this was the problem (lack of read permissions) you will need to setup a database role for that account, with read metadata and also write permissions.

Adrian Dumitrascu

|||

Hi

Thanks for the reply,i tried with what you said.

But there i can add only my NT Id only i cant see the permissions for read metadata.

still am getting the same problem.

|||

To temporary add the user as a server administrator, you only need to add it as a member in the Security tab of the server properties dialog in SQL Management Studio.

If that doesn't fix the problem, we need to double check 2 things:

1. make sure your code connects to the server you want

2. run SQL Management Studio under the account you run the code ('Run As' works), connect to the server from #1, right click on the Adventure Works DW database -> Properties and verify that the ID of the database is the one you use in the code

If in step #2 you do not see the Adventure Works database, we need to see if it's a permissions problem (although it should not be since you added the user as server admin) or you don't have the database on that server (connect with other users or check the data folder from "%ProgramFiles%\Microsoft SQL Server").

Adrian Dumitrascu

how to create a role in AS 2005 with AMO programmatically

hi All,

I am working on some migration project.it is from AS 2000 to AS 2005.

I have to create a role in AS 2005.Is this one is similer functionality like in AS 2000.

Plz let me know.

I am using AMO for making connection i can able to connect and now i have to create a role,In my previous development we used to create a role in database then after in cube with the help of DSO.Could you please help on this.

if possible if you have any sample to create a role in AS 2005 with AMO please send to me.

Thanks in Advance.

vishu

Hi,

There is a similar discussion about creating roles with AMO in this thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1795829&SiteID=1

Adrian Dumitrascu

|||

Hi Adrian,

Thanks for u r quick response,I gone through the link and i checked with the code but it is throwing error finding the database it self.

could u please tell me what could be the problem.because i checked with the same code which u provided in the Post.

Thanks in advance.

|||

If you get an exception in this line:

Database db = s.Databases["the database id"];

then you need to specify the ID of your database instead of the string "the database id". To get the ID of your database, use SQL Management Studio to see the database properties (right click on the Database -> Properties).

Or, if you know the Name of the database, but not the ID, you can use the GetByName method:

Database db = s.Databases.GetByName["the Name of your database"];

Adrian Dumitrascu

|||

Hi

I used below code.FYI.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.AnalysisServices

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()
'
'Connect to SSAS Instance
'--
Dim MyServer As New Server
Dim MyConnectionString As String = "localhost"
MyServer.Connect(MyConnectionString)

'Get Reference to Database
'--
Dim MyDatabase As Database = MyServer.Databases.GetByName("Adventure Works DW")

'Get Reference to Role
'--
Dim MyRole As New Role = MyDatabase.Roles.GetByName("My Role")

'Add Users
'--
Dim MyRoleMember As New RoleMember("TestDomain\TestUser1")
MyRole.Members.Add(MyRoleMember)
MyRole.Update()

'--
MyDatabase.Update() 'This may be optional
MyServer.Disconnect()

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

In above code am getting the error in Dim MyDatabase As Database = MyServer.Databases.GetByName("Adventure Works DW") this line.

is the problem with above code or anything i missed out please let me know.

Thanks in advance.

Regards

vishu.

|||

Is the code running under an account that has permissions to read the "Adventure Works DW" database ? Try to temporary add the account as a server admin or as a database admin (you can do that with SQL Management Studio, right click on the server item -> Properties -> Security tab).

If this was the problem (lack of read permissions) you will need to setup a database role for that account, with read metadata and also write permissions.

Adrian Dumitrascu

|||

Hi

Thanks for the reply,i tried with what you said.

But there i can add only my NT Id only i cant see the permissions for read metadata.

still am getting the same problem.

|||

To temporary add the user as a server administrator, you only need to add it as a member in the Security tab of the server properties dialog in SQL Management Studio.

If that doesn't fix the problem, we need to double check 2 things:

1. make sure your code connects to the server you want

2. run SQL Management Studio under the account you run the code ('Run As' works), connect to the server from #1, right click on the Adventure Works DW database -> Properties and verify that the ID of the database is the one you use in the code

If in step #2 you do not see the Adventure Works database, we need to see if it's a permissions problem (although it should not be since you added the user as server admin) or you don't have the database on that server (connect with other users or check the data folder from "%ProgramFiles%\Microsoft SQL Server").

Adrian Dumitrascu

How to count the tasks inside a package programmatically?

Hi there,

I'm trying to keep track of the ETL process inserting/updating a row in one table for each package that finish in my ETL process when executing. So far, I created a Script task that increments by one a variable (counter) and then open a connection to my database an insert/update my table. What I want to see is Step 1/30, Step 2/30 and so on. Right know I can display Step 1, Step 2 but how can I get the overall number of tasks within a package?

Thanks in advance for any help you can give me.

Sincerely,
Ricardo

A script task cannot interrogate the package in which it resides to find out that information. Unlike in DTS, tasks do not receive a pointer to package itself. So you can't do what you used to do in DTS i.e. Get a reference to DTSGlobalVariables.Parent and iterate over the object model.

This is one of the big differences in SSIS.

Some extra reading:

But it used to work in DTS (1) - Modifing a Package in script

(http://blogs.conchango.com/jamiethomson/archive/2006/06/28/SSIS_3A00_-But-it-used-to-work-in-DTS-_2800_1_2900_-_2D00_-Modifing-a-Package-in-script.aspx)

-Jamie

|||

You could handle the counting yourself - store one variable with the total count of steps, and another variable that you increment with script tasks as the package executes.

It's a little more work, but it should accomplish what you want.

|||

jwelch wrote:

You could handle the counting yourself - store one variable with the total count of steps, and another variable that you increment with script tasks as the package executes.

It's a little more work, but it should accomplish what you want.

Good idea. Thought you'll probably want to put the script task that increments the value into the OnPostExecute eventhandler scoped to the package.

-Jamie

|||Well, to get the count what I ended up doing is to call the package itself from a Script Task so I can do package.Executables.Count in order to get the total number of tasks inside a package.

I still used the Post-Execute event to increment my counter and to insert/update a row in my audit table.

Thank you guys for the suggestions.

Ricardo|||

bachocha wrote:

Well, to get the count what I ended up doing is to call the package itself from a Script Task so I can do package.Executables.Count in order to get the total number of tasks inside a package.

I still used the Post-Execute event to increment my counter and to insert/update a row in my audit table.

Thank you guys for the suggestions.

Ricardo

So you're building a package solely for the purpose of calling another package? You could of course write a .Net app to do this for you - I find it interesting that you chose not to and instead use SSIS itself as the host for your .Net code.

Anyway...I'm waffling. Glad you got what you wanted.

-Jamie

|||Actually, I think he's calling a new instance of the package from inside the same package. Seems like a lot of overhead, but to each his own.|||Yes, John is right. I'm loading the same package from inside my package using a Script Task. I was thinking to create a new package which only loads the package I want to get the number of tasks and then store this count into a variable.

I don't think I could use a .NET app, I mean I can build it but probably I will not be able to deploy it in production. I can only move dtsx packages. I really would like to implement the best solution for this problem. So if you think that loading the same pacakge from itself is an overhead, what would be the right way?

Thanks!

Ricardo|||

bachocha wrote:

Yes, John is right. I'm loading the same package from inside my package using a Script Task. I was thinking to create a new package which only loads the package I want to get the number of tasks and then store this count into a variable.

I don't think I could use a .NET app, I mean I can build it but probably I will not be able to deploy it in production. I can only move dtsx packages. I really would like to implement the best solution for this problem. So if you think that loading the same pacakge from itself is an overhead, what would be the right way?

Thanks!

Ricardo

Its not an overhead if it doesn't cause any problems. It seems like a strange thing to do but if it works for you - go for it.

-Jamie

|||

bachocha wrote:

So if you think that loading the same pacakge from itself is an overhead, what would be the right way?

"Right way" - I don't know. My comment about overhead was that you are essentially loading the package twice - once to do the real work, once to count the executables. Not a big deal if you are doing it in one or two packages. Across a few dozen, it might have more impact. However, just loading the package doesn't have the same overhead as executing it, so maybe it's not a problem. Like Jamie said, if it works for you, go with it.