November 16, 2015 at 10:48 am
I created a stored proc that is used to pass parameters and run an SSIS Pacakge. This proc initializes the execution, sets the parameters and finally starts the package. One of the issues I am running into is setting the parameters. I am using Catalog.set_execution_parameter_Value in the SSISDB database. This is using a table called internal.Data_Type_Mapping which is a mapping between SQL data types and SSIS datatypes. This table is missing the mapping between String and varchar. I’m not sure if this is a bug or I am missing something. As part of my deployment, I want to add it, but I’m a little leery since this is a system table. Does anyone know anything about this?
The query below only returns Char, NChar, and NVarchar.
select * from ssisdb.internal.data_type_mapping
where ssis_data_type = 'String'
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 16, 2015 at 11:26 am
Not sure whether this will work for you, but I do something similar.
I have a project deployed to SSISDB and have configured it so that certain params in the project's packages are picked up from an SSISDB environment.
Once you have this in place, it's easy to add something to your proc which sets the values of the environment variables before creating the execution, using
SSISDB.catalog.set_environment_variable_value
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 16, 2015 at 12:00 pm
That's the procedure I am calling. I explicitly created a nvarchar variable and passed it into this proc. When this line runs, it returns Varchar instead of Nvarchar
SET @variable_type = CONVERT(nvarchar(128), SQL_VARIANT_PROPERTY(@value, 'BaseType'))
then when it runs this from that proc, I get the error
SET @data_type = (SELECT [type] FROM [internal].[environment_variables]
WHERE [environment_id] = @environment_id AND [name] = @variable_name)
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 16, 2015 at 12:04 pm
Mike01 (11/16/2015)
That's the procedure I am calling. I explicitly created a nvarchar variable and passed it into this proc. When this line runs, it returns Varchar instead of Nvarchar
SET @variable_type = CONVERT(nvarchar(128), SQL_VARIANT_PROPERTY(@value, 'BaseType'))then when it runs this from that proc, I get the error
SET @data_type = (SELECT [type] FROM [internal].[environment_variables]WHERE [environment_id] = @environment_id AND [name] = @variable_name)
But (iirc) the datatype required in the environment is SQL_VARIANT.
I don't understand exactly where you are having a problem. Why are you worried about types, can you explain more?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 16, 2015 at 12:32 pm
I have about 15 variables for one package. I created a stored proc that takes all these variables. Then I call 3 procs in the SSISDB database:
1) Catalog.Create_Execution - this gives me an execution id
2) Catalog.set_execution_parameter_Value - for each parameter to set the value
3) Catalog.Start_Execution - actually start the package
it is the second step that is failing. I stepped through my proc that is calling it and stepped into that. That is where I found the issue that the String to Varchar mapping was missing in the data_type_Mapping table. I created a simple SSIS Package that will create a folder if it is not there. The Parameter is ArchiveFolder. I deployed it to the server, then ran the below to set the variable and execute the package.
declare @ArchiveFolder nvarchar(100) = '\\ServerName\c$\test',
@FolderName nvarchar(100) = 'test',
@PackageName nvarchar(100) = 'test.dtsx',
@ProjectName nvarchar(100) = 'test',
@ExecutionID bigint,
@ObjectType int = 20,
@Use32BitRunTime int = 0,
@ReferenceID int,
@Rows int,
@CurRow int = 1,
@ParameterName varchar(100) = 'ArchiveFolder',
@ParameterValue varchar(100),
@ErrorMessage varchar(500)
begin try
set nocount on
exec SSISDB.Catalog.Create_Execution @Folder_Name = @FolderName,
@Project_Name = @ProjectName,
@Package_Name = @PackageName,
@Reference_ID = @ReferenceID,
@Use32BitRunTime = @Use32BitRunTime,
@Execution_id = @ExecutionID output
set @ParameterValue = @ArchiveFolder
exec SSISDB.Catalog.set_execution_parameter_Value @Execution_ID = @ExecutionID,
@Object_Type= @ObjectType,
@Parameter_name = @ParameterName,
@Parameter_Value = @ParameterValue
exec SSISDB.Catalog.Start_Execution @Execution_ID = @ExecutionID
end try
begin catch
set @ErrorMessage = @ParameterName + ' - ' + Error_Message()
RaisError(@ErrorMessage, 16, 1)
return
end catch
the first time I ran it without the value in the data_Type_Mapping table, i got the error:
Msg 50000, Level 16, State 1, Line 36
ArchiveFolder - The data type of the input value is not compatible with the data type of the 'String'.
when I ran this insert, the folder was created successfully:
if not exists(select 1 from ssisdb.[internal].[data_type_mapping]
where ssis_data_type = 'String' and sql_data_type = 'varchar')
begin
insert into ssisdb.[internal].[data_type_mapping] (ssis_data_type, sql_data_type)
values ('String', 'varchar')
end
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 16, 2015 at 1:04 pm
OK, now I understand better. I get round that limitation (as suggested in previous posts) in a different way:
use SSISDB
go
exec catalog.set_environment_variable_value @folder_name = N'_environments', -- nvarchar(128)
@environment_name = N'Test', -- nvarchar(128)
@variable_name = N'ArchiveFolder', -- nvarchar(128)
@value = 'c:\temp' -- sql_variant
--Fails
exec catalog.set_environment_variable_value @folder_name = N'_environments', -- nvarchar(128)
@environment_name = N'Test', -- nvarchar(128)
@variable_name = N'ArchiveFolder', -- nvarchar(128)
@value = N'c:\temp' -- sql_variant
--Succeeds
Your solution sounds reasonable. But then you start to wonder: 'why was this ever omitted?'
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 16, 2015 at 1:33 pm
That's what my concern was too. The problem in your solution is that Archive Folder is in a parameter already. I put the parms and values in a table variable, then loop through that to assign the values and call the Catalog.set_execution_parameter_Value proc. I have logic that if it is an integer, then it passes an integer value instead, but otherwise they are already nvarchar values.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply