Exec Stored Procedure

Hi,

Hope someone can help me out here - I'm trying to design a data flow in SSIS which relies in part on a stored procedure as a data source - I'm running it through OLE DB.

Sure Exec MystoredProc works fine on preview and on parsing, but it refuses to acknowledge any of the columns, when I go to Edit-->Columns everything is blank.

Just out of interest - the reason I am using a stored procedure is because I dump the data into a temp table and then amend a couple of the columns to make it the same as my other database (for example where len(field) = 6 then field = '0000' + field).

Possibly I'm better off taking the raw data through the OLE connection and then transforming it through SSIS, but my gut feeling is I should minimise what I'm dumping into SSIS and offload the processing onto the local DB. Any thoughts?

Thanks

Rich

To do exactly what you need - use OLE DB Command Transformation - part of the data flow. Use ? for column parameters.

IMHO: There are two schools that define how to do this: ETL and ELT. What you are trying to do is ETL - the disadvantage of this is if you have a lot of rows - this method will execute the SP as many times - this is performance impact.

In ELT approach you load the data with Bulk Insert and then apply a SQL statement or another set of trasnformations to load the target.

|||

This is helpful Dima, particularly your comments on ETL & ELT, but it doesn't quite hit the spot in terms of what I want to achieve.

Maybe because I'm being thick. My fundamental problem is that my OLE DB connector will not return any column names. When I add the OLE DB transform it just whinges that I don't have any column names.

As I see it your solution will allow me to connect two tables through OLE DB connectors, drag them into OLEDB transform, do my changes, and then output columns - are you saying that there is no way for SSIS to derive column names from a stored procedure without iterating through the recordset one record at a time? This sounds totally useless, but completely feasible.....

As I have already done the work I really just want to get the stored procedure results into SSIS via my OLE DB source, if you are saying I have to start again then I will, but in truth if using this technology means I have to rewrite all my existing SP code as data flows then it is about as much use to me as a chocolate teapot, I have years of work wrapped up in stored procedures and also I would prefer to distribute the load, I want as few transformations as possible in SSIS, the primary use for the server is reporting, not data transforms, we have plenty of spare DB capacity across the planet and bandwidth is at a premium so thinking about what you've said I think we are better off restricting the recordset at source.

|||

I feel your pain :)

What is your OLE DB source? a DB or file? If you have weird source - you may want to see if there is newer ODBC or OLE DB driver out there.

For the existing SP's:

If you already have SP's that accept fields and insert them into a table, row by row - you should be able to reuse them. You can map source fields to the OLE DB command parameters.

|||

Hi Dima,

Hmm... Just to let you know I worked round the problem after reading your answers - my stored procedures now delete and create tables, which I then query, not very efficient but hey, it works, and I can schedule them monthly just before my reports need doing. Anyway, it is only MS SQL I have a problem with, all my Oracle stuff is based on views anyway and it works fine (?)

I tried using the ADO.NET source instead but this is even more messed up.

Path of least resistance until Microsoft sort this mess out, I think.

Thanks for your help

Rich

|||I guess you are doing something wrong... You can call a SP in an OLEDB datasource (described here: http://msdn2.microsoft.com/en-us/library/ms141696.aspx). I tried that with Adventureworks using the SP they suggested for testing with parameters (just entered a one as value for each ?). It returned the metadata correctly...|||I've had similar problems where SSIS doesn't correctly identify columns if you have anything more than a vanilla SELECT statement in the s/p. The solution is to do a SET NOCOUNT ON at the start of the s/p. In addition, there seems to be a problem if the s/p uses temporary tables (i.e. #TableName). It works fine if using table variables though.

Greg.

Exec Stored Procedure

Hi,

Hope someone can help me out here - I'm trying to design a data flow in SSIS which relies in part on a stored procedure as a data source - I'm running it through OLE DB.

Sure Exec MystoredProc works fine on preview and on parsing, but it refuses to acknowledge any of the columns, when I go to Edit-->Columns everything is blank.

Just out of interest - the reason I am using a stored procedure is because I dump the data into a temp table and then amend a couple of the columns to make it the same as my other database (for example where len(field) = 6 then field = '0000' + field).

Possibly I'm better off taking the raw data through the OLE connection and then transforming it through SSIS, but my gut feeling is I should minimise what I'm dumping into SSIS and offload the processing onto the local DB. Any thoughts?

Thanks

Rich

To do exactly what you need - use OLE DB Command Transformation - part of the data flow. Use ? for column parameters.

IMHO: There are two schools that define how to do this: ETL and ELT. What you are trying to do is ETL - the disadvantage of this is if you have a lot of rows - this method will execute the SP as many times - this is performance impact.

In ELT approach you load the data with Bulk Insert and then apply a SQL statement or another set of trasnformations to load the target.

|||

This is helpful Dima, particularly your comments on ETL & ELT, but it doesn't quite hit the spot in terms of what I want to achieve.

Maybe because I'm being thick. My fundamental problem is that my OLE DB connector will not return any column names. When I add the OLE DB transform it just whinges that I don't have any column names.

As I see it your solution will allow me to connect two tables through OLE DB connectors, drag them into OLEDB transform, do my changes, and then output columns - are you saying that there is no way for SSIS to derive column names from a stored procedure without iterating through the recordset one record at a time? This sounds totally useless, but completely feasible.....

As I have already done the work I really just want to get the stored procedure results into SSIS via my OLE DB source, if you are saying I have to start again then I will, but in truth if using this technology means I have to rewrite all my existing SP code as data flows then it is about as much use to me as a chocolate teapot, I have years of work wrapped up in stored procedures and also I would prefer to distribute the load, I want as few transformations as possible in SSIS, the primary use for the server is reporting, not data transforms, we have plenty of spare DB capacity across the planet and bandwidth is at a premium so thinking about what you've said I think we are better off restricting the recordset at source.

|||

I feel your pain :)

What is your OLE DB source? a DB or file? If you have weird source - you may want to see if there is newer ODBC or OLE DB driver out there.

For the existing SP's:

If you already have SP's that accept fields and insert them into a table, row by row - you should be able to reuse them. You can map source fields to the OLE DB command parameters.

|||

Hi Dima,

Hmm... Just to let you know I worked round the problem after reading your answers - my stored procedures now delete and create tables, which I then query, not very efficient but hey, it works, and I can schedule them monthly just before my reports need doing. Anyway, it is only MS SQL I have a problem with, all my Oracle stuff is based on views anyway and it works fine (?)

I tried using the ADO.NET source instead but this is even more messed up.

Path of least resistance until Microsoft sort this mess out, I think.

Thanks for your help

Rich

|||I guess you are doing something wrong... You can call a SP in an OLEDB datasource (described here: http://msdn2.microsoft.com/en-us/library/ms141696.aspx). I tried that with Adventureworks using the SP they suggested for testing with parameters (just entered a one as value for each ?). It returned the metadata correctly...|||I've had similar problems where SSIS doesn't correctly identify columns if you have anything more than a vanilla SELECT statement in the s/p. The solution is to do a SET NOCOUNT ON at the start of the s/p. In addition, there seems to be a problem if the s/p uses temporary tables (i.e. #TableName). It works fine if using table variables though.

Greg.

exception_int_divied_by_zero..

Hi all, once I try to change to the design mode of any table, an error
message "Exception_Int_Divided_By_Zero" display, what does this mean? And
alos, when I try to generate SQL script, drop a view, use DTS to export
data, I also receive this error message, any idea how to solve it?
begin 666 Au Yeung.vcf
M0D5'24XZ5D-!4D0-"E9%4E-)3TXZ,BXQ#0I..D%U(%EE=6YG#0I&3CI!=2!9
M975N9PT*14U!24P[4%)%1CM)3E1%4DY%5#IA<G1H=7)A>4!H; W1M86EL+F-O
D;0T*4D56.C(P,#4P-C$S5# W-#@.R-EH-"D5.1#I60T%21 T*
`
end
You mean, you are getting this error from Enterprise Manager? If so, could
you try the same from Enterprise Manager on another machine?
What exactly are you trying to do? You can probably do the same using
scripts from Query Analyzer.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Chung" <chung@.utaxhk.com.hk> wrote in message
news:%238S2Dw%23bFHA.584@.TK2MSFTNGP15.phx.gbl...
Hi all, once I try to change to the design mode of any table, an error
message "Exception_Int_Divided_By_Zero" display, what does this mean? And
alos, when I try to generate SQL script, drop a view, use DTS to export
data, I also receive this error message, any idea how to solve it?
|||I found that this error is associates with the database, not with the
Enterprise Management. Because not all of database has these error, only one
database. And even I backup, and restore it, the error still there. Without
EM, how can I generate the SQL script of the database? and copy data from
the database?
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> glsD:%23HfYeNAcFHA.3184@.TK2MSFTNGP15.phx .gbl...
> You mean, you are getting this error from Enterprise Manager? If so, could
> you try the same from Enterprise Manager on another machine?
> What exactly are you trying to do? You can probably do the same using
> scripts from Query Analyzer.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Chung" <chung@.utaxhk.com.hk> wrote in message
> news:%238S2Dw%23bFHA.584@.TK2MSFTNGP15.phx.gbl...
> Hi all, once I try to change to the design mode of any table, an error
> message "Exception_Int_Divided_By_Zero" display, what does this mean? And
> alos, when I try to generate SQL script, drop a view, use DTS to export
> data, I also receive this error message, any idea how to solve it?
>
>
>
|||Sounds more like either your connection, API, or database environment
settings...like SET NUMERIC_ROUNDABORT or SET ARITHABORT. Check your
database properties.
Another possibility would be that your client tools have been corrupted.
Did you install the Beta 1 for SQL Server 2005? There was an MDAC 9 Beta 1
that was included that caused all sorts of issues with SQL Server 2000
tools. You basically have to do a file replace and registry clean to clear
everything off. A new download of an older MDAC won't fix anything becuase
version 9 is newer, the MDAC will not deploy.
Sincerely,
Anthony Thomas

"Chung" <chung@.utaxhk.com.hk> wrote in message
news:u$cDkLLcFHA.3120@.TK2MSFTNGP12.phx.gbl...
I found that this error is associates with the database, not with the
Enterprise Management. Because not all of database has these error, only one
database. And even I backup, and restore it, the error still there. Without
EM, how can I generate the SQL script of the database? and copy data from
the database?
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com>
glsD:%23HfYeNAcFHA.3184@.TK2MSFTNGP15.phx .gbl...
> You mean, you are getting this error from Enterprise Manager? If so, could
> you try the same from Enterprise Manager on another machine?
> What exactly are you trying to do? You can probably do the same using
> scripts from Query Analyzer.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Chung" <chung@.utaxhk.com.hk> wrote in message
> news:%238S2Dw%23bFHA.584@.TK2MSFTNGP15.phx.gbl...
> Hi all, once I try to change to the design mode of any table, an error
> message "Exception_Int_Divided_By_Zero" display, what does this mean? And
> alos, when I try to generate SQL script, drop a view, use DTS to export
> data, I also receive this error message, any idea how to solve it?
>
>
>

exception_int_divied_by_zero..

Hi all, once I try to change to the design mode of any table, an error
message "Exception_Int_Divided_By_Zero" display, what does this mean? And
alos, when I try to generate SQL script, drop a view, use DTS to export
data, I also receive this error message, any idea how to solve it?
begin 666 Au Yeung.vcf
M0D5'24XZ5D-!4D0-"E9%4E-)3TXZ,BXQ#0I..D%U(%EE=6YG#0I&3CI!=2!9
M975N9PT*14U!24P[4%)%1CM)3E1%4DY%5#IA<G1H=7)A>4!H;W1M86EL+F-O
D;0T*4D56.C(P,#4P-C$S5# W-#@.R-EH-"D5.1#I60T%21 T*
`
endYou mean, you are getting this error from Enterprise Manager? If so, could
you try the same from Enterprise Manager on another machine?
What exactly are you trying to do? You can probably do the same using
scripts from Query Analyzer.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Chung" <chung@.utaxhk.com.hk> wrote in message
news:%238S2Dw%23bFHA.584@.TK2MSFTNGP15.phx.gbl...
Hi all, once I try to change to the design mode of any table, an error
message "Exception_Int_Divided_By_Zero" display, what does this mean? And
alos, when I try to generate SQL script, drop a view, use DTS to export
data, I also receive this error message, any idea how to solve it?|||I found that this error is associates with the database, not with the
Enterprise Management. Because not all of database has these error, only one
database. And even I backup, and restore it, the error still there. Without
EM, how can I generate the SQL script of the database? and copy data from
the database?
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> ¼¶¼g©ó¶l¥ó·s»D:%23HfYeNAcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> You mean, you are getting this error from Enterprise Manager? If so, could
> you try the same from Enterprise Manager on another machine?
> What exactly are you trying to do? You can probably do the same using
> scripts from Query Analyzer.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Chung" <chung@.utaxhk.com.hk> wrote in message
> news:%238S2Dw%23bFHA.584@.TK2MSFTNGP15.phx.gbl...
> Hi all, once I try to change to the design mode of any table, an error
> message "Exception_Int_Divided_By_Zero" display, what does this mean? And
> alos, when I try to generate SQL script, drop a view, use DTS to export
> data, I also receive this error message, any idea how to solve it?
>
>
>|||Sounds more like either your connection, API, or database environment
settings...like SET NUMERIC_ROUNDABORT or SET ARITHABORT. Check your
database properties.
Another possibility would be that your client tools have been corrupted.
Did you install the Beta 1 for SQL Server 2005? There was an MDAC 9 Beta 1
that was included that caused all sorts of issues with SQL Server 2000
tools. You basically have to do a file replace and registry clean to clear
everything off. A new download of an older MDAC won't fix anything becuase
version 9 is newer, the MDAC will not deploy.
Sincerely,
Anthony Thomas
"Chung" <chung@.utaxhk.com.hk> wrote in message
news:u$cDkLLcFHA.3120@.TK2MSFTNGP12.phx.gbl...
I found that this error is associates with the database, not with the
Enterprise Management. Because not all of database has these error, only one
database. And even I backup, and restore it, the error still there. Without
EM, how can I generate the SQL script of the database? and copy data from
the database?
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com>
¼¶¼g©ó¶l¥ó·s»D:%23HfYeNAcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> You mean, you are getting this error from Enterprise Manager? If so, could
> you try the same from Enterprise Manager on another machine?
> What exactly are you trying to do? You can probably do the same using
> scripts from Query Analyzer.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Chung" <chung@.utaxhk.com.hk> wrote in message
> news:%238S2Dw%23bFHA.584@.TK2MSFTNGP15.phx.gbl...
> Hi all, once I try to change to the design mode of any table, an error
> message "Exception_Int_Divided_By_Zero" display, what does this mean? And
> alos, when I try to generate SQL script, drop a view, use DTS to export
> data, I also receive this error message, any idea how to solve it?
>
>
>

Exception from HRESULT: 0x80131904

Hi,

I write a custom component (destination component) that handle the error of my dataflow.

The custom component works fine on design time and runtime by using BIDS.

When I'm using the same package that use my custom component with DTEXEC,

I got the following error :

System.Exception: AcquireConnections : Exception from HRESULT: 0x80131904

at SSISGenerator.SSISErrorHandler.ErrorHandlerDestination.AcquireConnections(
Object transaction)

at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnectio
ns(IDTSManagedComponentWrapper90 wrapper, Object transaction)

The error message point out that the problem is in acquireconnections method.

This is the code i'm using in my custom component for the acquireconnections method.

public override void AcquireConnections(object transaction)

{

try

{

if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)

{

ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);

ConnectionManagerAdoNet cmado = cm.InnerObject as ConnectionManagerAdoNet;

if (cmado == null)

throw new Exception(String.Format(MSG_ACQUIRECONNECTIONS_ADONET,cm.Name));

this.sqlConnection = cmado.AcquireConnection(null) as SqlConnection;

if (this.sqlConnection == null)

throw new Exception(String.Format(MSG_ACQUIRECONNECTIONS_ADONET, cm.Name));

if (sqlConnection.State != ConnectionState.Open)

this.sqlConnection.Open();

}

}

catch (Exception e)

{

throw new Exception(MSG_ACQUIRECONNECTIONS + e.Message);

}

}

Does someone got an idea ?

Mathieu

Could you try to debug your method while called from DTExec?

Thanks.

|||

Hi,

I did some tests and I will explain you the result.

Debug the method with DTExec

I debugged with DTEXEC a solution that contain two project

01 - class library project for the custom component

02 - integration service project for a test package

I did the following tasks for the debug proces

01 - On debug tab, I put the information needed for debugging (start an external program to debug (DTEXEC) and the file for the parameter)

02 - I put the debug dll in GAC

03 - I make sure that I have a breakpoint set on PreExecute

04 - I hit F5 and the error didn't show up.

05 - I took the custom component (dll) and I copy it on the dev server.

06 - I put the debug dll in GAC.

07 - I executed the test package on the dev server with DTEXEC and the error didn't show up.

08 - I remove the debug information and I've compiled the custom component in release mode.

09 - I put the release dll in GAC

10 - I executed the test package on my machine with DTEXEC and the error didn't show up.

11 - I took the custom component (dll) and I copy it on the dev server.

12 - I put the release dll in GAC

13 - I executed the test package on the dev server with DTEXEC and the error didn't show up.

After all these tests, I was suprise since the error message disappears. So, I will do other tests and post the result.

For now, I have one idea for this error message. When I did some tests, I was using the same command line over and over. I'm using a environment variable that indicate the location of my dtsconfig file for the connection string of my connection. I'm not sure if I create the environment variable before openning the commandline. I think it can be one of the reason.

We will see.

Mathieu

Excel Pivot

Hi,

I have developed a cube in AS 2005. I am accesing the Cube from Excel. In Cube Design I have grouped the Measures under different folders with 'DisplayFolder' Feature as I have about 100 measures.

When I access the measures from Excel, the folders are not displayed but the entire list of 100 measures. Is there anyway to group the measures together in folders such as sales, assets, profit etc.?

Thanks for the help.

Regards

Josh

Hello! If you are using Excel2003 i do not think that there is a solution.

If you use Excel2007 as a client they will show up in the display folders. Calculated members, however, will have to be mapped to display folders in the calculations properties dialoge. It is a small property button in the calculations tab in the cube editor.

HTH

Thomas Ivarsson

|||

Hi Thomas,

Thanks for your quick reply.

I am using Excel 2002 ;(

I will check with the upgrade and see what happens. BTW, I have couple more questions related to the same.:

- How can I use suppress values=0 (without using Autofilters)

- Is it possible to insert columns or rows in the pivot (which are not measures or dimensions)? The user wants to insert a column to do some calculations.

I would appreciate your reply on the same. Thanks a lot in advance.

Regards

Josh

|||

Hello.

To supress values = 0, I think you can only use FILTER() fór that. NON EMPTY will only suppress nulls.

Mosha have a good post here: http://www.sqljunkies.com/WebLog/mosha/archive/2007/03/07/averages_ratios_division_by_zero_non_empty_behavior.aspx

, that recommend you to aviod zeros in the cube.

You can insert columns and rows only if you do not use the pivot tables(In Excel2007). You will have to convert the pivot table to formulas. It is not possible to have both.

HTH

Thomas Ivarsson