May 5, 2009 at 8:42 pm
I could not find appropriate forum in the 2008 group for SSIS questions, so I'm asking here. (Steve - is the 2008 area supposed to be for features new to 2008, or should it pretty much mirror the 2005 area? There's nothing for SSIS, BIDS, BI, and lots of other things in the 2008 area that is in the 2005 area.)
I'm new to SSIS. I'm trying to learn it, but I'm having troubles. I will probably have more questions as I work through this. I am trying all of the help available in BIDS. I also have a Professional SQL Server 2005 Integration Services book that I'm trying to get answers out of also.
Goal: extract data from SQL server into Excel spreadsheets.
First, I get (from a sp) the publishers that need their data exported.
Then, for each publisher, export into a separate spreadsheet data about all of their publications:
The first sheet is a summary of all of the publisher's publications.
Additional sheets are the detail data for each publication.
My plan:
Execute SQL task that feeds a ForEach Loop Container for each publisher.
ForEach container gets data for each publisher, and exports data to excel spreadsheet.
So, I created the connection to the database, execute sql task and foreach loop. Inside the loop, I've got a script task to just do a message box for each PublisherID retrieved (you know, one step at a time...).
The script task's code is (set to use VS VB 2008):
Dim variables As Variables
If Dts.Variables.Contains("PublisherID") = True Then
Dts.VariableDispenser.LockOneForRead("PublisherID", variables)
End If
MsgBox(Dts.Variables("PublisherID").Value.ToString)
The Execute SQL Task and the PublisherID variable are set up per the screen shots in the attached word document.
When I run the package, I get this error in the Execute SQL Task:
Error: 0xC002F210 at Get Publishers for Retention Export, Execute SQL Task: Executing the query "dbo.spGet$PublisherProcess 'RetentionExport'" failed with the following error: "The type of the value being assigned to variable "User::PublisherID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I verified that the connection tests ok. Since I'm returning > 1 row, having the ResultSet set to "Full Result Set" is correct. I believe that I have the variable assigned correctly... the dataset is returning an int, and the variable is set to an Int32. (I did try Int16, Int64 and UInt32, but these didn't work either.) So, I'm not sure how to proceed.
Now, in the spirit of this website (you learn best by being guided, but doing it yourself), I want to learn SSIS. I'm not looking for being spoon-fed the answers, but for guidance in figuring out what's wrong, and in learning SSIS. At times, I may need something hammered into me, but that's not what I'm generally looking for.
Thanks,
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 5, 2009 at 9:50 pm
I should have looked on this web site harder. Found the solution to my (first) issue: the variable that the Execute SQL task goes into needs to be an object variable, not a Int32.
The link I found is here[/url].
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 8, 2009 at 10:08 pm
Okay, time for the next issue.
The goal of the package is to dynamically build Excel spreadsheets, with a variable number of tabs (worksheets). The # of spreadsheets, the # of tabs, and the data on each tab is all based off of results from stored procedures.
Setup:
Execute SQL Task #1: (gets list of publishers to process) Results to an object variable. (2 columns per row)
this feeds into a For Each Loop Container
the For Each Loop Container has, in order: (Separates columns into variables)
1. Create file folder File System Task
2. Delete existing file File System Task
3. Execute SQL Task to create the spreadsheet and first tab.
... more work to do
The Execute SQL Task in the For Each Loop Container is set to run a variable. It is connected to the Excel Connection Manager. The variable contains this statement:
"CREATE TABLE 'Summary' (
'Month' NVARCHAR(9),
'Base1' NVARCHAR(20),
'Retained1' NVARCHAR(20),
'Percent1' NVARCHAR(20),
'Base2' NVARCHAR(20),
'Retained2' NVARCHAR(20),
'Percent2' NVARCHAR(20),
'Base3' NVARCHAR(20),
'Retained3' NVARCHAR(20),
'Percent3' NVARCHAR(20)
)"
When I click on the "Parse Query" button, I get this error message:
"The query failed to parse. Syntax error in CREATE TABLE statement."
When I run the project, this step errors out with this error:
Error: 0xC00291EC at Get Publisher's Total Data, Execute SQL Task: Failed to acquire connection "Excel Connection Manager". Connection may not be configured correctly or you may not have the right permissions on this connection.
Task failed: Get Publisher's Total Data
The step to create a directory off of the data from the first Execute SQL Task is being created.
It would seem that it's failing on the CREATE TABLE statement, but it looks correct to me.
Can anyone help me figure out what's messed up with this?
On a related note, is nvarchar the only data type to use when creating excel tabs this way? Can you use INTEGER, or FLOAT?
BTW, this is running on Vista 64-bit, SQL Server Developer Edition (64-bit).
Thanks,
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 10, 2009 at 9:39 pm
WayneS (5/8/2009)
"CREATE TABLE 'Summary' ('Month' NVARCHAR(9),
'Base1' NVARCHAR(20),
'Retained1' NVARCHAR(20),
'Percent1' NVARCHAR(20),
'Base2' NVARCHAR(20),
'Retained2' NVARCHAR(20),
'Percent2' NVARCHAR(20),
'Base3' NVARCHAR(20),
'Retained3' NVARCHAR(20),
'Percent3' NVARCHAR(20)
)"
Well, I've found out that contrary to many articles on the internet, and even some on this site, that what wasn't being liked was the single quotes around the table/column names. Just for kicks, I did it the sql way with [], and it worked just fine.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 10, 2009 at 10:31 pm
Here are other issues that I've run into, and I haven't been able to figure out a solution for. Any help is appreciated.
I can probably do all of this with Excel Automation, but I'm trying to do it all from within SSIS without Excel Automation (since that requires Excel on the server, and all the underlying problems that can crop up with Excel Automation).
Once I'm working with the Excel Spreadsheet:
1. How do you handle where column headers are 2 rows, ie:
Column E (Row 1): 2009
Column E (Row 2): Base Qty
data is to follow in subsequent rows.
2. Is there a way to have one entire row be bold?
3. Is there a way to format numbers with commas?
4. How to you display a number as a percentage?
5. If the table name in the CREATE TABLE is [Something & SomethingElse], the tab name that gets created is:
Something___SomethingElse
(each space and the ampersand is replaced with underscores)
How do you get it to be displayed as named in the CREATE TABLE statement?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply