Iterate a Variable

Hi Guys,

I need to design this SSIS migration package to migrate data.

In a Execute SQL Task, I need to get a full result set and assign it to an variable, such as v_collection;

The SQL statement can be as simple as : select primary_key from a_table;

After that I have a ForEach Loop container, that consumes the variable, and assign each iteration to another variable, such as v_iter, the type of v_iter is DT_I4, because the primary key is a long integer

The problem is: in Oracle, the primary key is NUMERIC(10,0) and in SQL it is int.

I can not assign a NUMERIC(10,0) to an variable of DT_I4, but if I change the variable definition to DT_NUMERIC, then it would not work for SQL.

Anyone knows how to fix this?

I was thinking to add a Script Task between the Execute SQL Task and the Foreach Loop, and somehow access the collection variable, v_collection, and manually convert the value to a DT_IT, then repopulate another collection variable, v_collection_I4 with Integers, and force the Foreach Loop to use v_collection_I4 collection variable.

Will this work? If yes, how? :)

Thanks a lot!

Wenbiao

Hi Wenbiao,

My general principle with variables in SSIS is to use String datatype where at all possible. So following your example above, I would change your SQL statement along the lines of:

select convert ( varchar, primary_key) as primary_key from a_table;

The variable datatype would then be a String.

Then during your Dataflow within your loop, you can use a Derived Column transformation to perform a Type Cast to whatever datatype you need before you deliver the data.

Good luck.

Mike

|||As an alternative, you could create two variables, the first one DT_NUMERIC, the second DT_I4. Set the first one with the numeric value from Oracle in your For Each Loop. Set the second one to evaluate as an expression, and cast the first variable to DT_I4 in the expression. It will be updated each time the first variable changes, and you can use it in the tasks in the loop.

Iterate a Variable

Hi Guys,

I need to design this SSIS migration package to migrate data.

In a Execute SQL Task, I need to get a full result set and assign it to an variable, such as v_collection;

The SQL statement can be as simple as : select primary_key from a_table;

After that I have a ForEach Loop container, that consumes the variable, and assign each iteration to another variable, such as v_iter, the type of v_iter is DT_I4, because the primary key is a long integer

The problem is: in Oracle, the primary key is NUMERIC(10,0) and in SQL it is int.

I can not assign a NUMERIC(10,0) to an variable of DT_I4, but if I change the variable definition to DT_NUMERIC, then it would not work for SQL.

Anyone knows how to fix this?

I was thinking to add a Script Task between the Execute SQL Task and the Foreach Loop, and somehow access the collection variable, v_collection, and manually convert the value to a DT_IT, then repopulate another collection variable, v_collection_I4 with Integers, and force the Foreach Loop to use v_collection_I4 collection variable.

Will this work? If yes, how? :)

Thanks a lot!

Wenbiao

Hi Wenbiao,

My general principle with variables in SSIS is to use String datatype where at all possible. So following your example above, I would change your SQL statement along the lines of:

select convert ( varchar, primary_key) as primary_key from a_table;

The variable datatype would then be a String.

Then during your Dataflow within your loop, you can use a Derived Column transformation to perform a Type Cast to whatever datatype you need before you deliver the data.

Good luck.

Mike

|||As an alternative, you could create two variables, the first one DT_NUMERIC, the second DT_I4. Set the first one with the numeric value from Oracle in your For Each Loop. Set the second one to evaluate as an expression, and cast the first variable to DT_I4 in the expression. It will be updated each time the first variable changes, and you can use it in the tasks in the loop.

It doesnt execute my assemblies.

Hello I just did 2 stored procedures, they dont have errors and they deployed succesfully but after I tried to exec them it doesnt happen anything.
I waited for 10 minutes and nothing

using System;

using System.Data;

using System.Data.Sql;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void getSalesOrdersHeaders()

{

// Put your code here

SqlPipe sp = SqlContext.Pipe;

using (SqlConnection conn = new SqlConnection(@."Data Source=ESTACION15\SQL2005; User Id=pruebas; Password=pruebas;database=AdventureWorks;"))

{

conn.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.Text;

cmd.Connection = conn;

cmd.CommandText = "select * from sales.SalesOrderHeader";

SqlDataReader rdr = cmd.ExecuteReader();

sp.Send(rdr);

}

}

};

I tried to run it from visual studio .net and I get A TimeOut error

Error 1 Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Pubs|||

How did you run it from Visual Studio?
From server explorer, test scripts or database project script

Thanks,
-Vineet.

|||Hello. It suddently began working after restarting my machine. Strange behavior.|||same here. It works after restarting my machine. Is this really normal? Any hints on this one?|||dunno if this is relevant or what you wrote was intended, but by creating the connection like you do you're creating a new connection from the connection your sproc's already using.

SqlConnection conn = new SqlConnection("context connection = true");

will get a reference to the existing one.
more info @. http://codebetter.com/blogs/sahil.malik/archive/2005/07/26/129824.aspx

It doesnt execute my assemblies.

Hello I just did 2 stored procedures, they dont have errors and they deployed succesfully but after I tried to exec them it doesnt happen anything.
I waited for 10 minutes and nothing

using System;

using System.Data;

using System.Data.Sql;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void getSalesOrdersHeaders()

{

// Put your code here

SqlPipe sp = SqlContext.Pipe;

using (SqlConnection conn = new SqlConnection(@."Data Source=ESTACION15\SQL2005; User Id=pruebas; Password=pruebas;database=AdventureWorks;"))

{

conn.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.Text;

cmd.Connection = conn;

cmd.CommandText = "select * from sales.SalesOrderHeader";

SqlDataReader rdr = cmd.ExecuteReader();

sp.Send(rdr);

}

}

};

I tried to run it from visual studio .net and I get A TimeOut error

Error 1 Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Pubs|||

How did you run it from Visual Studio?
From server explorer, test scripts or database project script

Thanks,
-Vineet.

|||Hello. It suddently began working after restarting my machine. Strange behavior.|||same here. It works after restarting my machine. Is this really normal? Any hints on this one?|||dunno if this is relevant or what you wrote was intended, but by creating the connection like you do you're creating a new connection from the connection your sproc's already using.

SqlConnection conn = new SqlConnection("context connection = true");

will get a reference to the existing one.
more info @. http://codebetter.com/blogs/sahil.malik/archive/2005/07/26/129824.aspx

Issues with privileges

I am having trouble with providing the minimum security to a user. After issuing the following:

GRANT EXECUTE ON SCHEMA :: DBO TO skillsnetuser;

I test the permissions with

exec as login = 'skillsnetuser'

exec prcElmtList 1, 1, 102268

revert;

and receive this message

Msg 229, Level 14, State 5, Line 2

SELECT permission denied on object 'Org', database 'SNAccess_Dev', schema 'dbo'.

The principal that owns the dbo schema is dbo and is the principle for all procedures and tables in that schema.

What can I do to shed some light on what is causing this access problem?

Are the objects 'Org' and the procedure prcElmList defined in the same database ?|||Yes, Org is a table that prcElmList accesses|||

My first guess is that ownership chaining is broken for some reason. Can you please include the portion of the SP body that access the table? We can take a look to it and try to determine why ownership chaining is broken in this case.

I would also like to suggest migrating to one of the other mechanisms we have available on SQL Server 2005 that allow controlled access to resources via a module (i.e. SP) execution:

