How to Create Assembly function using dll files in SQL Server 2005?

Hiiiiiiii all

I have to make a user defined function in c# as the class liberary and create a dll file, now i want to use this function in SQL Server 2005 as a part of CLR Integration

I have tried like this

CREATE ASSEMBLY abc

FROM 'C:\abc.dll'

WITH PERMISSION_SET = SAFE

but it gives me

incorrect syntax error

so plzzzzz anyone help me wht to do in my probbbbbbbbb?

Pratik Kansara

What is the exact error-message, and are you sure you are doing it on the SQL 2005 instance (I seem to remember you have both a SQL 2000 as well as 2005 installed)?

Niels
|||
Hiii

yes i have both sql server 2000 and sql server 2005 and .net 2003 and .net 2005 both are installed

Actually there is no any type of group by concate function in sql server 2000- 2005 so tht i have made a class file in c# 2.0
and make a dll file of tht class file

and now sql server 2005 is supporting CLR Integration so tht i want to use tht function in my SQL Server 2005 instead of group by concate, I have already enabled clr enable in my sql server 2005.

but as i think i need to create a assembly function in sql server like

CREATE ASSEMBLY abc
From "c:\abc.dll"

So this statement gives me error so if you have any other option to do this then plz plz help me

thanking youuuuuuuuuuuuuuu...

Pratik Kansara
|||Yes, I understand your problem from your first post. So once again:
1. what is the exact error message you are getting
2. are you sure you are running the CREATE ASSEMBLY statement on the SQL 2005 instance.

The reason I'm asking no 2, is becuase your statement looks OK to me - and the only thing I can think of is that you try to run it on the SQL 2000 instance.

Niels
|||

Hiiiiiii

the exactly error message and syntax is like tht

CREATE ASSEMBLY abc

FROM 'C:\abc.dll'

GO

and the error message i m getting is:

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'ASSEMBLY'.

and i have also tried in sql server 2000 instance but as far as i know tht CLR Integration in SQL Server 2000 is not possible.

plz plz give me solution, i need it urgently...

Thnxxxxxxx

Pratik Kansara

|||

Hiiiii

the exact code tht i m writing is and error message is as bellow...

CREATE ASSEMBLY abc

FROM 'C:\abc.dll'

GO

and the error message is :

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'ASSEMBLY'.

and i have also tried in SQL Server 2000 instance also but as far as i know tht this is not possible in SQL Server 2000 because SQL Server 2000 does not support CLR Integration

so plzz plzzzz help me i need it urgently

Thnxxxxxxxxx so muchhh

Pratik

|||You are right, CLR integration is not supported in SQL 2000, and that is why I believe that when you run your CREATE ASSEMBLY code that you are logged into the SQL 2000 system, and not SQL 2005.

Couple of more questions:

1. Afe you absolutely sure you are running the statement in the SQL 2005 instance? What is the result from following query:
SELECT @.@.version
2. What tool are you using when you run the CREATE ASSEMBLY statement, Query Analyzer or SQL Server Management Studio? You should be using Management Studio.
3. What database do you try to do this in - is it a new database or?

Niels

|||

Respected Sir,

First of all thanks a lot for replying my mail

Sir, Recently i checked a blog and i going to knew tht there is a compitibility problem in my Sql Server tht u have mension

i have a database name Job which i have made in SQL Server 2005 now while chacking the compitibility using following syntax:

sp_dbcmptlevel Job

it gives me

The current compatibility level is 80.

means tht its a comitibility of SQL Server 2000 and also when i checked the property of my Database then in Option tag i found compatibility Lavel to Microsoft SQL Server 2000(80) and i m not getting here level SQL Server 2005(90)

and after executing the syntax

SELECT @.@.version

i got the message

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

and sir I m using Microsoft SQL Server Managment Studio tool for CREATE ASSAMBLY statement

so sir the problem as i think we got is compitibility lavel so sir i have created this database in SQL Server 2005 also

then why i m not getting here compatibility lavel 90 or SQL Server 2005

and sir another thing is tht when i m executing the statement

sp_dbcmptlevel Job, 90

then it gives me following error:

Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 92

Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]

Valid values of database compatibility level are 60, 65, 70, or 80.

thnks a lot and plz plz send me this solution.......

once again thnks

|||

Pratik Kansara wrote:

and after executing the syntax

SELECT @.@.version

i got the message

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

and sir I m using Microsoft SQL Server Managment Studio tool for CREATE ASSAMBLY statement

so sir the problem as i think we got is compitibility lavel so sir i have created this database in SQL Server 2005 also

then why i m not getting here compatibility lavel 90 or SQL Server 2005

The problem is NOT compatibility levels, if you from SELECT @.@.version are getting as per above SQL Server 2000 - 8.xx then you are NOT working against SQL 2005, you are working against SQL 2000.

