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 capute "Execution Reasults" to a text file

How can I save all the text in the SSIS "Execution results" pane to a file? (I can copy 1 line at a time, by right-clicking the message of interest, but there must be a better way.)

TIA,

Barker

Look up how to create log providers in BOL. It will let you dump whatever information you want out to a variety of sources.

How to call Oracle Stored Procedure which has an output parameter from SSIS?

I will really appreciate if someone can post step by step process to call an Oracle Stored Proc from SSIS. Here is the Stored Proc Spec:

PROCEDURE Interface_Begin

(p_from_dttmOUT varchar2,

p_error_codeOUT number,

p_error_textOUTvarchar2,

p_proc_nameOUT varchar2);

please check this

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1711335&SiteID=1

http://microsoftdw.blogspot.com/2005/11/parameterized-queries-against-oracle.html

-Nikul

|||

Could you please be more specific? I tried the following:

1) The stored proc spec is as follows:

Procedure testing(myDate OUT varchar2);

2) Created a Data Flow Task.

3) Created 2 variables called myStoredProc & myDate at the package level.

4) In the value for myDate variable i set it to myDate.

5) In the properties for myStoredProc variable i changed the EvaluateAsExpression property to True.

6) In the expression for myStoredProc variable i have the following:

"declare myDate varchar2(50); begin sa.testing(" + @.[User::myDate] + "); end;"

7) Now inside my Data Flow Task i have a Ole Db DataSource connection set to Native OLE DB\Microsoft Ole Db

Provider for Oracle.

8) Data Access Mode set to SQL Command from Variable.

9) The value of the Variable Name is User::myStoredProc.

10) Now when i hit preview i get the following error Message.

No disconnected record set is available for the specified SQL statement.

I am not sure what's wrong here. Could someone help me?

|||Anyone?|||Try doing this from a Execute SQL task, not a Source component. Source components expect a recordset, not an output parameter.|||

Hi there,

As Jwelch said use Execute SQL Task and select Single Row in Recordset option.

I think this will iron out your issue.

Please specify if this does not work.

Thanks

|||

Thanks guys. Now i am able to execute it successfully. But i am getting some junk characters in the Output parameters. Here is the stored proc definition:

create or replace procedure testing(myDate OUT varchar2)
IS
BEGIN
myDate := 'hey';
return;
END;

This should return 'hey' but i am getting this:


)

Any ideas?

How to call a UDF from expression language?

How to call a UDF from expression language?
(This should help work around the lack of reusability in SSIS, and also the problem that the validator doesn't understand length limits, and I have an incorrect validation warning beause the parse doesn't understand REPLICATE correctly, AFAICT.)
You can't do this Perry.

I dare say extensibility of the expression language is on the cards for the next version.

-Jamie

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 call a dot net assembly from ssis

How to call a dot net assembly from ssis.

I have used a script component and went in the design script editor by clicking the "Design Script" button.From their using a object browser trying to load a dll file in references to be used in the ssis application.

But it gives error " Cannot browse the file"

Can any body help me in the same.

http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2341.aspx

-Jamie

how to build properly

Hi,

I'm very new in SSIS. I've created 3 packages in the project. sometimes when i modified the project and save/save-all it, when i tried to build (isn't this used to deploy?), I am being asked by this:

Package 1 has been modified outside the source editor. Do you want to reload it?

When I press on yes, all my modifications were not save. If i answered no, the build process stops. i dunno if this is because the build process is already finished or it was terminated because i chose 'NO'. When i tried to rebuild it again, it will ask me the same question.

What is the proper way to save and build the project? When it says ' do you want to reload it?', does it mean reloading the old copy before modification?

Thanks!

cherrie

Yes, reloading means loading the version from outside VS - and that will not be the version you are editing in VS, so you lose changes.

I wonder if you have more than copy open? Or do you have some other process such as backup or offline file synchronization which may be touching the files while they are open?

You can work round the problem by not reloading the file when you get this message.

Donald

|||

Ic. Thanks a lot!

cherrie