|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Stored Procedures in Integration ServicesI am new to using version 2005, and want to set up an integration service
that takes the results of a stored procedure and exports it to a .csv file. I can set the OLD DB source to use the Stored Procedure as an SQL Command, and the Preview button in the connection manager returns the correct columns and data, however I have not been able to figure out how to get the return columns to show in the columns link, and also the columns do not show when I try to map it in the destination Can you help me on this and show me the best way to do this? Hello,
Thank you for your post in MSDN newsgroup! If I understand the issue correctly, you use a OLE DB source to execute Stored Procedure from a connection manager, and you want to output the result to a CSV file. If I'm off-base, please let me know. Based on my test, you could use Flat file destination and connect the OLE DB source to the flat file destination. You could create a file connection manager from Flat file destination: 1. Right click the Flat file destination->Edit->connectioin Manager, click New, and selet "Delimited" 2. Click Browse, and select, type the file name, and select CSV as file type. 3. On Mapping tab, you shall be able to see the mappings you want. If anything is unclear, or if you have any questions on above steps, please let me know. I look forward to your update. Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Partner Support When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ===================================================== This posting is provided "AS IS" with no warranties, and confers no rights. Thanks.
When I try this, the mappings do not show up. They also do not show up in the OLE DB Source editor. The Preview button returns the correct rows, but no columns show up, even in the OLE DB Source editor. When I try to map the flat file destination, not columns show up there either. Any ideas? Thanks. ""privatenews"" wrote: Show quote > Hello, > > Thank you for your post in MSDN newsgroup! > > If I understand the issue correctly, you use a OLE DB source to execute > Stored Procedure from a connection manager, and you want to output the > result to a CSV file. If I'm off-base, please let me know. > > Based on my test, you could use Flat file destination and connect the OLE > DB source to the flat file destination. You could create a file connection > manager from Flat file destination: > > > 1. Right click the Flat file destination->Edit->connectioin Manager, click > New, and selet "Delimited" > 2. Click Browse, and select, type the file name, and select CSV as file > type. > 3. On Mapping tab, you shall be able to see the mappings you want. > > If anything is unclear, or if you have any questions on above steps, please > let me know. I look forward to your update. > > Best Regards, > > Peter Yang > MCSE2000/2003, MCSA, MCDBA > Microsoft Online Partner Support > > When responding to posts, please "Reply to Group" via your newsreader so > that others may learn and benefit from your issue. > > ===================================================== > > > This posting is provided "AS IS" with no warranties, and confers no rights. > > > Hello,
Thank you for your reply. I'm still not quite sure about "no columns show up". Do you mean the it is "blank"? Will you please capture a screen shot of the Column tab of OLEDB source editor, and Mapping tab of Flat file destination and post it here? Also, please create a new package, add a new OLE DB Source to a different database, and use "Table/View" instead of "SQL Command" as Data Access mode to test. Does the issue still occur? If you use Datereader source instead of OLE DB Source, does the issue persist? I look fowrard to your update. Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Partner Support ===================================================== Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif ications <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at <http://msdn.microsoft.com/subscriptions/support/default.aspx>. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. Hi,
Thanks. There is no problem with the table/view one, that works as expected. It also works as expected if I do a select * from table in the SQL commant text. However, if I use exec stored_proc, then I can see the result in the Preview, but nothing shows up in the Columns. Not sure how to attach a screen print to this.... but the columns tab in the OLE DB Source looks like this: Box with title "Available External Columns", with one column header row with first box empty second box says Name. No rows under it. Then at the bottom there is like a two column spreadsheet, column 1 labeled "External Column", column 2 labeled "Output Column" with no rows. If I go the the Connection Manager tab then Preview, I see 1 header row and3 data rows with about 15 columns with the correct name. Does this clarify the issue? I'll send you screen prints, but don't see a way to do it in this tool. Thanks. Bob ""privatenews"" wrote: Show quote > Hello, > > Thank you for your reply. I'm still not quite sure about "no columns show > up". Do you mean the it is "blank"? Will you please capture a screen shot > of the Column tab of OLEDB source editor, and Mapping tab of Flat file > destination and post it here? > > Also, please create a new package, add a new OLE DB Source to a different > database, and use "Table/View" instead of "SQL Command" as Data Access mode > to test. Does the issue still occur? > > If you use Datereader source instead of OLE DB Source, does the issue > persist? > > I look fowrard to your update. > > Best Regards, > > Peter Yang > MCSE2000/2003, MCSA, MCDBA > Microsoft Online Partner Support > > > ===================================================== > > Get notification to my posts through email? Please refer to > http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif > ications > <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>. > Note: The MSDN Managed Newsgroup support offering is for non-urgent issues > where an initial response from the community or a Microsoft Support > Engineer within 1 business day is acceptable. Please note that each follow > up response may take approximately 2 business days as the support > professional working with you may need further investigation to reach the > most efficient resolution. The offering is not appropriate for situations > that require urgent, real-time or phone-based interactions or complex > project analysis and dump analysis issues. Issues of this nature are best > handled working with a dedicated Microsoft Support Engineer by contacting > Microsoft Customer Support Services (CSS) at > <http://msdn.microsoft.com/subscriptions/support/default.aspx>. > ================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. > > > Some additional data, if I make an sql task, run the stored procedue and
populate a separate table, then use that table as the source, I can get the product that I want (the data into a .csv file), but this seems rather clunky as it leaves two objects to maintain, the stored procedure and the additional table, rather than just the stored procedure. Show quote "Gerhard" wrote: > Hi, > > Thanks. > > There is no problem with the table/view one, that works as expected. > > It also works as expected if I do a select * from table in the SQL commant > text. > > However, if I use exec stored_proc, then I can see the result in the > Preview, but nothing shows up in the Columns. Not sure how to attach a > screen print to this.... but the columns tab in the OLE DB Source looks like > this: > > Box with title "Available External Columns", with one column header row with > first box empty second box says Name. No rows under it. > > Then at the bottom there is like a two column spreadsheet, column 1 labeled > "External Column", column 2 labeled "Output Column" with no rows. > > If I go the the Connection Manager tab then Preview, I see 1 header row and3 > data rows with about 15 columns with the correct name. > > Does this clarify the issue? > > I'll send you screen prints, but don't see a way to do it in this tool. > > Thanks. > > Bob > > > > ""privatenews"" wrote: > > > Hello, > > > > Thank you for your reply. I'm still not quite sure about "no columns show > > up". Do you mean the it is "blank"? Will you please capture a screen shot > > of the Column tab of OLEDB source editor, and Mapping tab of Flat file > > destination and post it here? > > > > Also, please create a new package, add a new OLE DB Source to a different > > database, and use "Table/View" instead of "SQL Command" as Data Access mode > > to test. Does the issue still occur? > > > > If you use Datereader source instead of OLE DB Source, does the issue > > persist? > > > > I look fowrard to your update. > > > > Best Regards, > > > > Peter Yang > > MCSE2000/2003, MCSA, MCDBA > > Microsoft Online Partner Support > > > > > > ===================================================== > > > > Get notification to my posts through email? Please refer to > > http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif > > ications > > <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>. > > Note: The MSDN Managed Newsgroup support offering is for non-urgent issues > > where an initial response from the community or a Microsoft Support > > Engineer within 1 business day is acceptable. Please note that each follow > > up response may take approximately 2 business days as the support > > professional working with you may need further investigation to reach the > > most efficient resolution. The offering is not appropriate for situations > > that require urgent, real-time or phone-based interactions or complex > > project analysis and dump analysis issues. Issues of this nature are best > > handled working with a dedicated Microsoft Support Engineer by contacting > > Microsoft Customer Support Services (CSS) at > > <http://msdn.microsoft.com/subscriptions/support/default.aspx>. > > ================================================== > > This posting is provided "AS IS" with no warranties, and confers no rights. > > > > > > Hello Bob,
Thank you for your update and additional information. Based on my test, I was not able to reproduce the issue. 1. Created a test table create table tb1 ( id int prmiary key, pname varchar(20)) insert tb1 values (1, 'test1') insert tb1 values (2, 'test2') 2. Created a SP create proc testsp (@p1 int, @p2 varchar(20)) as select * from tb1 where id=@p1 or pname like @p2 3. Created a connection manager to the SQL 2005 database, and OLEDB source to use the connection manager. 4. Used the following command for SQL command exec testsp 1, 'test2' 5. On Columns, I could see the proper columns. You may test above steps on your side to see if the issue persists. I suspect the issue is caused by that the SP you use does not return the header information properly. Please test it on different SPs/tables/databases to see if it is specific to this SP or tables behind. For the connection manager, do you use Native oledb\SQL native client ? You may want to send a simple sample SP and backend table schema you use to me at pet***@microsoft.com, and I willl check if the issue could be reproudced on my side. I understand that you'v found a workaround on this issue but this may bring some overhead because you have to maintain objects not required. Please let me know if you have any update and we look forward to your reply. Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Partner Support ===================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ====================================================== Gerhard (acsla@community.nospam) writes:
> Not sure how to attach a screen print to this.... You could always upload the images on a web site and just post theURL:s. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx That works for me, the images are at
http://www.criminonwus.org/ScreenPrints.doc Thanks. Show quote "Erland Sommarskog" wrote: > Gerhard (acsla@community.nospam) writes: > > Not sure how to attach a screen print to this.... > > You could always upload the images on a web site and just post the > URL:s. > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > Do the screen prints help answer my question? I still would like to know
what is up on this. Thanks. Show quote "Erland Sommarskog" wrote: > Gerhard (acsla@community.nospam) writes: > > Not sure how to attach a screen print to this.... > > You could always upload the images on a web site and just post the > URL:s. > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > Gerhard (acsla@community.nospam) writes:
> Do the screen prints help answer my question? I still would like to know Calling Peter Yang, who was assisting you originally. I don't know> what is up on this. Thanks. Integration Services, so I cannot assist with that point. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hello Bob,
Seems that you missed my previous post and I have included below. I look forward to your reply. ============================ Hello Bob, Thank you for your update and additional information. Based on my test, I was not able to reproduce the issue. 1. Created a test table create table tb1 ( id int prmiary key, pname varchar(20)) insert tb1 values (1, 'test1') insert tb1 values (2, 'test2') 2. Created a SP create proc testsp (@p1 int, @p2 varchar(20)) as select * from tb1 where id=@p1 or pname like @p2 3. Created a connection manager to the SQL 2005 database, and OLEDB source to use the connection manager. 4. Used the following command for SQL command exec testsp 1, 'test2' 5. On Columns, I could see the proper columns. You may test above steps on your side to see if the issue persists. I suspect the issue is caused by that the SP you use does not return the header information properly. Please test it on different SPs/tables/databases to see if it is specific to this SP or tables behind. For the connection manager, do you use Native oledb\SQL native client ? You may want to send a simple sample SP and backend table schema you use to me at pet***@microsoft.com, and I willl check if the issue could be reproudced on my side. I understand that you'v found a workaround on this issue but this may bring some overhead because you have to maintain objects not required. Please let me know if you have any update and we look forward to your reply. ================================= Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Partner Support ===================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ====================================================== Did you look at the screen prints I sent? I see no reason why the valuse
should not show up in the columns. ""privatenews"" wrote: Show quote > Hello Bob, > > Seems that you missed my previous post and I have included below. I look > forward to your reply. > > ============================ > Hello Bob, > > Thank you for your update and additional information. > > Based on my test, I was not able to reproduce the issue. > > 1. Created a test table > > create table tb1 ( id int prmiary key, pname varchar(20)) > > insert tb1 values (1, 'test1') > > insert tb1 values (2, 'test2') > > > 2. Created a SP > > create proc testsp (@p1 int, @p2 varchar(20)) > > as > > select * from tb1 where id=@p1 or pname like @p2 > > 3. Created a connection manager to the SQL 2005 database, and OLEDB source > to use the connection manager. > 4. Used the following command for SQL command > > exec testsp 1, 'test2' > > > 5. On Columns, I could see the proper columns. > > You may test above steps on your side to see if the issue persists. I > suspect the issue is caused by that the SP you use does not return the > header information properly. Please test it on different > SPs/tables/databases to see if it is specific to this SP or tables behind. > For the connection manager, do you use Native oledb\SQL native client ? > > You may want to send a simple sample SP and backend table schema you use to > me at pet***@microsoft.com, and I willl check if the issue could be > reproudced on my side. > > I understand that you'v found a workaround on this issue but this may bring > some overhead because you have to maintain objects not required. > > Please let me know if you have any update and we look forward to your > reply. > ================================= > > Best Regards, > > Peter Yang > MCSE2000/2003, MCSA, MCDBA > Microsoft Online Partner Support > > > ===================================================== > > This posting is provided "AS IS" with no warranties, and confers no rights. > ====================================================== > > > Hello Bob,
Thank you for your update. I did see the screen shots you provided. However, I was not able to reproduce the issue on my side. As I mentioned, you may test the steps I listed to see if this issue persists. Also, if possible, please send me a simple sample SP and backend table schemas so that I may reproduce the issue. Actually, I'v searched in our database and does not find any known issue on this. I suspect it is related to specific database or machine environment. Hope I 'm able to get a repro on this so that we could report this issue if we consider it a bug. Thank you for your time. Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Partner Support ===================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ====================================================== Hello Bob,
Do you have any update on this issue? Did you try my steps to see if it has issues on your side? If possible, please send me a simple sample SP and backend table schemas so that I may reproduce the issue. You could send it to me at pet***@microsoft.com. Thanks. Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Partner Support ===================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ====================================================== Hi,
Sorry for the delay in answering, I was out of town and not where I could get online. I sent you an email with a simple breakdown that shows the issue. Have a happy day. Bob ""privatenews"" wrote: Show quote > Hello Bob, > > Do you have any update on this issue? Did you try my steps to see if it has > issues on your side? If possible, please send me a simple sample SP and > backend table schemas so that I may reproduce the issue. You could send it > to me at pet***@microsoft.com. Thanks. > > Best Regards, > > Peter Yang > MCSE2000/2003, MCSA, MCDBA > Microsoft Online Partner Support > > > ===================================================== > > This posting is provided "AS IS" with no warranties, and confers no rights. > ====================================================== > > > Hello Bob,
Thank you for your update and detailed information to reproduce the issue. I was able to reproduce the issue on my side. Based on my research, this behavior is by design. This issue has been documented in our internal database. The reason that no column information was available is that a local temp table was used in the select statement in the stored procedure. Since the local temp table is only created when the stored procedure is executed but the OLE DB Source UI does not execute the stored procedure, the column metadata cannot be determined when configuring the OLE DB Source adapter. You may want to use table valued function under the situation to work around this kind of issue. For example: create function testfun () returns @tbl table (contactname VARCHAR(128)) as begin INSERT INTO @tbl (contactname) SELECT DISTINCT dbo.PersonName.MiddleName FROM dbo.PersonName return end You could use following SQL statement instead. select * from testfun () I understand that it might not be convenient for you to use this workaround, and please rest assured your feedback on this feature is routed to the proper channel. In the meantime, I also encourage you submit via the link below http://lab.msdn.microsoft.com/productfeedback/default.aspx If you have any further questions or concerns on the issue, please feel free to let's know. Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Partner Support ===================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ====================================================== "privatenews" (pet***@online.microsoft.com) writes:
> Thank you for your update and detailed information to reproduce the issue. "By design"? Better call it limitation, at least for Microsoft's good> I was able to reproduce the issue on my side. Based on my research, this > behavior is by design. This issue has been documented in our internal > database. The reason that no column information was available is that a > local temp table was used in the select statement in the stored procedure. > Since the local temp table is only created when the stored procedure is > executed but the OLE DB Source UI does not execute the stored procedure, > the column metadata cannot be determined when configuring the OLE DB > Source adapter. reputation. :-) Yes, using a table variable instead of a temp table is plausible workaround. Don't think it has to be a table-valued function, though. (But I bave not seen the stored procedure code.) I don't know the interface looks where you feed SSIS the information, but if leaves room for an SQL statement, another possible workaround is SET FMTONLY OFF; EXEC mysp The reason is that SSIS submits SET FMTONLY ON before calling the procedure to get the profile for the result set. (I don't know much about SSIS, but I perfectly recognize what's going on from Peter's description.) By adding SET FMTONLY OFF, you lure SSIS to actually execute the procedure. Of course, this can have undesired consequences so be careful. Overall, SET FMTONLY ON is a poor hack with several unpleasant side effects. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  
Other interesting topics
|
|||||||||||||||||||||||