Make sure you are connecting to the SQL 2005 instance from Management Studio, as Management Studio allows you to connect both to SQL 2000 as well as SQL 2005.

Niels
|||

Respected Sir

yes i m connected to SQL Server 2000 from the SQL Server 2005 Managment Studio but sir i have cheked the configration tools and other option but i cant find out how to connect to 2005

So will you plz help me how to connect to SQL Server 2005 using Managment Studio ?

thnnking you so much

|||

Respected Sir,

I have successfully solved this problem, and created a assambly in SQL Server thnkin you so much for helping me...

|||Glad you got it to work!

Niels
sql

How to Create Assembly function using dll files in SQL Server 2005?

Hiiiiiiii all

I have to make a user defined function in c# as the class liberary and create a dll file, now i want to use this function in SQL Server 2005 as a part of CLR Integration

I have tried like this

CREATE ASSEMBLY abc

FROM 'C:\abc.dll'

WITH PERMISSION_SET = SAFE

but it gives me

incorrect syntax error

so plzzzzz anyone help me wht to do in my probbbbbbbbb?

Pratik Kansara

What is the exact error-message, and are you sure you are doing it on the SQL 2005 instance (I seem to remember you have both a SQL 2000 as well as 2005 installed)?

Niels
|||
Hiii

yes i have both sql server 2000 and sql server 2005 and .net 2003 and .net 2005 both are installed

Actually there is no any type of group by concate function in sql server 2000- 2005 so tht i have made a class file in c# 2.0
and make a dll file of tht class file

and now sql server 2005 is supporting CLR Integration so tht i want to use tht function in my SQL Server 2005 instead of group by concate, I have already enabled clr enable in my sql server 2005.

but as i think i need to create a assembly function in sql server like

CREATE ASSEMBLY abc
From "c:\abc.dll"

So this statement gives me error so if you have any other option to do this then plz plz help me

thanking youuuuuuuuuuuuuuu...

Pratik Kansara
|||Yes, I understand your problem from your first post. So once again:
1. what is the exact error message you are getting
2. are you sure you are running the CREATE ASSEMBLY statement on the SQL 2005 instance.

The reason I'm asking no 2, is becuase your statement looks OK to me - and the only thing I can think of is that you try to run it on the SQL 2000 instance.

Niels
|||

Hiiiiiii

the exactly error message and syntax is like tht

CREATE ASSEMBLY abc

FROM 'C:\abc.dll'

GO

and the error message i m getting is:

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'ASSEMBLY'.

and i have also tried in sql server 2000 instance but as far as i know tht CLR Integration in SQL Server 2000 is not possible.

plz plz give me solution, i need it urgently...

Thnxxxxxxx

Pratik Kansara

|||

Hiiiii

the exact code tht i m writing is and error message is as bellow...

CREATE ASSEMBLY abc

FROM 'C:\abc.dll'

GO

and the error message is :

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'ASSEMBLY'.

and i have also tried in SQL Server 2000 instance also but as far as i know tht this is not possible in SQL Server 2000 because SQL Server 2000 does not support CLR Integration

so plzz plzzzz help me i need it urgently

Thnxxxxxxxxx so muchhh

Pratik

|||You are right, CLR integration is not supported in SQL 2000, and that is why I believe that when you run your CREATE ASSEMBLY code that you are logged into the SQL 2000 system, and not SQL 2005.

Couple of more questions:

1. Afe you absolutely sure you are running the statement in the SQL 2005 instance? What is the result from following query:
SELECT @.@.version
2. What tool are you using when you run the CREATE ASSEMBLY statement, Query Analyzer or SQL Server Management Studio? You should be using Management Studio.
3. What database do you try to do this in - is it a new database or?

Niels

|||

Respected Sir,

First of all thanks a lot for replying my mail

Sir, Recently i checked a blog and i going to knew tht there is a compitibility problem in my Sql Server tht u have mension

i have a database name Job which i have made in SQL Server 2005 now while chacking the compitibility using following syntax:

sp_dbcmptlevel Job

it gives me

The current compatibility level is 80.

means tht its a comitibility of SQL Server 2000 and also when i checked the property of my Database then in Option tag i found compatibility Lavel to Microsoft SQL Server 2000(80) and i m not getting here level SQL Server 2005(90)

and after executing the syntax

SELECT @.@.version

i got the message

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

and sir I m using Microsoft SQL Server Managment Studio tool for CREATE ASSAMBLY statement

so sir the problem as i think we got is compitibility lavel so sir i have created this database in SQL Server 2005 also

then why i m not getting here compatibility lavel 90 or SQL Server 2005

and sir another thing is tht when i m executing the statement

