July 27, 2009 at 2:31 pm
Env: SQL Server 2005 (SP3) Std version. Windows 2003
Visual Studio professional version.
I am just beyond beginners level for SSIS.
I am trying to run a stored procedure that requires parameter values to be passed.
I am trying to setup a DATAFLOW task with "OLE DB Source editor" where 'data access mode'='SQL Command' and 'SQL Command Text:' = 'exec [test].[TestSP] ?,?,?'
I set up Parameters properly, using 'User Variables'. I understand that 'Preview Option' does not work because variables are not bound at this time.
I added 'Excel Destination' DATAFLOW task right below 'OLE DB Source' (I set up as indicated above). When I try to Edit it gives me a message 'This component has no available input columns'.
It is not possible to map columns without having any columns from 'OLE DB Source'. How can I get columns from 'OLE DB Source' that can be input into 'Excel Destination'. Any help is appreciated.
PS. Here is an excellent article from elsewhere about setting up parameters for stored procedures in SSIS:
July 27, 2009 at 2:41 pm
Create an excel spreadsheet that matches the colums you are try to pull. This should make the message go away and you can map your columns. Be sure when you get in and point it to your excel sheet to check the box first line is header.
leisha
MCSE SQL Server 2012\2014\2016
July 27, 2009 at 4:15 pm
Thnx, but the problem is not Excel, it is with Available input columns, there are none from the 'OLE DB Source'.
July 27, 2009 at 5:35 pm
Maybe this thread is relevant:
http://qa.sqlservercentral.com/Forums/Topic746185-148-1.aspx
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 28, 2009 at 8:46 am
So you have you columns in the excel sheet? 🙂
MCSE SQL Server 2012\2014\2016
July 28, 2009 at 12:13 pm
Phil: That link you posted is of tremendous help, who would have known to publish metadata. Thank You.
'SQL NEW NEW': I managed to get it to work, the essential parts. I think you need column headings in Excel sheet if you checked 'First row is Header'.
Here are the steps that I took to accomplish the task so far, I spent a lot of time on this.
1) Write your stored procedures that requires parameters.
2) Publish metadata first in your stored procedure, follow steps in Phil's link, reposted below.
http://qa.sqlservercentral.com/Forums/Topic746185-148-1.aspx
Don't forget the statement 'If 1=0' statement you need it, it publishes the meta data with no output.
3) Follow the steps in this link to setup parameters in SSIS:
4) You may need to add 'Data conversion' from toolbox. Make 'OLE DB Source' output going into 'Data conversion'.
If you have any varchar columns, select them under "Available input columns' box. Change the Datatype to 'unicode string[DT_WSTR]. This action will create 'copy...' columns.
(I am not sure why I need to do this, I only have varchar columns)
5) Create an Excel file with column headings, I used exactly same column names and same order as in the metadata that I published in step 2.
6) I dragged 'Excel destination' from toolbox into Dataflow. I let Out put from 'Data Conversion' flow into 'Excel destination'. Edit 'Excel destination', click on NEW button next to 'OLE DB connection manager', and configure to the excel file created in step 5 (check box 'first row has column names'.
I left the 'Data access mode' as Table or view. Selected 'Name of excel sheet' as Sheet1$ sign (it is part of drop down box).
7) click on Mappings to remap - I had to map the converted columns (copy... columns) to destination columns.
It worked.
Now I am trying to setup so the user can run this package as a job. I need to give the user the ability to change parameter values, any ideas?.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply