how to change data types in Excel source file?

I'm getting a bit lost in SSIS. I've got an Excel source file that I'm trying to load into a table. I keep getting validation errors that warn about not being able to convert between unicode and non-unicode string data types.

I'm trying figure out where I have to change this and am frankly confused. It seems SSIS is selecting various columns as unicode/WSTR data types, but I want them to import as regular string types.

On the Data Flow tab in SSIS, I right-click on the source Data Flow component (the Excel file) and select Show Advanced Editor. Then on the last tab, Input and Output Properties, there's a tree view for the Excel output. There are "External Columns" and "Output Columns" containers in the tree view.

I tried setting some of these but they don't seem to "take". Do I need to change the data type for each column under both the External and Output columns?

That seems like a lot of work! And, as I say, I tried setting some, but I still got the same validation errors. So, then I go back to this spot (Advanced Editor -> Input and Output Properties tab) and my changes seem to have been lost.

Any help would be appreciated!

The recommended way for doing this is to use the Data Conversion Transform and explicitly specify your data type conversions there.

Try using the Import/Export wizard to generate a sample package for this.

|||

Hi Bob,

What is your destination? Is it SQL Server or MS Access or any other database? If it is SQL Server, declare the varchar column as nvarchar to avoid this kind of conversion errors. But if you are importing data from Flat File, in the Flat File Connection Manager you have an option to set unicode characters by means of selecting the "Unicode" check box.

If it is Excel Source, then you need to change the datatype in your database. I don't find any other solution for this. Is anybody having any other solution, it is well and good.

Thanks & Regards,

Prakash Srinivasan.

|||

I am going from Excel to a SQL table. Changing the data type on the SQL type isn't really going to be a reasonable solution, essentially doubling (or halving, depending on how you look at it) storage requirements.

From the SSIS tutorials, I know you can change the data type on the Flat File connection manager and am really struggling to understand why you can't do this w/ an Excel file. In fact, the Excel provider has "picked" the wrong data type in many cases... it "saw" some numbers in a column and decided it was a numeric field, but it's wrong, it's a string field, and in fact some of the data has an alpha in it.

So, I'm now back to trying to figure out how to sort this out when setting up the source file. I believe I can use a data conversion transformation, but I just don't understand why I can't do this at the source, as it were. If you have to use a data transformation, then the Excel provider should just bring in everything as a generic string and not try to cast it at all for you. And why shouldn't I then be able to tell it to "default" to a non-unicode string data type rather than unicode?

Also I'm all the more wondering what the "Input and Output Properties" tab in the Advanced Editor is all abou then? When do you use the External columns vs Output columns, vs both?

BOL does not seem to offer any meaningful information here.

|||I have the exact same issue. Row one in the excel file is numeric (20), many of the rest are text (20A, 20B etc). The excel connector forces this to a type of double, and won't let me convert to text, even if it did, it strips out the non-double values and gives me nulls. Same effect in the stored procedure that drove me to try and use SSIS. This is so easy outside of Excel! There has to something to allow you to override what Excel "thinks" the datatype is right?

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\temp\jjt.xls', 'SELECT * FROM [Jobs$]')

how to change data types in Excel source file?

I'm getting a bit lost in SSIS. I've got an Excel source file that I'm trying to load into a table. I keep getting validation errors that warn about not being able to convert between unicode and non-unicode string data types.

I'm trying figure out where I have to change this and am frankly confused. It seems SSIS is selecting various columns as unicode/WSTR data types, but I want them to import as regular string types.

On the Data Flow tab in SSIS, I right-click on the source Data Flow component (the Excel file) and select Show Advanced Editor. Then on the last tab, Input and Output Properties, there's a tree view for the Excel output. There are "External Columns" and "Output Columns" containers in the tree view.

I tried setting some of these but they don't seem to "take". Do I need to change the data type for each column under both the External and Output columns?

That seems like a lot of work! And, as I say, I tried setting some, but I still got the same validation errors. So, then I go back to this spot (Advanced Editor -> Input and Output Properties tab) and my changes seem to have been lost.

Any help would be appreciated!

The recommended way for doing this is to use the Data Conversion Transform and explicitly specify your data type conversions there.

Try using the Import/Export wizard to generate a sample package for this.

|||

Hi Bob,

What is your destination? Is it SQL Server or MS Access or any other database? If it is SQL Server, declare the varchar column as nvarchar to avoid this kind of conversion errors. But if you are importing data from Flat File, in the Flat File Connection Manager you have an option to set unicode characters by means of selecting the "Unicode" check box.

If it is Excel Source, then you need to change the datatype in your database. I don't find any other solution for this. Is anybody having any other solution, it is well and good.

Thanks & Regards,

Prakash Srinivasan.

|||

I am going from Excel to a SQL table. Changing the data type on the SQL type isn't really going to be a reasonable solution, essentially doubling (or halving, depending on how you look at it) storage requirements.

From the SSIS tutorials, I know you can change the data type on the Flat File connection manager and am really struggling to understand why you can't do this w/ an Excel file. In fact, the Excel provider has "picked" the wrong data type in many cases... it "saw" some numbers in a column and decided it was a numeric field, but it's wrong, it's a string field, and in fact some of the data has an alpha in it.

So, I'm now back to trying to figure out how to sort this out when setting up the source file. I believe I can use a data conversion transformation, but I just don't understand why I can't do this at the source, as it were. If you have to use a data transformation, then the Excel provider should just bring in everything as a generic string and not try to cast it at all for you. And why shouldn't I then be able to tell it to "default" to a non-unicode string data type rather than unicode?

Also I'm all the more wondering what the "Input and Output Properties" tab in the Advanced Editor is all abou then? When do you use the External columns vs Output columns, vs both?

BOL does not seem to offer any meaningful information here.

|||I have the exact same issue. Row one in the excel file is numeric (20), many of the rest are text (20A, 20B etc). The excel connector forces this to a type of double, and won't let me convert to text, even if it did, it strips out the non-double values and gives me nulls. Same effect in the stored procedure that drove me to try and use SSIS. This is so easy outside of Excel! There has to something to allow you to override what Excel "thinks" the datatype is right?

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\temp\jjt.xls', 'SELECT * FROM [Jobs$]')

How to change ConnectionString programmaticaly for report

Hi!
Is there any way to change the connecting string of a report data source
programmatically at run time. Actually my problem is that I have an ASP.NET
application and connection string is stored in Web.Config file. I have
multiple copies of databases hosted on different SQL Server and I update my
web.config connection string to switch between these databases. I just want
to use this connection string for my reports also. I am using SQL Server
authentication and user name and passwords are different for different
servers.
Please help me as this is becoming a show stopper for my application.
Regards,
NamwarOn Jun 8, 3:33 pm, "Namwar Rizvi" <nam...@.hotmail.com> wrote:
> Hi!
> Is there any way to change the connecting string of a report data source
> programmatically at run time. Actually my problem is that I have an ASP.NET
> application and connection string is stored in Web.Config file. I have
> multiple copies of databases hosted on different SQL Server and I update my
> web.config connection string to switch between these databases. I just want
> to use this connection string for my reports also. I am using SQL Server
> authentication and user name and passwords are different for different
> servers.
> Please help me as this is becoming a show stopper for my application.
> Regards,
> Namwar
This link should be helpful.
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/53e96ed5cde45213/bbe61adc20aeeb87?lnk=st&q=dynamic+datasource+reporting+services&rnum=1#bbe61adc20aeeb87
Regards,
Enrique Martinez
Sr. Software Consultant|||On Jun 9, 3:50 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Jun 8, 3:33 pm, "Namwar Rizvi" <nam...@.hotmail.com> wrote:
> > Hi!
> > Is there any way tochangethe connecting string of areportdata source
> > programmatically at run time. Actually my problem is that I have an ASP.NET
> > application and connection string is stored in Web.Config file. I have
> > multiple copies of databases hosted on different SQL Server and I update my
> > web.config connection string to switch between these databases. I just want
> > to use this connection string for my reports also. I am using SQL Server
> > authentication and user name and passwords are different for different
> > servers.
> > Please help me as this is becoming a show stopper for my application.
> > Regards,
> > Namwar
> This link should be helpful.http://groups.google.com/group/microsoft.public.sqlserver.reportingsv...
> Regards,
> Enrique Martinez
> Sr. Software Consultant
I received your email. The only other thing I can think of is to
create the RDL file and/or the datasource file for the report
programmatically via a custom ASP.NET application.|||Have you tried using an expression as a datasource, as described here
http://msdn2.microsoft.com/en-us/library/ms156450.aspx
... look for the section on "dynamic datasources" or "expressions" or
something like that.
I do understand that you want to read your stuff out of the web config file.
But there are several ways you probably could handle this -- without
programmatically altering the RDL file -- assuming the basic idea of a
datasource based on an expression will work for you. To start with, how are
the reports actually invoked (in the asp.net application? or elsewhere?) and
what access does reporting code have to the web.config file and its
contents?
>L<
"Namwar Rizvi" <namwar@.hotmail.com> wrote in message
news:efkKU0hqHHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hi!
> Is there any way to change the connecting string of a report data source
> programmatically at run time. Actually my problem is that I have an
> ASP.NET application and connection string is stored in Web.Config file. I
> have multiple copies of databases hosted on different SQL Server and I
> update my web.config connection string to switch between these databases.
> I just want to use this connection string for my reports also. I am using
> SQL Server authentication and user name and passwords are different for
> different servers.
> Please help me as this is becoming a show stopper for my application.
> Regards,
> Namwar
>

how to call a stored procedure in SSIS

I have to transfer data from source to destination using stored procedures result set. There might be some more transformation needed to store the final result in the destination table.

Appreciate an early feedback.

Qadir Syed

Just create a new instance of "OLE DB Command".

Pick and choose your connection, and call the command as exec <procedure name>

|||"Oledb command" Source executes stored procedures.But it does not recognize the output of the stored procedure.
If I have a select statement at the end of the Stored procedure that returns me some columns,then those columns are not recognized by "OLEDB Command" as out put columns.

Is there any advice for executing such stored procedure?
|||Use a no-op select statement to "declare" metadata to the pipeline. Since stored procedures don't publish rowset meta-data like tables,views and table-valued functions, the first select statement of a stored procedure is used by the SQLClient OLEDB provider to determine column metadata.

Code Snippet

CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON

IF 1 = 0
BEGIN
SELECT CAST(1 as smallint) as Fake
-- Publish metadata
END

-- do real work starting here
DECLARE @.x char(1)
SET @.x = (SELECT '1')

SELECT cast(@.x as smallint)

RETURN

|||jaegd - thanks!!!|||It does not work If there are more than one rows are coming out of stored procedure.

|||I got it solved on my end by replacing all temporary tables by temporary variables.
|||

Please give an example of what doesn't work for you.

|||In the above post it should be table variable not temporary variable.

Conclusion:
It was table variable that Used instead of temporary table.

|||Hey try with the example below

CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON
CREATE TABLE #test(
[id] [int] NULL,
[Name] [nchar](10) NULL,
[SirName] [nchar](10) NULL
) ON [PRIMARY]

INSERT INTO #test
SELECT '1','A','Z' union all select '2','b','y'

select id,name,SirName
from #test
drop table #test
RETURN

Please let me know the result.
|||

With a local temp table created in the stored procedure, use a no-op select statement to "declare" metadata to the pipeline.

Code Snippet

IF OBJECT_ID('[dbo].[GenMetadata]', 'P') IS NOT NULL

DROP PROCEDURE [dbo].[GenMetadata]

GO

CREATE PROCEDURE [dbo].[GenMetadata]

AS

SET NOCOUNT ON

IF 1 = 0

BEGIN

-- Publish metadata

SELECT CAST(NULL AS INT) AS id,

CAST(NULL AS NCHAR(10)) AS [Name],

CAST(NULL AS NCHAR(10)) AS SirName

END

-- Do real work starting here

CREATE TABLE #test

(

[id] [int] NULL,

[Name] [nchar](10) NULL,

[SirName] [nchar](10) NULL

)

INSERT INTO #test

SELECT '1',

'A',

'Z'

UNION ALL

SELECT '2',

'b',

'y'

SELECTid,

[Name],

SirName

FROM#test

DROP TABLE #test

RETURN

GO

|||Hi Thank you very much..

It is now working fine for the changes you suggested..
|||Hello,

With the procedure you have told,SSIS package able to detect output of the stored procedure.
But there another problem introduced bcz of this procedure.Where According to your procedure the SP returns two datasets.
First dataset having 1 row and this is as a result of First select statement.
Second dataset bcz of our actual select query.

So SSIS chooses first dataset,So returns only one row having Null values for all columns.

How to overcome from this?
|||

Post a sproc, or usage of the above sproc, which demonstrates the problem (e.g. OPENQUERY, EXEC, INSERT EXEC, so on...)

how to call a stored procedure in SSIS

I have to transfer data from source to destination using stored procedures result set. There might be some more transformation needed to store the final result in the destination table.

Appreciate an early feedback.

Qadir Syed

Just create a new instance of "OLE DB Command".

Pick and choose your connection, and call the command as exec <procedure name>

|||"Oledb command" Source executes stored procedures.But it does not recognize the output of the stored procedure.
If I have a select statement at the end of the Stored procedure that returns me some columns,then those columns are not recognized by "OLEDB Command" as out put columns.

Is there any advice for executing such stored procedure?
|||Use a no-op select statement to "declare" metadata to the pipeline. Since stored procedures don't publish rowset meta-data like tables,views and table-valued functions, the first select statement of a stored procedure is used by the SQLClient OLEDB provider to determine column metadata.

Code Snippet

CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON

IF 1 = 0
BEGIN
SELECT CAST(1 as smallint) as Fake
-- Publish metadata
END

-- do real work starting here
DECLARE @.x char(1)
SET @.x = (SELECT '1')

SELECT cast(@.x as smallint)

RETURN

|||jaegd - thanks!!!|||It does not work If there are more than one rows are coming out of stored procedure.

|||I got it solved on my end by replacing all temporary tables by temporary variables.
|||

Please give an example of what doesn't work for you.

|||In the above post it should be table variable not temporary variable.

Conclusion:
It was table variable that Used instead of temporary table.

|||Hey try with the example below

CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON
CREATE TABLE #test(
[id] [int] NULL,
[Name] [nchar](10) NULL,
[SirName] [nchar](10) NULL
) ON [PRIMARY]

INSERT INTO #test
SELECT '1','A','Z' union all select '2','b','y'

select id,name,SirName
from #test
drop table #test
RETURN

Please let me know the result.
|||

With a local temp table created in the stored procedure, use a no-op select statement to "declare" metadata to the pipeline.

Code Snippet

IF OBJECT_ID('[dbo].[GenMetadata]', 'P') IS NOT NULL

DROP PROCEDURE [dbo].[GenMetadata]

GO

CREATE PROCEDURE [dbo].[GenMetadata]

AS

SET NOCOUNT ON

IF 1 = 0

BEGIN

-- Publish metadata

SELECT CAST(NULL AS INT) AS id,

CAST(NULL AS NCHAR(10)) AS [Name],

CAST(NULL AS NCHAR(10)) AS SirName

END

-- Do real work starting here

CREATE TABLE #test

(

[id] [int] NULL,

[Name] [nchar](10) NULL,

[SirName] [nchar](10) NULL

)

INSERT INTO #test

SELECT '1',

'A',

'Z'

UNION ALL

SELECT '2',

'b',

'y'

SELECTid,

[Name],

SirName

FROM#test

DROP TABLE #test

RETURN

GO

|||Hi Thank you very much..

It is now working fine for the changes you suggested..
|||Hello,

With the procedure you have told,SSIS package able to detect output of the stored procedure.
But there another problem introduced bcz of this procedure.Where According to your procedure the SP returns two datasets.
First dataset having 1 row and this is as a result of First select statement.
Second dataset bcz of our actual select query.

So SSIS chooses first dataset,So returns only one row having Null values for all columns.

How to overcome from this?
|||

Post a sproc, or usage of the above sproc, which demonstrates the problem (e.g. OPENQUERY, EXEC, INSERT EXEC, so on...)

how to call a stored procedure in SSIS

I have to transfer data from source to destination using stored procedures result set. There might be some more transformation needed to store the final result in the destination table.

Appreciate an early feedback.

Qadir Syed

Just create a new instance of "OLE DB Command".

Pick and choose your connection, and call the command as exec <procedure name>

|||"Oledb command" Source executes stored procedures.But it does not recognize the output of the stored procedure.
If I have a select statement at the end of the Stored procedure that returns me some columns,then those columns are not recognized by "OLEDB Command" as out put columns.

Is there any advice for executing such stored procedure?
|||Use a no-op select statement to "declare" metadata to the pipeline. Since stored procedures don't publish rowset meta-data like tables,views and table-valued functions, the first select statement of a stored procedure is used by the SQLClient OLEDB provider to determine column metadata.

Code Snippet

CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON

IF 1 = 0
BEGIN
SELECT CAST(1 as smallint) as Fake
-- Publish metadata
END

-- do real work starting here
DECLARE @.x char(1)
SET @.x = (SELECT '1')

SELECT cast(@.x as smallint)

RETURN

|||jaegd - thanks!!!|||It does not work If there are more than one rows are coming out of stored procedure.

|||I got it solved on my end by replacing all temporary tables by temporary variables.
|||

Please give an example of what doesn't work for you.

|||In the above post it should be table variable not temporary variable.

Conclusion:
It was table variable that Used instead of temporary table.

|||Hey try with the example below

CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON
CREATE TABLE #test(
[id] [int] NULL,
[Name] [nchar](10) NULL,
[SirName] [nchar](10) NULL
) ON [PRIMARY]

INSERT INTO #test
SELECT '1','A','Z' union all select '2','b','y'

select id,name,SirName
from #test
drop table #test
RETURN

Please let me know the result.
|||

With a local temp table created in the stored procedure, use a no-op select statement to "declare" metadata to the pipeline.

Code Snippet

IF OBJECT_ID('[dbo].[GenMetadata]', 'P') IS NOT NULL

DROP PROCEDURE [dbo].[GenMetadata]

GO

CREATE PROCEDURE [dbo].[GenMetadata]

AS

SET NOCOUNT ON

IF 1 = 0

BEGIN

-- Publish metadata

SELECT CAST(NULL AS INT) AS id,

CAST(NULL AS NCHAR(10)) AS [Name],

CAST(NULL AS NCHAR(10)) AS SirName

END

-- Do real work starting here

CREATE TABLE #test

(

[id] [int] NULL,

[Name] [nchar](10) NULL,

[SirName] [nchar](10) NULL

)

INSERT INTO #test

SELECT '1',

'A',

'Z'

UNION ALL

SELECT '2',

'b',

'y'

SELECTid,

[Name],

SirName

FROM#test

DROP TABLE #test

RETURN

GO

|||Hi Thank you very much..

It is now working fine for the changes you suggested..
|||Hello,

With the procedure you have told,SSIS package able to detect output of the stored procedure.
But there another problem introduced bcz of this procedure.Where According to your procedure the SP returns two datasets.
First dataset having 1 row and this is as a result of First select statement.
Second dataset bcz of our actual select query.

So SSIS chooses first dataset,So returns only one row having Null values for all columns.

How to overcome from this?
|||

Post a sproc, or usage of the above sproc, which demonstrates the problem (e.g. OPENQUERY, EXEC, INSERT EXEC, so on...)

how to call a stored procedure in SSIS

I have to transfer data from source to destination using stored procedures result set. There might be some more transformation needed to store the final result in the destination table.

Appreciate an early feedback.

Qadir Syed

Just create a new instance of "OLE DB Command".

Pick and choose your connection, and call the command as exec <procedure name>

|||"Oledb command" Source executes stored procedures.But it does not recognize the output of the stored procedure.
If I have a select statement at the end of the Stored procedure that returns me some columns,then those columns are not recognized by "OLEDB Command" as out put columns.

Is there any advice for executing such stored procedure?
|||Use a no-op select statement to "declare" metadata to the pipeline. Since stored procedures don't publish rowset meta-data like tables,views and table-valued functions, the first select statement of a stored procedure is used by the SQLClient OLEDB provider to determine column metadata.

Code Snippet

CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON

IF 1 = 0
BEGIN
SELECT CAST(1 as smallint) as Fake
-- Publish metadata
END

-- do real work starting here
DECLARE @.x char(1)
SET @.x = (SELECT '1')

SELECT cast(@.x as smallint)

RETURN

|||jaegd - thanks!!!|||It does not work If there are more than one rows are coming out of stored procedure.

|||I got it solved on my end by replacing all temporary tables by temporary variables.
|||

Please give an example of what doesn't work for you.

|||In the above post it should be table variable not temporary variable.

Conclusion:
It was table variable that Used instead of temporary table.

|||Hey try with the example below

CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON
CREATE TABLE #test(
[id] [int] NULL,
[Name] [nchar](10) NULL,
[SirName] [nchar](10) NULL
) ON [PRIMARY]

INSERT INTO #test
SELECT '1','A','Z' union all select '2','b','y'

select id,name,SirName
from #test
drop table #test
RETURN

Please let me know the result.
|||

With a local temp table created in the stored procedure, use a no-op select statement to "declare" metadata to the pipeline.

Code Snippet

IF OBJECT_ID('[dbo].[GenMetadata]', 'P') IS NOT NULL

DROP PROCEDURE [dbo].[GenMetadata]

GO

CREATE PROCEDURE [dbo].[GenMetadata]

AS

SET NOCOUNT ON

IF 1 = 0

BEGIN

-- Publish metadata

SELECT CAST(NULL AS INT) AS id,

CAST(NULL AS NCHAR(10)) AS [Name],

CAST(NULL AS NCHAR(10)) AS SirName

END

-- Do real work starting here

CREATE TABLE #test

(

[id] [int] NULL,

[Name] [nchar](10) NULL,

[SirName] [nchar](10) NULL

)

INSERT INTO #test

SELECT '1',

'A',

'Z'

UNION ALL

SELECT '2',

'b',

'y'

SELECTid,

[Name],

SirName

FROM#test

DROP TABLE #test

RETURN

GO

|||Hi Thank you very much..

It is now working fine for the changes you suggested..
|||Hello,

With the procedure you have told,SSIS package able to detect output of the stored procedure.
But there another problem introduced bcz of this procedure.Where According to your procedure the SP returns two datasets.
First dataset having 1 row and this is as a result of First select statement.
Second dataset bcz of our actual select query.

So SSIS chooses first dataset,So returns only one row having Null values for all columns.

How to overcome from this?
|||

Post a sproc, or usage of the above sproc, which demonstrates the problem (e.g. OPENQUERY, EXEC, INSERT EXEC, so on...)

how to call a stored procedure in SSIS

I have to transfer data from source to destination using stored procedures result set. There might be some more transformation needed to store the final result in the destination table.

Appreciate an early feedback.

Qadir Syed

Just create a new instance of "OLE DB Command".

Pick and choose your connection, and call the command as exec <procedure name>

|||"Oledb command" Source executes stored procedures.But it does not recognize the output of the stored procedure.
If I have a select statement at the end of the Stored procedure that returns me some columns,then those columns are not recognized by "OLEDB Command" as out put columns.

Is there any advice for executing such stored procedure?|||Use a no-op select statement to "declare" metadata to the pipeline. Since stored procedures don't publish rowset meta-data like tables,views and table-valued functions, the first select statement of a stored procedure is used by the SQLClient OLEDB provider to determine column metadata.

Code Snippet

CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON

IF 1 = 0
BEGIN
SELECT CAST(1 as smallint) as Fake
-- Publish metadata
END

-- do real work starting here
DECLARE @.x char(1)
SET @.x = (SELECT '1')

SELECT cast(@.x as smallint)

RETURN

|||jaegd - thanks!!!|||It does not work If there are more than one rows are coming out of stored procedure.|||I got it solved on my end by replacing all temporary tables by temporary variables.|||

Please give an example of what doesn't work for you.

|||In the above post it should be table variable not temporary variable.

Conclusion:
It was table variable that Used instead of temporary table.|||Hey try with the example below

CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON
CREATE TABLE #test(
[id] [int] NULL,
[Name] [nchar](10) NULL,
[SirName] [nchar](10) NULL
) ON [PRIMARY]

INSERT INTO #test
SELECT '1','A','Z' union all select '2','b','y'

select id,name,SirName
from #test
drop table #test
RETURN

Please let me know the result.|||

With a local temp table created in the stored procedure, use a no-op select statement to "declare" metadata to the pipeline.

Code Snippet

IF OBJECT_ID('[dbo].[GenMetadata]', 'P') IS NOT NULL

DROP PROCEDURE [dbo].[GenMetadata]

GO

CREATE PROCEDURE [dbo].[GenMetadata]

AS

SET NOCOUNT ON

IF 1 = 0

BEGIN

-- Publish metadata

SELECT CAST(NULL AS INT) AS id,

CAST(NULL AS NCHAR(10)) AS [Name],

CAST(NULL AS NCHAR(10)) AS SirName

END

-- Do real work starting here

CREATE TABLE #test

(

[id] [int] NULL,

[Name] [nchar](10) NULL,

[SirName] [nchar](10) NULL

)

INSERT INTO #test

SELECT '1',

'A',

'Z'

UNION ALL

SELECT '2',

'b',

'y'

SELECTid,

[Name],

SirName

FROM#test

DROP TABLE #test

RETURN

GO

|||Hi Thank you very much..

It is now working fine for the changes you suggested..|||Hello,

With the procedure you have told,SSIS package able to detect output of the stored procedure.
But there another problem introduced bcz of this procedure.Where According to your procedure the SP returns two datasets.
First dataset having 1 row and this is as a result of First select statement.
Second dataset bcz of our actual select query.

So SSIS chooses first dataset,So returns only one row having Null values for all columns.

How to overcome from this?|||

Post a sproc, or usage of the above sproc, which demonstrates the problem (e.g. OPENQUERY, EXEC, INSERT EXEC, so on...)

how to call a stored procedure in SSIS

I have to transfer data from source to destination using stored procedures result set. There might be some more transformation needed to store the final result in the destination table.

Appreciate an early feedback.

Qadir Syed

Just create a new instance of "OLE DB Command".

Pick and choose your connection, and call the command as exec <procedure name>

|||"Oledb command" Source executes stored procedures.But it does not recognize the output of the stored procedure.
If I have a select statement at the end of the Stored procedure that returns me some columns,then those columns are not recognized by "OLEDB Command" as out put columns.

Is there any advice for executing such stored procedure?
|||Use a no-op select statement to "declare" metadata to the pipeline. Since stored procedures don't publish rowset meta-data like tables,views and table-valued functions, the first select statement of a stored procedure is used by the SQLClient OLEDB provider to determine column metadata.

Code Snippet

CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON

IF 1 = 0
BEGIN
SELECT CAST(1 as smallint) as Fake
-- Publish metadata
END

-- do real work starting here
DECLARE @.x char(1)
SET @.x = (SELECT '1')

SELECT cast(@.x as smallint)

RETURN

|||jaegd - thanks!!!|||It does not work If there are more than one rows are coming out of stored procedure.

|||I got it solved on my end by replacing all temporary tables by temporary variables.
|||

Please give an example of what doesn't work for you.

|||In the above post it should be table variable not temporary variable.

Conclusion:
It was table variable that Used instead of temporary table.

|||Hey try with the example below

CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON
CREATE TABLE #test(
[id] [int] NULL,
[Name] [nchar](10) NULL,
[SirName] [nchar](10) NULL
) ON [PRIMARY]

INSERT INTO #test
SELECT '1','A','Z' union all select '2','b','y'

select id,name,SirName
from #test
drop table #test
RETURN

Please let me know the result.
|||

With a local temp table created in the stored procedure, use a no-op select statement to "declare" metadata to the pipeline.

Code Snippet

IF OBJECT_ID('[dbo].[GenMetadata]', 'P') IS NOT NULL

DROP PROCEDURE [dbo].[GenMetadata]

GO

CREATE PROCEDURE [dbo].[GenMetadata]

AS

SET NOCOUNT ON

IF 1 = 0

BEGIN

-- Publish metadata

SELECT CAST(NULL AS INT) AS id,

CAST(NULL AS NCHAR(10)) AS [Name],

CAST(NULL AS NCHAR(10)) AS SirName

END

-- Do real work starting here

CREATE TABLE #test

(

[id] [int] NULL,

[Name] [nchar](10) NULL,

[SirName] [nchar](10) NULL

)

INSERT INTO #test

SELECT '1',

'A',

'Z'

UNION ALL

SELECT '2',

'b',

'y'

SELECTid,

[Name],

SirName

FROM#test

DROP TABLE #test

RETURN

GO

|||Hi Thank you very much..

It is now working fine for the changes you suggested..
|||Hello,

With the procedure you have told,SSIS package able to detect output of the stored procedure.
But there another problem introduced bcz of this procedure.Where According to your procedure the SP returns two datasets.
First dataset having 1 row and this is as a result of First select statement.
Second dataset bcz of our actual select query.

So SSIS chooses first dataset,So returns only one row having Null values for all columns.

How to overcome from this?
|||

Post a sproc, or usage of the above sproc, which demonstrates the problem (e.g. OPENQUERY, EXEC, INSERT EXEC, so on...)

How to build a Management Studio plugin?

Our shop uses subversion for its source control, and since I can't find a SVN plugin for Management Studio I thought I would build one for fun. However, I am unable to find any documentation regarding how to do this or extending the Management Studio environment in general.

Is this documentation available? If so, where?

Thanks

It's accessible through the SCC API. The documentation on this is available through the Visual Studio Industry Partner program. http://msdn2.microsoft.com/en-us/vstudio/aa700860.aspx

Others are using CVS SCC proxy to integrate Subversion and Management Studio. You can find information and download this from: http://www.pushok.com/soft_cvs_proxy.php

-Sue

|||

This is excellent information. The Affiliate level of the VSIP program looks promising. Unfortunately, the site is down for maintenance until 8/15/2007.

Also, I found an updated link for the Push OK SVN plugin: http://www.pushok.com/soft_svn.php

Thanks a ton!

Ray