* EXECUTE AS – For more information you can visit Context Switching in BOL (http://msdn2.microsoft.com/en-us/library/ms188268.aspx)

* module signing: Module signing in BOL (http://msdn2.microsoft.com/en-us/library/ms345102.aspx)

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

IF NOT EXISTS (SELECT

o.OrgID

FROM

Org o

WHERE

o.OrgID = @.piOrgID

AND o.Active = 1

AND o.BeginDate < GetDate()

AND (o.EndDate > GetDate() OR o.EndDate IS NULL) )

BEGIN

RAISERROR ('The passed OrgID is invalid or inactive.' ,17 ,1)

RETURN -2 -- OrgID not valid or active

END -- IF NOT EXISTS

|||

I tried a similar scenario, and it seems to work for me, and as your sample code doen’t involve any dynamic SQL I am not sure what I may be missing. Can you give us some more information?

I would like to ask you to run the following query:

SELECT name, principal_id, schema_id

FROM sys.objects WHERE

name LIKE 'prcElmtList'

OR name LIKE 'Org'

I would also like to ask you if you are using a database in backwards compatibility mode (and if so, what is the compatibility level you are using) and if you are using any options for the stored procedure declaration (such as NOT FOR REPLICATION, EXECUTE AS, etc.), and if there are more objects involved in the chain other than the SP and the table.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

SELECT name, principal_id, schema_id

FROM sys.objects WHERE

name LIKE 'prcElmtList'

OR name LIKE 'Org'

--Results

name principal_id schema_id

Org NULL 1

prcElmtList NULL 1

--For the procedure generation script

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

WITH RECOMPILE

--General Properties of the procedure showned by the GUI

Execute as caller

Schema dbo

System object False

ANSI NULLS True

Encrypted False

For replication False

Quoted identifier True

Recompile True

--Database Options showned by the GUI

Compatibility Level SQL Server 2005 (90)

All Miscellaneous Settings are False except Parameterization which is Simple

|||

At this junction I have added with execute as owner to the procedures,

this provides the functionality I was expecting to be present....

|||

Sorry for the late response, I have been trying to repro why ownership chaining didn’t worked for this particular scenario, I tried creating a small repro similar to the one you described, but so far I have no luck (my repro test is able to access the table as expected).

If you consider it is a bug in the product, I would recommend following the instructions described on the “Tips on using SQL Server Security forums” page to report it (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=286374&SiteID=1). We will probably need more details than the ones we have right now to continue the investigation.

I am glad you were able to find a solution for the problem. Please, let us know if you have any further questions or feedback; we will greatly appreciate your feedback on the EXECUTE AS feature.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

Issues with execute package task in SSIS

Hi,

We have used an execute package task in our master package to execute a child package and we have set the execute out of process=false. This master package is running fine in 32 bit server but is failing in 64 bit server. is there any settings to be done in the server or is it the problem with the property setting(execute out of process)

Vivek S

Please provide the specific error(s) you are receiving.|||

Hi Phil,

Here is the error msg i am getting.

Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run.

Thanks & regards

Vivek S

|||Can you run the child package on it's own on the x64 machine?|||

A package may fail validation for many reasons. Make sure the 64 bit server have the 64 bit version of the drivers used by the connection managers. Also notice that you could force the execution of the package in 32 bit mode (via dtexec).

BTW how are you running the package? Have you tried runnig them via dtexec to see if you can get a more detailed error?

|||

Hi,

The package exection fails irrespective of whether we run using dtexec from command prompt or from integration services.

we tried both ways, still it fails.

Just a check. its the RTM version of SQL which in currently in the 64 bit m/c where the pkg is failing where as the 32 bit m/c are applied with SP1. can this patch make a difference.

Vivek S

|||

Hi Crispin,

Yes the child package executes successfully when executed saperately.

Vivek S

|||

Vivek S wrote:

Just a check. its the RTM version of SQL which in currently in the 64 bit m/c where the pkg is failing where as the 32 bit m/c are applied with SP1. can this patch make a difference.

Vivek S

Sure, it can make a difference.

|||How are you executing the child to test it, and how are you executing the master?|||First, any Excel connections? There isn't an Excel connection manager in 64 bit mode.

Second, try running the package in 32 bit mode (use the 32 bit executable) on the 64 bit server.|||

Hi,

The issue is solved post SP1 application in 64 bit m/c. Thanks to all for the suggessions given for my posting.

Regards,

Vivek S

ISSUE: After the SQL Server restart, Login failed for user 'XXX\Administrator'

Hello,
I've a problem, When I Execute the following COMMAND in a batch
command(.bat)
NET STOP MSSQLSERVER
NET START MSSQLSERVER
MY_DATABASE APPLICATION
I GOT Login failed for user 'XXX\Administrator' Exception, but when
after the SERVER started long time, MY_DATABASE APPLICATION will not
get the exception
My COnnection string is "Trusted_Connection=Yes;Integrated
security=SSPI;data source=(local);initial catalog=xxx"
Is there any help that can help me to get rid of it?
MSSQL SERVER 2005I've noticed that SQL Server 2005 takes longer to start. I suggest adding a
delay after starting SQL
Server and before starting your application. Or, modify your application cod
e so it has some
re-tries with some wait in between.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"zzppallas" <zzppallas@.gmail.com> wrote in message
news:1140253035.885476.318630@.g43g2000cwa.googlegroups.com...
> Hello,
> I've a problem, When I Execute the following COMMAND in a batch
> command(.bat)
> NET STOP MSSQLSERVER
> NET START MSSQLSERVER
> MY_DATABASE APPLICATION
> I GOT Login failed for user 'XXX\Administrator' Exception, but when
> after the SERVER started long time, MY_DATABASE APPLICATION will not
> get the exception
> My COnnection string is "Trusted_Connection=Yes;Integrated
> security=SSPI;data source=(local);initial catalog=xxx"
> Is there any help that can help me to get rid of it?
> MSSQL SERVER 2005
>|||Actually this is a weird explanation. If you start the server, are you
getting at this point the exception or at connection time with your
client application. If you are getting the exception at service start,
it seems that your account is a) not prvililedged any more or b) the
password you enetered in the service manager has changed.
If you are getting the exception in the connection via a application
you have to make sure that the user you are connecting with has the
appropiate right on the database. per default administrator are
priviledged on the server, but they can be removed from the autorized
list.
HTH, jens Suessmeyer.

ISSUE: After the SQL Server restart, Login failed for user 'XXX\Administrator'

Hello,
I've a problem, When I Execute the following COMMAND in a batch
command(.bat)
NET STOP MSSQLSERVER
NET START MSSQLSERVER
MY_DATABASE APPLICATION
I GOT Login failed for user 'XXX\Administrator' Exception, but when
after the SERVER started long time, MY_DATABASE APPLICATION will not
get the exception
My COnnection string is "Trusted_Connection=Yes;Integrated
security=SSPI;data source=(local);initial catalog=xxx"
Is there any help that can help me to get rid of it?
MSSQL SERVER 2005I've noticed that SQL Server 2005 takes longer to start. I suggest adding a delay after starting SQL
Server and before starting your application. Or, modify your application code so it has some
re-tries with some wait in between.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"zzppallas" <zzppallas@.gmail.com> wrote in message
news:1140253035.885476.318630@.g43g2000cwa.googlegroups.com...
> Hello,
> I've a problem, When I Execute the following COMMAND in a batch
> command(.bat)
> NET STOP MSSQLSERVER
> NET START MSSQLSERVER
> MY_DATABASE APPLICATION
> I GOT Login failed for user 'XXX\Administrator' Exception, but when
> after the SERVER started long time, MY_DATABASE APPLICATION will not
> get the exception
> My COnnection string is "Trusted_Connection=Yes;Integrated
> security=SSPI;data source=(local);initial catalog=xxx"
> Is there any help that can help me to get rid of it?
> MSSQL SERVER 2005
>|||Actually this is a weird explanation. If you start the server, are you
getting at this point the exception or at connection time with your
client application. If you are getting the exception at service start,
it seems that your account is a) not prvililedged any more or b) the
password you enetered in the service manager has changed.
If you are getting the exception in the connection via a application
you have to make sure that the user you are connecting with has the
appropiate right on the database. per default administrator are
priviledged on the server, but they can be removed from the autorized
list.
HTH, jens Suessmeyer.

Issue with the ForEachLoop Task in SSIS

Hi,

I am using a SQL task to execute a stored procedure which returns a single field with multiple records. I want the records returned by the stored procedure to be processed one by one within a ForEachLoop container. How do I assign the records one by one to one variable and use it in a Script task running inside the ForEachLoop container.

I am using 2 tasks in my package.

In my first task I call a SQL task that executes a stored procedure which returns a list of reference numbers (TrackData). This works perfectly.

However, in my second task I must use the ForEachLoop task to loop through the above list and set the value of var_TrackData (a user variable declared by me) with the value of the TrackData present in it during that particular loop.

I am not sure how to go about the second task. Any help would be greatly appreciated.

Create an object variable.

In the execute sql task set the resultset to the variable name (set result name to 0).

Create a variable to hold the reference numbers (int or string?)

Create a for each loop task

Set the collection to for each ADO enumerator

Set the ADO object source variable to your object variable name (which contains the resultset).

In variable mappings set the variable to the variable name you created to hold the reference numbers

Set the precednce so that the execute sql runs before the for each llop.

Now when this runs the object variable will be set to the resultset the the for each loop will itterate through it setting the variable to the reference number for each loop.

|||

See if this post gives you some idea of how you can do it:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

Issue with executing dynamic sql with inbound paramter as varchar striong

Hi All:

I am trying to execute a dynamic sql, the dynamic sql makes use of an inbound paramter defined as varchar.

When I try to execute it fails, because it does not plavce the inbound paramter in quotes.

Any help would be appreciated.

In the bound search as an eaxmple can be" 'NY'

@.P_SEARCH_VALUE='NY'

SET @.V_SQL_FILTER = N' WHERE STATE = '+@.P_SEARCH_VALUE

SET @.V_SQL=@.V_BASE_SQL+@.V_SQL_FILTER

EXEC sp_executesql @.V_SQL

Here is the v_sql out put:

SELECT TOP 100 * FROM V$ZIPCODE_LOOKUP_ALL WHERE STATE = NY

As you can see the sql will fail because the NY is not in quotes.

I tried using '@.P_SEARCH_VALUE''' and other forms but could not get it work.

There are a couple of ways (and variations) to approach this issue.

First, and the most 'robust', is to use the capability of sp_executesql to handle parameters. For the best explanition and demonstration, see Erland's article here.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/ms188332.aspx
http://msdn2.microsoft.com/en-us/library/ms175170.aspx

Otherwise, you have to devise some scheme to manage quotes. For example, in you code above you are not managing the quotes inside your string. To embed a single quote inside a string, you have to double it up. So your filter would be more like this:

SET @.V_SQL_FILTER = N' WHERE STATE = ''' + @.P_SEARCH_VALUE + ''''

Handling the quotes can seem to get out of hand, and very confusing. For that reason, the first option is the best.

|||

Since you are using the sp_executesql, itself supports the parameterized quires. You need not to concatenate those values (contaminating values may cause sql injection).

You can achieve the same result using the following query,

Code Snippet

SET @.P_SEARCH_VALUE='NY'

SET @.V_SQL_FILTER = N' WHERE STATE = @.P_SEARCH_VALUE'

SET @.V_SQL = @.V_BASE_SQL+@.V_SQL_FILTER

SET @.V_PARAM = N'@.P_SEARCH_VALUE VARCHAR(100)'

EXEC sp_executesql @.V_SQL, @.V_PARAM , @.P_SEARCH_VALUE

Sample,

Code Snippet

Declare @.SQL as Nvarchar(1000);

Declare @.Value as varchar(100);

Declare @.Param as Nvarchar(100);

Set @.SQL = N'Select * from sysobjects where name=@.value'

Set @.Param = N'@.value varchar(100)'

Set @.Value = 'sysobjects'

exec sp_executesql @.SQL, @.Param, @.value

|||

For something like this:

SET @.V_SQL_FILTER = N' WHERE STATE = ''' + @.P_SEARCH_VALUE + ''''

You can use:

declare @.p_search_value nvarchar(20)

set @.p_search_value = 'This is a quote '''

select N' WHERE STATE = ' + quoteName(@.P_SEARCH_VALUE ,'''')

It is best to use sp_executeSQL, but if you have to work with quotes, this is the best tool for the job.|||try the following use three single quotes

@.P_SEARCH_VALUE='''NY'''

SET @.V_SQL_FILTER = N' WHERE STATE = '+@.P_SEARCH_VALUE

SET @.V_SQL=@.V_BASE_SQL+@.V_SQL_FILTER

EXEC sp_executesql @.V_SQL