June 23, 2008 at 11:25 am
My goal: To pull a small amount of data from a SQL Server 2000 database and output that data to an Excel file.
So far: I have run the SSIS Import Export Wizard, which set up the following for me:
In the Control Flow:
1) An "Execute SQL Task" which set a SQL Statement with a connection type of EXCEL, a connection of the "DestinationConnectionExcel" I had set up as the Excel output, a SQLSourceType of "Direct Input" and a SQL Statement that creates a table called Query that contains the 7 column names.
In the Data Flow:
2) A "OLE DB Source" task that contains a connection to the database and a SQL Command I wrote to get the data I need
3) A "Data Conversion" task in which all 7 column names have been selected for conversion to an alias and the data type has been assigned as a unicode text string on all columns.
4) An "EXCEL Destination" task in which the connection manager has been assigned to the "DestinationConnectionExcel" I had set up as the Excel output, the Data access mode is set to "Table of view" and the 'Name of the Excel sheet' has been set to "Query", which is the table name the "Execute SQL Task" creates.
The problem (at least one of them so far):
-- When I run the package, it comes back with an error stating that the table "Query" already exists.
Questions:
1) Why is it creating an interim table? Is that to temporarily hold the output from the main query before it can be input to the Excel file?
2) How can I get rid of the error message stating that the "Query" table already exists? Where is this "Query" table located?
June 23, 2008 at 11:37 am
1) Why is it creating an interim table? Is that to temporarily hold the output from the main query before it can be input to the Excel file?
There is no interim table being created. Your data flow is querying the data source, converting the data type, and outputting to Excel.
2) How can I get rid of the error message stating that the "Query" table already exists? Where is this "Query" table located?
This is because of the Execute SQL task that it created in the control flow. It has a CREATE TABLE statement in it. Disable or remove this task if you intend to re-use the Excel file that already has the output table in it. But remember, by removing this step, the Excel table will not be created if it does not exist, so the package would fail if you pointed it at an empty workbook.
June 23, 2008 at 1:01 pm
By removing the Execute SQL task, that leaves me with a Control Flow File System task (I forgot to mention this in my original post) in which I'm creating a new Excel file from an existing Excel file template, and my 3 aforementioned data flow tasks.
I'm confused about how to get the results of the "OLE DB Source" task into the spreadsheet created in the File System task (which only consists of a header row). When I create a connection manager in the "EXCEL Destination" task which has the name and path of the Excel file created in the File System task, it also wants a Data Access mode of table, view or SQL command. I don't understand what it's requesting?
Also, I don't know why the wizard set up a "Data Conversion" task, but I believe it has to do with the conversion from test to unicode characters.
It seems like this process should be a simple one, but I'm having a hard time getting a handle on what needs to be done to accomplish my goal.
June 24, 2008 at 12:00 am
Hey,
The execute SQL task is actually creating a worksheet called "Query".
If this worksheet exists, it will simply append to the data already there.
Why not simply have a drop table 'Query' before the create table command.
That should make sure that it drops the worksheet query (in other words deletes it), and recreates this same worksheet.
In terms of the data conversion, it depends. When you created the excel sheet through the wizard, did you change anything to the layout of the excel sheet? Unicode conversion is most probably the best explanation given.
Good luck
~PD
June 24, 2008 at 3:06 am
Hi fstop
I still think my advice on the other thread, to start from scratch, was right, but if you're not used to using SSIS this way, it may be a problem, so let's try again.
I'm no expert in SSIS but everything I do involves getting data out of a sql server database, manipulating it and outputting it to Excel, and it really is easy, so maybe you've missed telling us something. Here's some further suggestions:
1. Ok, your file system task works and you now have a copy of the Excel workbook in the output destination. Try this. Edit the excel destination and click on the dropdown box for 'Name of the Excel sheet'. You should be able to select the Query sheet from this list. The first thing that occurs to me is that spreadsheet pages have their names suffixed by a dollar, and maybe you had typed the name of the sheet without a $. Does your entry have Query$? If not, can you select Query$?. It might also help you to be clearer if you rename the spreadsheet page to something unique, perhaps something that has meaning to the business, like 'Weekly Absence Report', or whatever the data is. This will help you to understand where your data is going.
2. In it's simplest form, all your data flow needs is an OLEDB source and an Excel Destination. The oledb source needs a database connection, which I think your SSIS import routine provided, a Data Access mode of SQL Command, and a sequel select statement to get your data. You can put aliases in the statement if you need them. This part of the process will create a temporary table of metadata that is passed to the next box in the flow. If this is your Excel destination, SSIS will interpret the metadata and give you some default settings that you can overwrite. Can you amend your package just to do this? We can concentrate on refinements later.
3. I've found problems with the behaviour of a spreadsheet when a row or column appears empty and it isn't. Let's make sure the template is in a fit state before we try anything else. This is a sledgehammer to crack a nut, but can you open the template in Excel and select all columns to the right of your 7 data columns in the template and select Edit .. then Delete. Do the same thing with all the rows below your headings. Then run the complete package again.
4. Don't give up. You'll kick yourself when you find how easy it is - we all did 🙂
June 24, 2008 at 2:00 pm
Thank you so much for your posts.
Regarding your post, born2bongo:
For #1, I made sure Query$ was selected in the ExcelDestination.
For #2, I decided to remove the Data Conversion step and try running it without that. I received an error (the dreaded Red-X) in the Excel Destination step that it couldn't handle the conversion between unicode and non-unicode. So I put the Data Conversion step back in and set the data type to convert to unicode (DT_WSTR). In the ExcelDestination, I made sure the columns were matching up correctly. The Red-X was gone and all looked rosey.
For #3, I did what you requested.
So..now I run the package and I get another error from the dark, dusty closet of MS cryptic messages: "OLE DB Source failed the pre-execute phase". What is a pre-execute phase??? I know the source query ran because it inserts rows into a BP_TempObjects table. The last step in the query is a select so the rows I need are selected for output. Is that a problem? :w00t:
Any thoughts would be greatly appreciated.
June 24, 2008 at 2:12 pm
I did a net search on the "OLE DB Source failed the pre-execute phase" error and found out that I need to set Nocount On if the query updates, deletes or inserts rows. If I don't do this it will return a row count.
I set Nocount on and tried it again and IT WORKS!!!!!!! 🙂
Thank you, thank you, thank you
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply