ExecSQL Task Truncates Ouput Parameter

I am successfully passing several parameters into a SP using the ExecSQL Task. I have a single parameter that is an OUTPUT parameter in the SP.

I always recieve the first character of the OUTPUT string. I have tried chaning the data type of the parameter mapping [AnsiString, String, etc.] and I have tried changing the data type of the OUTPUT parameter [varchar, char, nvarchar, nchar]

In each case, only the frist character of the output value is returned and subsequently passed to the next SSIS component / task.

Any ideas why this value is truncated?It is a String.

One of the previous replies indicated that there is a max lenght on the variable, but I do not see where to set this length. Any further information on the issue is appreciated.

John|||

jloper wrote:

One of the previous replies indicated that there is a max lenght on the variable, but I do not see where to set this length. Any further information on the issue is appreciated.

John

Ignore that, I was wrong. Sorry!

Note to self: Check out your answers before posting them!

-Jamie

exec stored procedure

Hi,

I'm new to SSIS and SQL Server 2005 and this is now driving me very mad!!

I have an OLE DB Command in my data flow task that I want to update a table with. I have looked round this forum and on Google and just can not find a solution or what I am doing wrong. So any help would be great!

The ole db command calls a stored procedure with two input variables:

exec stp_updedgrsholds status, temp_cr_num

from debugging the ssis it says it has updated 4 rows and also from doing a data view, the data it is updating seems all correct.

but nothing gets updated in the database.

If I call the stored procedure the following way

exec stp_updedgrsholds 'C', 87

It updates fine! I have tried a number of different way with @. symbols and assignment p_status = @.status

but nothing seems to work.

Any ideas are much appreciated.

Ninder Bassi

If you call the stored proedure and it succeeds, but then data is not updated, have you confused the target of your connections?

What is a data view?

How is the stored procedure being called, what task or component?

What are status and tep_cr_num, that syntax does not make sense. When passing in parameters with OLE-DB, it is usual to use ? as the placeholder, then in the task or component UI, use the "Parameters" screen to map a SSIS variable to the placeholder.

|||

told you i was new to it all!!

the stored procedure is being called from the ole-db command

i have tried the ?'s now and get the following error after mapping them in the "column mappings" tab.

[OLE DB Commmand [3310]]: Columns cannot convert between unicode and non-unicode string data types.

many thanks for the help.

|||

ot it. did a data conversion and the parameters mapped fine. and all worked fine.

many thanks DarrenSQLIS i just needed that nudge in the right direction.

Exec SQL Task: Capture return code of stored proc not working

I am just trying to capture the return code from a stored proc as follows and if I get a 1 I want the SQL Task to follow a failure(red) constrainst workflow and send a SMTP mail task warning the customer. How do I achieve the Exec SQL Task portion of this, i get a strange error message [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

Using OLEDB connection, I utilize SQL: EXEC ? = dbo.CheckCatLog

EXEC SQL Task Editer settings:
RESULTSET: Single Row
PARAMETER MAPPING: User::giBatchID
DIRECTION: OUTPUT
DATATYPE: LONG
PARAMETER NAME: 0

PS-Not sure if I need my variable giBatchID which is an INT32 but I thought it is a good idea to feed the output into here just in case there is no way that the EXEC SQL TASK can chose the failure constrainst workflow if I get a 1 returned or success constraint workflow if I get a 0 returned from stored proceedure

CREATE PROCEDURE CheckCatLog
@.OutSuccess INT
AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @.RowCountCAT INT
DECLARE @.RowCountLOG INT

these totals should match
SELECT @.RowCountCAT = (SELECT Count(*) FROM mydb_Staging.dbo.S_CAT)
SELECT @.RowCountLOG = (SELECT Count(*) FROM mydb_Staging.dbo.S_LOG)
--PRINT @.RowCountCAT
--PRINT @.RowCountLOG
BEGIN
IF @.RowCountCAT <> @.RowCountLOG
--PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file'
--RETURN 1
SET @.OutSuccess = 1
END
GO

Thanks in advance

Dave

Set ResultSet=None.

If OutSuccess is an OUTPUT parameter, you have to modify the second line in SP to "@.OutSuccess INT OUTPUT". If it is not an OUTPUT parameter, you have to modify the mapping direction in your task.

Also, if you are returning a value from the SP, you have to add a parameter (mapping direction: ReturnValue) to get the return value.
|||

Thanks for quick reply--opps I have fixed the SPROC see bold and set the result set in SSIS to none and still get the error--what I want to do is return the output from the Exec SQL task into a variable giBatchID Int32 and then connect to a script task and if value of giBatchID = 1 then fail the task and connect to an SMTP task to send an alert to the customer. The showstopper is the Execute SQL task with OLEDB connection to SQL server 2005 table--it simply does not work, there is no way that it will pick up a single row output from a mple OLEDB connection: Any ideas guys?

CREATE PROCEDURE CheckCatLog
@.OutSuccess INT OUTPUT
AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @.RowCountCAT INT
DECLARE @.RowCountLOG INT

these totals should match
SELECT @.RowCountCAT = (SELECT Count(*) FROM mydb_Staging.dbo.S_CAT)
SELECT @.RowCountLOG = (SELECT Count(*) FROM mydb_Staging.dbo.S_LOG)
--PRINT @.RowCountCAT
--PRINT @.RowCountLOG
BEGIN
IF @.RowCountCAT <> @.RowCountLOG
--PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file'
--RETURN 1
SET @.OutSuccess = 1

RETURN @.OutSuccess
END
GO

|||You can try Parameter Mapping, set direction to Output and map the parameter name 0 to the user variables.

Also set a default to @.OutSuccess to 0 instead of null
|||I do not understand why you would want to make OutSuccess an OUTPUT parameter and the return value.

My suggestion would be to make it just an OUTPUT paramter and not the return value. So, leave the second line in SP as it is, but comment out RETURN part. Set ResultSet=None in your task. Set SQLStatement="CheckCatLog ? OUTPUT". Then add an output parameter (variablename=giBatchID, Direction=Output, Type=Int32 and Parameter Name=0).

EXEC SQL TASK to FLAT FILE - questions

Hi,

I have a FOREACHLOOP container that contains an EXECUTE SQL TASK. The EXECUTE SQL TASK is executing a list of stored procedures from a table. This part is working.

However, I now need to send the output of each stored stored procedure to a flat file.

I dropped a DATA FLOW task inside the FOREACHLOOP container, and then created an OLEDB source and FLAT FILE destination on the Data Flow tab.

However, I'm not sure how this is going to work. When I click on the OLEDB source, Connection Manager, I thought I should select the Data Access Mode as SQL Command from variable. Then select the variable name.

But I get this error when I try to save:

Error at Data Flow Task [OLE DB Source [42]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C

An OLE DB Recored is available. Source: blah

"Command text was not set for the command object"

What to do?

Thanks

Would the data flow task work well as a stand alone task with one fixed parameter before it is wrapped into the "FOREACHLOOP"?

You mentioned the parameter is used to build up the stored procedure statements. If it is the case, try to write the line of SQL command you used for invoking the stored procedure into a flat file for logging purpose. The log file can be used to check syntax and display parameters passed in. Sometime, it will help debugging.

|||

K108 wrote:

Hi,

I have a FOREACHLOOP container that contains an EXECUTE SQL TASK. The EXECUTE SQL TASK is executing a list of stored procedures from a table. This part is working.

However, I now need to send the output of each stored stored procedure to a flat file.

I dropped a DATA FLOW task inside the FOREACHLOOP container, and then created an OLEDB source and FLAT FILE destination on the Data Flow tab.

However, I'm not sure how this is going to work. When I click on the OLEDB source, Connection Manager, I thought I should select the Data Access Mode as SQL Command from variable. Then select the variable name.

But I get this error when I try to save:

Error at Data Flow Task [OLE DB Source [42]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C

An OLE DB Recored is available. Source: blah

"Command text was not set for the command object"

What to do?

Thanks

Something is not clear here for me. If you use an Execute SQl Task to execute the SPs; I don't see a way you can get their resultsets inside of the data flow.

Can you provide the SQL statement you are placing in the variable used in the OLE DB SOurce component?

Are the SPs' resulet structures consistent? if not, you will need a separate dataflow.

You may have more than one option to get this done; but we need more info to help you

|||

Did you ever resolve this issue? I'm having the same problem using the Foreach loop container to access excel files on the network. I'm getting the same exact error when trying to use a variable to change the connection string in my excel file connection. Here's the error message:

TITLE: Microsoft Visual Studio

Error at GDW - RDB LOAD [Connection manager "UK RDB"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.

Error at Extract UK RDB [UK RDB [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "UK RDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

Any help would be greatly appreciated!

Kevin

|||

You will need to provide a valid initial value in used variables or try to play with the DelayValidation flag.

Thanks.

EXEC SQL TASK to FLAT FILE - questions

Hi,

I have a FOREACHLOOP container that contains an EXECUTE SQL TASK. The EXECUTE SQL TASK is executing a list of stored procedures from a table. This part is working.

However, I now need to send the output of each stored stored procedure to a flat file.

I dropped a DATA FLOW task inside the FOREACHLOOP container, and then created an OLEDB source and FLAT FILE destination on the Data Flow tab.

However, I'm not sure how this is going to work. When I click on the OLEDB source, Connection Manager, I thought I should select the Data Access Mode as SQL Command from variable. Then select the variable name.

But I get this error when I try to save:

Error at Data Flow Task [OLE DB Source [42]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C

An OLE DB Recored is available. Source: blah

"Command text was not set for the command object"

What to do?

Thanks

Would the data flow task work well as a stand alone task with one fixed parameter before it is wrapped into the "FOREACHLOOP"?

You mentioned the parameter is used to build up the stored procedure statements. If it is the case, try to write the line of SQL command you used for invoking the stored procedure into a flat file for logging purpose. The log file can be used to check syntax and display parameters passed in. Sometime, it will help debugging.

|||

K108 wrote:

Hi,

I have a FOREACHLOOP container that contains an EXECUTE SQL TASK. The EXECUTE SQL TASK is executing a list of stored procedures from a table. This part is working.

However, I now need to send the output of each stored stored procedure to a flat file.

I dropped a DATA FLOW task inside the FOREACHLOOP container, and then created an OLEDB source and FLAT FILE destination on the Data Flow tab.

However, I'm not sure how this is going to work. When I click on the OLEDB source, Connection Manager, I thought I should select the Data Access Mode as SQL Command from variable. Then select the variable name.

But I get this error when I try to save:

Error at Data Flow Task [OLE DB Source [42]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C

An OLE DB Recored is available. Source: blah

"Command text was not set for the command object"

What to do?

Thanks

Something is not clear here for me. If you use an Execute SQl Task to execute the SPs; I don't see a way you can get their resultsets inside of the data flow.

Can you provide the SQL statement you are placing in the variable used in the OLE DB SOurce component?

Are the SPs' resulet structures consistent? if not, you will need a separate dataflow.

You may have more than one option to get this done; but we need more info to help you

|||

Did you ever resolve this issue? I'm having the same problem using the Foreach loop container to access excel files on the network. I'm getting the same exact error when trying to use a variable to change the connection string in my excel file connection. Here's the error message:

TITLE: Microsoft Visual Studio

Error at GDW - RDB LOAD [Connection manager "UK RDB"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.

Error at Extract UK RDB [UK RDB [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "UK RDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

Any help would be greatly appreciated!

Kevin

|||

You will need to provide a valid initial value in used variables or try to play with the DelayValidation flag.

Thanks.

Exec SQL Task (stored procedures)

Hi,

Can anyone tell me how to pass parameters from one exec sql task to other ?... (I used stored proc in 1st exec sql task) and passed input parameter (default value set using a variable A) and stored the output parameter value in another variable B.

In the 2nd exec sql task , I passed the output param ( value of B) and doing insert into table xyz...

I get errors (in passing int and string values) . I tried using ole-db as well as ado.net.

Kindly give sample example.

Thanks,

The approach you are using sounds right to me. Could you post the details (including connection manager type) of the 2 execute sql task and the error you get?.

Exec Process Task problem

I am trying to use a variable in an Execute Process Task

Here is my code that I use to set the variable and it works and has the right information:

Public Sub Main()

Dim serverNamePart As String = Mid(CStr(System.Environment.MachineName()), 1, 5)

Dts.Variables.Item("gvServerName").Value = (CStr(System.Environment.MachineName()))

Select Case serverNamePart

Case "ATCDE" 'Development server

Dts.Variables.Item("gvAppServer").Value = "ATCDEVRPNAPP01"

Case "ATCIN" 'Intergration server

Dts.Variables.Item("gvAppServer").Value = "ATCINTRPNAPP01"

Case "ATCDE" 'Development server

Dts.Variables.Item("gvAppServer").Value = "ATCQARPNAPP01"

Case "LTC1P" 'Development server

Dts.Variables.Item("gvAppServer").Value = "LTC1RPNAPP01 LTC1RPNAPP02 LTC1RPNAPP03 LTC1RPNAPP04"

End Select

' MsgBox(Dts.Variables.Item("gvServerName").Value)

MsgBox(Dts.Variables.Item("gvAppServer").Value)

Dts.TaskResult = Dts.Results.Success

End Sub

On my Execute Process task I have User::gvAppServer as my StandardInputVariable but when I execute the task I get

[Execute Process Task] Error: In Executing "D:\RPNUtility\bin\AutoTransmitCall.exe" "" at "", The process exit code was "1" while the expected was "0".

If I take out the StandardInputVariable and hard code the value in Arguments it works. What am I doing wrong?

Phil, I believe you want to parameterize your "Arguments" property rather than your StandardInputVariable property. Use the Expressions page of the Execute Process Editor to use your gvAppServer variable for Arguments.

Your StandardInputVariable would be used for data that is processed by an executable, rather than a parameter for it.

|||That looks like it worked. Thanks.

ExceptionMessageBox

I am trying to get the Exception Message Box to work in a script task in SSIS.

I am using the example from http://msdn2.microsoft.com/en-us/library/ms166340.aspx almost verbatum.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.MessageBox

Public Sub Main()

'

' Define the message and caption to display.

Dim str As String = "Do you want to push to Production?"

Dim caption As String = "Zip Push"

Dim var As Variables

' Show the exception message box with Yes and No buttons.

Dim box As ExceptionMessageBox = New ExceptionMessageBox(str, caption)

box.DefaultButton = ExceptionMessageBoxDefaultButton.Button2

box.Symbol = ExceptionMessageBoxSymbol.Question

box.Buttons = ExceptionMessageBoxButtons.YesNo

'box.SetButtonText("Yes", "No", "Cancel")

If Windows.Forms.DialogResult.Yes = box.Show(CType(Me,Windows.Forms.IWin32Window)) Then

Dts.Variables("Production").Value = True

End If

'

Dts.TaskResult = Dts.Results.Success

End Sub

And yet all I get is the following error

Unable to cast object of type 'ScriptTask_bc7fa8cd8b3c4f4d96407f2b13927e0f.ScriptMain' to type 'System.Windows.Forms.IWin32Window'.

Has anyone gotten this to work?

BTW: I am running SQL 2005 SP2

Just use MsgBox("...")

You don't even need to reference any assembly for it.

By the way, what are you trying to do here? SSIS is supposed to be a batch-oriented process, not interactive.

-Jamie

|||MsgBox works, but it's too limited. I wanted the added functionality of ExceptionMessageBox. Besides, my question was not "How do I NOT use ExceptionMessageBox?".|||

S1monk wrote:

MsgBox works, but it's too limited. I wanted the added functionality of ExceptionMessageBox. Besides, my question was not "How do I NOT use ExceptionMessageBox?".

Was it? I've just re-read your original post and didn't see that question anywhere. All I saw was "Has anyone gotten this to work?"

I'm afraid I don't know why your code isn't working. What exactly do you want to do?

-Jamie

|||

Jamie Thomson wrote:

What exactly do you want to do?

I second this question. Also Jamie's comment about SSIS being a batch utility, not an interactive one.

|||

What I would like to do is use ExceptionMessageBox to display a message and allow me to customize the buttons, which I believe you cannot do with MsgBox. The example I used is just a simplest case taken from http://msdn2.microsoft.com/en-us/library/ms166340.aspx to test the functionality. I cannot get it to run. All I get is

Unable to cast object of type 'ScriptTask_bc7fa8cd8b3c4f4d96407f2b13927e0f.ScriptMain' to type 'System.Windows.Forms.IWin32Window'.

Any help would be greatly appreciated, but comments like use something else and SSIS is a batch utility are not helpfull.

|||

S1monk wrote:

What I would like to do is use ExceptionMessageBox to display a message and allow me to customize the buttons, which I believe you cannot do with MsgBox. The example I used is just a simplest case taken from http://msdn2.microsoft.com/en-us/library/ms166340.aspx to test the functionality. I cannot get it to run. All I get is

I don't know for sure but my guess from the error message is that this simply cannot be done. The script task is not designed to be used interactively as you are attempting to do.

S1monk wrote:

Unable to cast object of type 'ScriptTask_bc7fa8cd8b3c4f4d96407f2b13927e0f.ScriptMain' to type 'System.Windows.Forms.IWin32Window'.

Any help would be greatly appreciated, but comments like use something else and SSIS is a batch utility are not helpfull.

If something isn't working then I would have thought suggesting an alternative was perfectly good advice. Likewise the advice about SSIS being a batch utility was intended to be helpful and, as I think your error message proves, this advice has been borne out to be true.

The question "What exactly are you trying to do?" was a lead-in to suggesting an alternative that WOULD work. Due to the very nature of SSIS, prompting users for input from INSIDE a package is not an appropriate thing to do - better to prompt them elsewhere and pass that information into the package so that it can act upon it dynamically.

I was trying to proffer some simple advice but obviously that advice is not appreciated hence I won't post on this thread again. I have no desire to help someone that doesn't value that help. Good luck in finding a solution to your problem.

-Jamie

|||

The type you are passing to the Show method is not a window. You have to catch a handle of the top window and pass it to this method. Perhaps, even null could work but it might create a weird effects (like popping up in the backround or something similar).

|||

The error is because Me is not a window, it does not implement IWin32Window, so the cast is invalid - CType(Me,Windows.Forms.IWin32Window)

You do not have a Form, because SSIS is just not aimed at being an interactive tool, which is the point others have tried to highlight.

You could get the same functionality with old MsgBox or System.Windows.Forms.MessageBox (same thing really), it supports Yes/No/Cancel if you wish.

Sorry for going so far off the question, but I thought it might help provide a solution.

|||

I found a way to make it work

Option Strict On

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.MessageBox

Public Class ScriptMain

Public Sub Main()

Dim str As String = "Are you sure you want to delete file 'c:\somefile.txt'?"

Dim caption As String = "Confirm File Deletion"

Dim win As Windows.Forms.IWin32Window

' Show the exception message box with Yes and No buttons.

Dim box As ExceptionMessageBox = New ExceptionMessageBox(str, _

caption, ExceptionMessageBoxButtons.YesNo, _

ExceptionMessageBoxSymbol.Question, _

ExceptionMessageBoxDefaultButton.Button2)

If Windows.Forms.DialogResult.Yes = box.Show(win) Then

' Delete the file.

End If

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

|||[Shaking head]

I just don't understand why you'd want to do this. I just don't understand........ SSIS is not an interactive tool - your solution only works when debugging SSIS.

[/Shaking head]|||I don't understand why you are all so hung up on SSIS being batch or interactive. It is what it is and it works how you use it. We do a lot of data loads on one server and then push the data to different environments (Development, QA, Staging, Production). With DTS we had a seperate package for each push. It leads to a LOT of packages. My goal was to clean this up a little and make the packages a little more versatile. I don't know what flavor of SSIS you are using, but the above code works just fine in my INTERACTIVE production environment.|||

S1monk wrote:

I don't understand why you are all so hung up on SSIS being batch or interactive. It is what it is and it works how you use it. We do a lot of data loads on one server and then push the data to different environments (Development, QA, Staging, Production). With DTS we had a seperate package for each push. It leads to a LOT of packages. My goal was to clean this up a little and make the packages a little more versatile. I don't know what flavor of SSIS you are using, but the above code works just fine in my INTERACTIVE production environment.

HAHAHAHA. Yeah, okay.|||I hope we can agree then, that when developing, testing or maintaining in BIDS, dialogs can be useful.

The use of dialogs IN SSIS packages is not a good idea in general, but even so, that's not a universal truth (dialogs + SSIS = misunderstanding SSIS and its intent) .

As long as the System::InteractiveMode variable is checked in advance, dialogs are fine. SSIS provides this variable since a good amount of time is spent in development and maintenance inside of BIDS, where its nice to make changes to variables without changing package source code.

Its for this same reason ("debugging and maintenance") that many shells and language interpreters have both an interactive mode ( bash, python,ruby, and powershell come to mind ) and may be run interactively as well as non-interactively.

Exception trying to import data from Excel .

Hello,
I am running SQL Management Studio and was trying to import excel data into
a table on my local 2005 database. When I choose the import task then choose
Excel as the data source I get this lovely message:
TITLE: SQL Server Import and Export Wizard
An error occurred which the SQL Server Integration Services Wizard was not
prepared to handle.
ADDITIONAL INFORMATION:
Exception has been thrown by the target of an invocation. (mscorlib)
The connection type "EXCEL" specified for connection manager
"{2D54D28C-42CF-4614-ADB6-371E9E4F927D}" is not recognized as a valid
connection manager type. This error is returned when an attempt is made to
create a connection manager for an unknown connection type. Check the
spelling in the connection type name.
({06C927B9-F2F2-429B-B488-591883AE4655})
The connection type "EXCEL" specified for connection manager
"{2D54D28C-42CF-4614-ADB6-371E9E4F927D}" is not recognized as a valid
connection manager type. This error is returned when an attempt is made to
create a connection manager for an unknown connection type. Check the
spelling in the connection type name.
({06C927B9-F2F2-429B-B488-591883AE4655})
I have searched the net (google and groups) and have come up with nothing.
Any ideas would be greatly appreciated.
Rich
Hello Rich,
It seems there is some issues in SSIS related components are not registered
properly. You may want to try the following:
Bring up a command prompt and
-- go to C:\Program Files\Microsoft SQL Server\90\DTS\Binn
-- type below to unregister:
regsvr32.exe -u dtsconn.dll
--Then type below to register:
regsvr32.exe dtsconn.dll
If the issue persists, please ensure the Users (machinename\Users) have
Full Control on the
HKEY_CLASSES_ROOT\CLSID\.
In fact, you should go to PERMISSION-->ADVANCE
Ensure machinename\Users have full control permission.
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.
--
>Thread-Topic: Exception trying to import data from Excel .
>thread-index: AcY9aoUve4FjMD2oRuCRoGREQJdwgw==
>X-WBNR-Posting-Host: 143.166.226.16
>From: "=?Utf-8?B?UmljaCBEZW5pcw==?=" <cojones@.community.nospam>
>Subject: Exception trying to import data from Excel .
>Date: Wed, 1 Mar 2006 11:58:33 -0800
>Lines: 39
>Message-ID: <61A56959-4BBB-4267-BDF9-D2CACA3AF52E@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.tools
>Path: TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.tools:29869
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>Hello,
>I am running SQL Management Studio and was trying to import excel data
into
>a table on my local 2005 database. When I choose the import task then
choose
>Excel as the data source I get this lovely message:
>TITLE: SQL Server Import and Export Wizard
>--
>An error occurred which the SQL Server Integration Services Wizard was not
>prepared to handle.
>--
>ADDITIONAL INFORMATION:
>Exception has been thrown by the target of an invocation. (mscorlib)
>--
>The connection type "EXCEL" specified for connection manager
>"{2D54D28C-42CF-4614-ADB6-371E9E4F927D}" is not recognized as a valid
>connection manager type. This error is returned when an attempt is made to
>create a connection manager for an unknown connection type. Check the
>spelling in the connection type name.
> ({06C927B9-F2F2-429B-B488-591883AE4655})
>--
>The connection type "EXCEL" specified for connection manager
>"{2D54D28C-42CF-4614-ADB6-371E9E4F927D}" is not recognized as a valid
>connection manager type. This error is returned when an attempt is made to
>create a connection manager for an unknown connection type. Check the
>spelling in the connection type name.
> ({06C927B9-F2F2-429B-B488-591883AE4655})
>I have searched the net (google and groups) and have come up with nothing.

>Any ideas would be greatly appreciated.
>--
>Rich
>
|||Peter,
Thanks for the quick response. I tried what you said (unregister and
re-register) and it yeilded no results. I then applied the registry
permisison (while SQL Management studio was open and the error was on the
screen) to the CLSID folder (giving Users full control). I then tried the
operation again and got a message saying something to the effect of Server
Name Unknown (sorry I didnt think to write it down). So I closed the SQL
Management studio and re-opened it. When I tried again, I got a similar
message to the first time I tried but it had different CLSID's. I have
attached the message below.
One other thing to note, I noticed that I also do not have the drivers to be
able to read in such things as flat files. On a co-workers computer it
appears between the first .net dirvers and the media catalog drivers. I am
not sure if te two are related but I thought I would bring it up.
Lastly before, you replied today, I uninstalled SQL Server and the tools and
then re-installed. Still nothing.
Message follows:
TITLE: SQL Server Import and Export Wizard
An error occurred which the SQL Server Integration Services Wizard was not
prepared to handle.
ADDITIONAL INFORMATION:
Exception has been thrown by the target of an invocation. (mscorlib)
The connection type "EXCEL" specified for connection manager
"{586BE7D4-F968-4585-9DE7-DAEDAD067CF3}" is not recognized as a valid
connection manager type. This error is returned when an attempt is made to
create a connection manager for an unknown connection type. Check the
spelling in the connection type name.
({38BF22B4-3036-4BAB-9177-4820DA4EE187})
The connection type "EXCEL" specified for connection manager
"{586BE7D4-F968-4585-9DE7-DAEDAD067CF3}" is not recognized as a valid
connection manager type. This error is returned when an attempt is made to
create a connection manager for an unknown connection type. Check the
spelling in the connection type name.
({38BF22B4-3036-4BAB-9177-4820DA4EE187})
BUTTONS:
OK
Rich
"Peter Yang [MSFT]" wrote:

> Hello Rich,
> It seems there is some issues in SSIS related components are not registered
> properly. You may want to try the following:
> Bring up a command prompt and
> -- go to C:\Program Files\Microsoft SQL Server\90\DTS\Binn
> -- type below to unregister:
> regsvr32.exe -u dtsconn.dll
> --Then type below to register:
> regsvr32.exe dtsconn.dll
> If the issue persists, please ensure the Users (machinename\Users) have
> Full Control on the
> HKEY_CLASSES_ROOT\CLSID\.
> In fact, you should go to PERMISSION-->ADVANCE
> Ensure machinename\Users have full control permission.
> 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.
>
> --
> into
> choose
>
>
|||Hello Rich,
It seems that oledb related driver has issues on this server. I suggest
that you try the following steps:
1. Reinstall MDAC by right clicking %windir%\inf\mdac.inf->Install to
reinstall MDAC. You may prompt to insert Win2003 setup CD.
2. Reinstall Jet SP8 on your server:
239114: How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0
http://support.microsoft.com/default...b;en-us;239114
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.
--
>Thread-Topic: Exception trying to import data from Excel .
>thread-index: AcY9rUgr1ynjYsVLQnuskA4GOXAWCw==
>X-WBNR-Posting-Host: 70.123.136.122
>From: "=?Utf-8?B?UmljaCBEZW5pcw==?=" <cojones@.community.nospam>
>References: <61A56959-4BBB-4267-BDF9-D2CACA3AF52E@.microsoft.com>
<53c43gaPGHA.8000@.TK2MSFTNGXA03.phx.gbl>
>Subject: RE: Exception trying to import data from Excel .
>Date: Wed, 1 Mar 2006 19:56:27 -0800
>Lines: 167
>Message-ID: <ADECD008-AC46-4800-B932-121744E9B779@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.tools
>Path: TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.tools:29881
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>Peter,
>Thanks for the quick response. I tried what you said (unregister and
>re-register) and it yeilded no results. I then applied the registry
>permisison (while SQL Management studio was open and the error was on the
>screen) to the CLSID folder (giving Users full control). I then tried the
>operation again and got a message saying something to the effect of Server
>Name Unknown (sorry I didnt think to write it down). So I closed the SQL
>Management studio and re-opened it. When I tried again, I got a similar
>message to the first time I tried but it had different CLSID's. I have
>attached the message below.
>One other thing to note, I noticed that I also do not have the drivers to
be
>able to read in such things as flat files. On a co-workers computer it
>appears between the first .net dirvers and the media catalog drivers. I
am
>not sure if te two are related but I thought I would bring it up.
>Lastly before, you replied today, I uninstalled SQL Server and the tools
and[vbcol=seagreen]
>then re-installed. Still nothing.
>Message follows:
>TITLE: SQL Server Import and Export Wizard
>--
>An error occurred which the SQL Server Integration Services Wizard was not
>prepared to handle.
>--
>ADDITIONAL INFORMATION:
>Exception has been thrown by the target of an invocation. (mscorlib)
>--
>The connection type "EXCEL" specified for connection manager
>"{586BE7D4-F968-4585-9DE7-DAEDAD067CF3}" is not recognized as a valid
>connection manager type. This error is returned when an attempt is made to
>create a connection manager for an unknown connection type. Check the
>spelling in the connection type name.
> ({38BF22B4-3036-4BAB-9177-4820DA4EE187})
>--
>The connection type "EXCEL" specified for connection manager
>"{586BE7D4-F968-4585-9DE7-DAEDAD067CF3}" is not recognized as a valid
>connection manager type. This error is returned when an attempt is made to
>create a connection manager for an unknown connection type. Check the
>spelling in the connection type name.
> ({38BF22B4-3036-4BAB-9177-4820DA4EE187})
>--
>BUTTONS:
>OK
>--
>
>--
>Rich
>
>"Peter Yang [MSFT]" wrote:
registered[vbcol=seagreen]
rights.[vbcol=seagreen]
not[vbcol=seagreen]
to[vbcol=seagreen]
to[vbcol=seagreen]
nothing.
>
|||Peter,
You are a genius. Thanks a million. I re-installed the MDAC inf and
everything started working again. Thank you so much. I have a task where I
REALLY needed to be able to import some xls/csv spreadsheets and was not
looking forward to having to write the code to do it myself.
Thanks again.
Rich
"Peter Yang [MSFT]" wrote:

> Hello Rich,
> It seems that oledb related driver has issues on this server. I suggest
> that you try the following steps:
> 1. Reinstall MDAC by right clicking %windir%\inf\mdac.inf->Install to
> reinstall MDAC. You may prompt to insert Win2003 setup CD.
> 2. Reinstall Jet SP8 on your server:
> 239114: How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0
> http://support.microsoft.com/default...b;en-us;239114
> 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.
>
> --
> <53c43gaPGHA.8000@.TK2MSFTNGXA03.phx.gbl>
> be
> am
> and
> registered
> rights.
> not
> to
> to
> nothing.
>
|||Hello Rich,
Welcome! Great to hear the issue is resolved. :-)
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.
--
>Thread-Topic: Exception trying to import data from Excel .
>thread-index: AcY+EDFvaKWB5xrzSHyd9a5fSbx9sA==
>X-WBNR-Posting-Host: 143.166.226.17
>From: "=?Utf-8?B?UmljaCBEZW5pcw==?=" <cojones@.community.nospam>
>References: <61A56959-4BBB-4267-BDF9-D2CACA3AF52E@.microsoft.com>
<53c43gaPGHA.8000@.TK2MSFTNGXA03.phx.gbl>
<ADECD008-AC46-4800-B932-121744E9B779@.microsoft.com>
<MZFTnFdPGHA.2528@.TK2MSFTNGXA03.phx.gbl>
>Subject: RE: Exception trying to import data from Excel .
>Date: Thu, 2 Mar 2006 07:44:29 -0800
>Lines: 247
>Message-ID: <FF059899-A260-49CC-85F8-134D3D7F75BA@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.tools
>Path: TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.tools:29886
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>Peter,
>You are a genius. Thanks a million. I re-installed the MDAC inf and
>everything started working again. Thank you so much. I have a task where
I[vbcol=seagreen]
>REALLY needed to be able to import some xls/csv spreadsheets and was not
>looking forward to having to write the code to do it myself.
>Thanks again.
>--
>Rich
>
>"Peter Yang [MSFT]" wrote:
4.0[vbcol=seagreen]
rights.[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
Server[vbcol=seagreen]
SQL[vbcol=seagreen]
similar[vbcol=seagreen]
to[vbcol=seagreen]
I[vbcol=seagreen]
tools[vbcol=seagreen]
not[vbcol=seagreen]
to[vbcol=seagreen]
to[vbcol=seagreen]
have[vbcol=seagreen]
so[vbcol=seagreen]
data[vbcol=seagreen]
then[vbcol=seagreen]
was[vbcol=seagreen]
valid[vbcol=seagreen]
made[vbcol=seagreen]
the[vbcol=seagreen]
valid[vbcol=seagreen]
made[vbcol=seagreen]
the
>

Excel XSLT?

What's the best way to combine XML INTO an excel document? I'm using the SSIS XML Task to do this.

There are a few options, XSLT, merge etc. Which one will take raw xml (from a database query for instance) and, combined with, an XSL document?, create a spreadsheet in excel?

Please help,

Phil

I'll answer my own question again. The operant type should be XSLT and with the correct XSL you can create an excel document using raw XML from SQL.

Phil

Excel XSLT?

What's the best way to combine XML INTO an excel document? I'm using the SSIS XML Task to do this.

There are a few options, XSLT, merge etc. Which one will take raw xml (from a database query for instance) and, combined with, an XSL document?, create a spreadsheet in excel?

Please help,

Phil

I'll answer my own question again. The operant type should be XSLT and with the correct XSL you can create an excel document using raw XML from SQL.

Phil

Excel task has no output columns

I created an Excel Source and used a query to get the data,i.e

SELECT F1,F2,F3,F4,F5,F6,F7 FROM [Fut Days$A20:G1480]

The query works fine, the preview returns the rows, but SSIS will not generate output Columns nor will it let me manually add them? Am I missing something?

Here is some information that may answer your question:

1. In the Excel Source Editor, navigate to the Columns page.

You can modify the output column names that are listed in the External Column/Output Column table by clicking in the field and typing a new name.|||

Hi,

The problem is there's no output columns listed, and you cannot manually add them. The problem is when I specify a query instead of an entire worksheet, the External Column meta-data is not added like it is when you select a worksheet by name.

Dave

|||

Hi,

Have you tried naming the columns in your excel source as some meaningful names and then try to connect your excel source. It will give you all the names ,after that you can select the ones you need and dont tick the checkboxes of the ones you dont need.

Regards,

Vikram Kansal

excel problem

hi I have an excel sheet with no header row.
so I used DTS package to transfer data to the table,
however, transform data task think my first row is the header column,
as the result, I can never transfer my first row data to the table.
is there a way to do this? maybe I have to use activex?
regardsI'm trying to leave work, but
HDR=Yes;IMEX=1
is something I remember about hte header row.
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:AAAA9CB5-206D-4721-B033-09EBD825100F@.microsoft.com...
> hi I have an excel sheet with no header row.
> so I used DTS package to transfer data to the table,
> however, transform data task think my first row is the header column,
> as the result, I can never transfer my first row data to the table.
> is there a way to do this? maybe I have to use activex?
> regards
>|||where do I put this code?
I tried to put the code in activex,
nothing happen. still same problem..
thanks for helping
"sloan" wrote:

> I'm trying to leave work, but
>
> HDR=Yes;IMEX=1
> is something I remember about hte header row.
>
>
>
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:AAAA9CB5-206D-4721-B033-09EBD825100F@.microsoft.com...
>
>|||Refer this link
http://www.sqldts.com/default.aspx?254
Regards
Sudarshan Selvaraja
"Kevin" wrote:
> where do I put this code?
> I tried to put the code in activex,
> nothing happen. still same problem..
> thanks for helping
>
> "sloan" wrote:
>

Excel Objects From SSIS

I'm trying to use Excel objects from within an SSIS Script Task but I can't figure out how to add a reference the object library. I'm using Excel 2003 and tried Add Reference from the Project menu but there is no entry for Microsoft Excel Object Library or Microsoft Excel 11.0 Object Library. Any suggestions? Thanks.You would need to copy the Excel PIA to a specific folder for it to become available in the VSA Add Reference dialog. http://support.microsoft.com/kb/306149

Note that automation of Office apps from server-side components that often run unattended, like Integration Services packages, is discouraged. For more information, see 257757 INFO: Considerations for Server-Side Automation of Office
http://support.microsoft.com/?id=257757.

-Doug
|||I've got the Excel 2003 PIA installed. It appears to be in the GAC. Using windows explorer, I'm given the option to uninstall it from c:\windows\assembly, but not the option to copy or move it to another folder. Using the command line, when I navigate to c:\windows\assembly, I don't find the file listed in c:\windows\assembly, but instead I find a lot of nested folders. How do I move the PIA to somewhere that VSA can see it?

I'm trying to create the excel file that SSIS will soon be exporting data to. Is there another way to do that (programatically) inside SSIS?

Thanks,

Matthew Martin|||That's a lot of overhead to invoke excel.exe, and discouraged since packages often run unattended.

I would (1) use the Export Wizard to create the destination spreadsheet file initially or (2) save a blank one somewhere as a "template" and use a Script task (for example) to make a copy of it each time the package runs.

I once saw the syntax to create a new Excel file by using the Jet provider (through ADO rather than Excel), but now can't find it. Since the provider normally expects an existing database (naturally), I seem to recall that it wasn't obvious.

Best regards,

-Doug