sp_dbcmptlevel Job, 90

then it gives me following error:

Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 92

Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]

Valid values of database compatibility level are 60, 65, 70, or 80.

thnks a lot and plz plz send me this solution.......

once again thnks

|||

Pratik Kansara wrote:

and after executing the syntax

SELECT @.@.version

i got the message

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

and sir I m using Microsoft SQL Server Managment Studio tool for CREATE ASSAMBLY statement

so sir the problem as i think we got is compitibility lavel so sir i have created this database in SQL Server 2005 also

then why i m not getting here compatibility lavel 90 or SQL Server 2005

The problem is NOT compatibility levels, if you from SELECT @.@.version are getting as per above SQL Server 2000 - 8.xx then you are NOT working against SQL 2005, you are working against SQL 2000.

Make sure you are connecting to the SQL 2005 instance from Management Studio, as Management Studio allows you to connect both to SQL 2000 as well as SQL 2005.

Niels
|||

Respected Sir

yes i m connected to SQL Server 2000 from the SQL Server 2005 Managment Studio but sir i have cheked the configration tools and other option but i cant find out how to connect to 2005

So will you plz help me how to connect to SQL Server 2005 using Managment Studio ?

thnnking you so much

|||

Respected Sir,

I have successfully solved this problem, and created a assambly in SQL Server thnkin you so much for helping me...

|||Glad you got it to work!

Niels

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 column names using results of query?

I need to generate a report where the column names are defined by the result
s
of the first query and the data by the second query.
The closest I can get is to create a stored procedure that creates a temp
table with named columns fill it with a single row (query 1) and then fill i
t
with data. I then query the table for my results to pass on to the
application dropping the table when done. My issue with this method is that
App I am passing data to shows the temp column names as well (no way to
suppress them).
Over simplified pseudo code:
CREATE TABLE ##c (
ordr int,
Col1 varchar(30),
Col2 varchar(30)
)
INSERT INTO ##c
SELECT ‘1’ AS [ordr], h.header1, h.header2
FROM dbo.configure h
WHERE h.id = ‘105’
INSERT INTO ##c
SELECT ‘2’ AS [ordr], d.data1, d.data2
FROM dbo.detail d
WHERE d.id = ‘105’
ORDER by d.data1
SELECT col1, col2
FROM ##c
ORDER BY ordr, col1, col2
DROP TABLE ##cYou could generate a SELECT string you need with column names from the first
query and rename them use the AS keyword.
Then you can use EXECUTE (T-SQL command) to execute it.
Search the BOL for "Using EXECUTE with a Character String"
Milan
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:B43F7817-1C89-44AF-A4EE-15FCC0F17B52@.microsoft.com...
>I need to generate a report where the column names are defined by the
>results
> of the first query and the data by the second query.
> The closest I can get is to create a stored procedure that creates a temp
> table with named columns fill it with a single row (query 1) and then fill
> it
> with data. I then query the table for my results to pass on to the
> application dropping the table when done. My issue with this method is
> that
> App I am passing data to shows the temp column names as well (no way to
> suppress them).
> Over simplified pseudo code:
> CREATE TABLE ##c (
> ordr int,
> Col1 varchar(30),
> Col2 varchar(30)
> )
> INSERT INTO ##c
> SELECT '1' AS [ordr], h.header1, h.header2
> FROM dbo.configure h
> WHERE h.id = '105'
> INSERT INTO ##c
> SELECT '2' AS [ordr], d.data1, d.data2
> FROM dbo.detail d
> WHERE d.id = '105'
> ORDER by d.data1
> SELECT col1, col2
> FROM ##c
> ORDER BY ordr, col1, col2
> DROP TABLE ##c
>|||You can do something like the following:
IF OBJECT_ID (N'teble1') IS NOT NULL
DROP FUNCTION table1
GO
select * into table1 from Query1 where 0 = 1
insert into table1 select * from Query2
"Dan" <Dan@.discussions.microsoft.com> wrote in message news:B43F7817-1C89-44AF-A4EE-15FCC0F
17B52@.microsoft.com...
>I need to generate a report where the column names are defined by the resul
ts
> of the first query and the data by the second query.
> The closest I can get is to create a stored procedure that creates a temp
> table with named columns fill it with a single row (query 1) and then fill
it
> with data. I then query the table for my results to pass on to the
> application dropping the table when done. My issue with this method is tha
t
> App I am passing data to shows the temp column names as well (no way to
> suppress them).
> Over simplified pseudo code:
> CREATE TABLE ##c (
> ordr int,
> Col1 varchar(30),
> Col2 varchar(30)
> )
> INSERT INTO ##c
> SELECT '1' AS [ordr], h.header1, h.header2
> FROM dbo.configure h
> WHERE h.id = '105'
> INSERT INTO ##c
> SELECT '2' AS [ordr], d.data1, d.data2
> FROM dbo.detail d
> WHERE d.id = '105'
> ORDER by d.data1
> SELECT col1, col2
> FROM ##c
> ORDER BY ordr, col1, col2
> DROP TABLE ##c
>|||Do you mean:
Declare @.c1 as varchar(30)
Set @.c1 = Select col1 from table1
Select newCol1 AS @.c1 from table2
if so I can not get that to work
"Milan Kosanovic" wrote:

> You could generate a SELECT string you need with column names from the fir
st
> query and rename them use the AS keyword.
> Then you can use EXECUTE (T-SQL command) to execute it.
> Search the BOL for "Using EXECUTE with a Character String"
> Milan
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:B43F7817-1C89-44AF-A4EE-15FCC0F17B52@.microsoft.com...
>
>|||I am missing somthing hear as well:
I can not get my query clauses to run within the framework laid out.
"fish" wrote:

> You can do something like the following:
> IF OBJECT_ID (N'teble1') IS NOT NULL
> DROP FUNCTION table1
> GO
> select * into table1 from Query1 where 0 = 1
> insert into table1 select * from Query2
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message news:B43F7817-1C89-
44AF-A4EE-15FCC0F17B52@.microsoft.com...
>
>|||Have you ever considered being a good programmer who does his reports
in the front end instead of writing kludges in the database? This is
the whole idea of a tiered architecture.
Have you ever thought about writing declarative code instead mimicking
the steps of a procedural program? You are even trying to name a table
the way we used to name a tape on a mag tape system.|||> Have you ever considered being a good programmer who does his reports
> in the front end instead of writing kludges in the database? This is
> the whole idea of a tiered architecture.
IMHO report writing is a simple and boring task for 95% of time. IMHO
using a good programmer for report writing is an overkill - in my
recent experience a high scholl senior quickly mastered Crystal Reports
and
did all that booooooooring label aligning pretty well, and for a cheap
price. Overall it was a faster and cheaper approach as opposed to the
traditional one...|||Requestor
Character string
Trust me this is not my preferred method but the client’s dictated report
application and the predefined legacy applications db structure are off
limits to me. And I have this one obstacle to getting sign off. My boundarie
s
are that the I can pass a string to the db and the grid returned is what the
user see’s
I have figured out that I can
declare @.col1 as Varchar(15)
declare @.sql as varchar(755)
set @.col1 = (select some stuff)
Set @.sql = 'create table#t ([' + @.col1 + ] varchar(30))'
exec (@.sql)
not great but it gets me out the door within the clients constraints
"Alexander Kuznetsov" wrote:

> IMHO report writing is a simple and boring task for 95% of time. IMHO
> using a good programmer for report writing is an overkill - in my
> recent experience a high scholl senior quickly mastered Crystal Reports
> and
> did all that booooooooring label aligning pretty well, and for a cheap
> price. Overall it was a faster and cheaper approach as opposed to the
> traditional one...
>

how to count with restrictions

Hello Gents,
need your help to write a formula for counting records with defined calue in the fileld, e.g. how many records with value "123456" in field {table.field}?
thank you very much for your help!U can.

Create a running total field. Add the field which you want to count. Give "Count" as your type of summary.

In Evaluate option,
select "Use a Formula" option. click X-2 box. Write your formula. Never reset this .

u can get the count what u want.

Hope this work.|||thanks a lot, but which will be formula in my case?|||Just simply enter

{table.fieldname} = "123456"|||thanks for prompt reply, but unfortunatelly it always returns zero...|||If it is string field, use trim function, records may be stored with spaces.

Otherwise it should work if the table contains records for that value|||Doesnt help... When i use simply count({fieldname}) - that returns total fine, but with this restriction it doesnt work...|||Sorry for asking this,

Do you have any records which meet ur condition?

I used lots of running total sums with various conditons. working fine.|||Sure, i have :-)
Actually, this field (string type) is a set of 5 kind of different words (total is ~700). I would like to know how many i have words "123456" for example.|||In addition: when i use running total with evaluation "For each record" - it returns 1. When i set type of summary to maximum - then returns only value, not the sum.|||Make sure

i) Included the exact field name in "Fields to Summarize" box
ii) Type of Summary is count

iii) Evaluate:

Check "Use a formula option" and
write the formula in formula workshop (by clicking X-2)
trim({table.fieldname}) = "123456"

iv) Reset:

check "Never" option|||Yes, all of these conditions is true.|||Ok, good

Now where have u placed your running total field?
(in which section)|||In ReportHeader.|||that's the problem.

It's running total. So will be calculated while printing records.

So place in Report Footer. So u'll get the count.|||Thats works! Thank you very much!!!