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.

Issues with Export to Excel file

I need to do a simple task. I have a oledb source which pulls data from sql server, creates an excel file abc.xls and dumps it with the Excel destination at remote location.

I need to create this file at runtime. However, when i give the location, the Excel Destination expects the file to be there for the Mapping of the source columns with destination ones.

My tasks are

1. Creating a table for the worksheet in Excel connection

2. Select from source table and write in Excel Destination

Will appreciate your response.

You can create the xls file at run time without problem; but the file need to exists at design time as SSIS needs to get the metedata from it.

I have a couple of posts that show some examples using excel files; I hope you find them helpful

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

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 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 File System Task and folder locking

I'm having an issue where a folder that I'm accessing in an SSIS package gets locked up and cannot be deleted by my package later in the process.

1) I use a File System task to rename the folder. At this point, it appears to become locked and cannot be deleted.

2) Later I attempt to delete the folder using another File System task object. I get the error '[File System Task] Error: An error occurred with the following error message: "The process cannot access the file because it is being used by another process."'.

My package accesses the folder via a drive mapping. This seems to be part of the problem, as it's possible to delete the folder directly but not via the drive mapping. Even once the package completes it's still not possible to delete the folder in Windows Explorer via the drive mapping.

I've since rebooted my machine and re-created the network drive mapping and the problem has gone away.

issue with dts.taskresult from script task

have a script task that checks for existense of a file if found it sets

Dts.TaskResult = Dts.Results.Success and moves to next task

and if not found it sends an email then sets Dts.TaskResult = Dts.Results.Failure

but when I run the job to test for failure I do get the email but the job continues

to be in yellow state it never turns red. I have to terminate it myself manually.

Is there anything else I need to set?

Thanks in advance

I find that it is easier to set a boolean variable indicating success or otherwise. you can then check the value within a conditional precedence constraint.

-Jamie

Issue with Ascii 7 files

Hi All,

I need to generate Ascii 7 bit flat file, based on data in db, using integration services, FTP task. Currently i am generating file with ansi-latin and then using the script task converting it to the ascii 7. File looks to be generated properly. But when the target system reads this, they complain that the file has junk charecters some thing like this. when i open it after generating the file it looks fine to me in DOS also. I dont know what is the target system and what OS is used by them. what cud be the issue for these junk charecters and is it possible that a Ascii 7 file generated by windows doesnt work in other OS? If the method i am doing to generate the ascii 7 is not currect then what is the best method for this?

????H^@.D^@.R^@.|^@.1^@.E^@.N^@.I^@.N^@.A^@.|^@.O^@.|^@.2^@.0^@.0^@.6^@.-^@.0^@.9^@.-^@.1^@.3^@.

PS: earlier i had generated a flat file using data export from Excel & that worked in the target system well. is there any difference in the file encoding generated by excel and integration services?

Please help me!!!!

-vinu

ANSI Latin is extention of ASCII character set. Meaning any proper ASCII text is also valid ANSI Latin text. And ANSI Latin text is either valid ASCII text as well, or contains characters that can't be represented in ASCII at all. So you either don't need any conversion from ANSI Latin -> ASCII, or the lossles conversion is not possible.

I'm not sure what you are doing in the script task, but if you specified the requirements correctly, it is either not needed or not possible :). Anyway, it would be helpful if you include the code.

Issue while using Transfer Sql Server Objects tasks

Hi
I am trying to use Transfer SQL Server Object task to transfer various
DB objects from a Source DB to a newly created Destination DB. The
Destination DB is blank before this attempted transfer.
Now our source Db contains various SPs and views which internally
reference other SPs/Views of the same DB (source DB). For example we
have 2 SPs - SP1 and SP2. Now SP1 internally executes SP2. Whie using
transfer objects task, if we try to transfer all SPs only, we get an
error saying that SP2 does not exist while creating SP1. It seems SSIS
compiles objects while transfering them. We tried to set the
DelayValidation property of the entire package as well as the task to
True, but this didnt help either.
It would be great if someone could suggest some mechanism to prevent
compilation of objects, or any other method using which we can avoid
the problem that we are facing.
Thanks
Regards,
Piyush
I would redirect any failure to a logging table or other, or even ignore
errors.
On 1st pass sp1 will fail, but sp2 should still be created, correct? Then
perform a second pass which will cause sp2 to fail because already exists,
however sp1 will compile and create fine since dependency now exists...
You will get a quicker response if you post to dts group for ssis questions.
good luck.
ChrisB MCDBA
MSSQLConsulting.com
"whirlwnd" wrote:

> Hi
> I am trying to use Transfer SQL Server Object task to transfer various
> DB objects from a Source DB to a newly created Destination DB. The
> Destination DB is blank before this attempted transfer.
> Now our source Db contains various SPs and views which internally
> reference other SPs/Views of the same DB (source DB). For example we
> have 2 SPs - SP1 and SP2. Now SP1 internally executes SP2. Whie using
> transfer objects task, if we try to transfer all SPs only, we get an
> error saying that SP2 does not exist while creating SP1. It seems SSIS
> compiles objects while transfering them. We tried to set the
> DelayValidation property of the entire package as well as the task to
> True, but this didnt help either.
> It would be great if someone could suggest some mechanism to prevent
> compilation of objects, or any other method using which we can avoid
> the problem that we are facing.
> Thanks
> Regards,
> Piyush
>

Issue gettin a sql agent task to stop.

Russell,
do any of the steps invoke DTC or use linked servers?
Russell wrote:
> I Have a daily task that runs a stored procedure to import some orders us
ing
> a call to a third party exe, then to copy and rename a couple of logs and
> then use XPSMTP to send the logs to a couple of people. It has done all o
f
> it except the sending of the e-mail.
> I have noticed this morning that the run of the job from friday was still
> running. So I have killed the process which now says it is rolling back, s
o
> far it has been rolling back for 3 hours. and it does not do much data
> manipulation. If I use kill with status I get:
> SPID 61: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> But the rollback still runs, are ther any executables that SQL uses to run
> extended stored procedures that I could kill that might release this threa
d?
> Regards
> RussellThe job has 3 steps, the first runs a DTS which copies some rows from a
remote server, the second job executes an exe on remote server, the third
step runs a stored procedure which copies a file from a remote server and
renames it, also it runs an ISQL statement to put the output to a file,
lastly it e-mails the 2 files to myself and the finance director. The only
part of the whole process it has not done is to send the e-mail
"shane" wrote:

> Russell,
> do any of the steps invoke DTC or use linked servers?
>
> Russell wrote:
>|||I Have a daily task that runs a stored procedure to import some orders usin
g
a call to a third party exe, then to copy and rename a couple of logs and
then use XPSMTP to send the logs to a couple of people. It has done all of
it except the sending of the e-mail.
I have noticed this morning that the run of the job from friday was still
running. So I have killed the process which now says it is rolling back, so
far it has been rolling back for 3 hours. and it does not do much data
manipulation. If I use kill with status I get:
SPID 61: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
But the rollback still runs, are ther any executables that SQL uses to run
extended stored procedures that I could kill that might release this thread?
Regards
Russell|||Russell,
do any of the steps invoke DTC or use linked servers?
Russell wrote:
> I Have a daily task that runs a stored procedure to import some orders us
ing
> a call to a third party exe, then to copy and rename a couple of logs and
> then use XPSMTP to send the logs to a couple of people. It has done all o
f
> it except the sending of the e-mail.
> I have noticed this morning that the run of the job from friday was still
> running. So I have killed the process which now says it is rolling back, s
o
> far it has been rolling back for 3 hours. and it does not do much data
> manipulation. If I use kill with status I get:
> SPID 61: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> But the rollback still runs, are ther any executables that SQL uses to run
> extended stored procedures that I could kill that might release this threa
d?
> Regards
> Russell|||The job has 3 steps, the first runs a DTS which copies some rows from a
remote server, the second job executes an exe on remote server, the third
step runs a stored procedure which copies a file from a remote server and
renames it, also it runs an ISQL statement to put the output to a file,
lastly it e-mails the 2 files to myself and the finance director. The only
part of the whole process it has not done is to send the e-mail
"shane" wrote:

> Russell,
> do any of the steps invoke DTC or use linked servers?
>
> Russell wrote:
>|||Ive usually come across this problem when there is a distributed
transaction involved.
stop and restart MS DTC, and see if this helps
also try running a dbcc opentran on the database in question, to see if
there are any other transacations which may be interfering..
worst case scenario youll have to stop and restart the sql services
Russell wrote:[vbcol=seagreen]
> The job has 3 steps, the first runs a DTS which copies some rows from a
> remote server, the second job executes an exe on remote server, the third
> step runs a stored procedure which copies a file from a remote server and
> renames it, also it runs an ISQL statement to put the output to a file,
> lastly it e-mails the 2 files to myself and the finance director. The only
> part of the whole process it has not done is to send the e-mail
> "shane" wrote:
>|||Hi Shane,
There are no open transactions and I have restarted the DTC service. So it
looks like I will have to try stopping and starting SQL on the server.
"shane" wrote:

> Ive usually come across this problem when there is a distributed
> transaction involved.
> stop and restart MS DTC, and see if this helps
> also try running a dbcc opentran on the database in question, to see if
> there are any other transacations which may be interfering..
> worst case scenario youll have to stop and restart the sql services
>
> Russell wrote:
>|||Ive usually come across this problem when there is a distributed
transaction involved.
stop and restart MS DTC, and see if this helps
also try running a dbcc opentran on the database in question, to see if
there are any other transacations which may be interfering..
worst case scenario youll have to stop and restart the sql services
Russell wrote:[vbcol=seagreen]
> The job has 3 steps, the first runs a DTS which copies some rows from a
> remote server, the second job executes an exe on remote server, the third
> step runs a stored procedure which copies a file from a remote server and
> renames it, also it runs an ISQL statement to put the output to a file,
> lastly it e-mails the 2 files to myself and the finance director. The only
> part of the whole process it has not done is to send the e-mail
> "shane" wrote:
>|||Hi Shane,
There are no open transactions and I have restarted the DTC service. So it
looks like I will have to try stopping and starting SQL on the server.
"shane" wrote:

> Ive usually come across this problem when there is a distributed
> transaction involved.
> stop and restart MS DTC, and see if this helps
> also try running a dbcc opentran on the database in question, to see if
> there are any other transacations which may be interfering..
> worst case scenario youll have to stop and restart the sql services
>
> Russell wrote:
>

IsQueryStoredProcedure property won't stay set

I'm using an ExecuteSQL task to fire a stored procedure. There is a property in the editor called IsQueryStoredProcedure that is disabled. However, in the properties window there is a property call IsStoredProcedure that is enabled and can be set. Problem is that it doesn't stay set. Is this a bug?

From BOL

IsQueryStoredProcedure

Indicates whether the specified SQL statement to be run is a stored procedure. This property is read/write only if the task uses the ADO connection manager. Otherwise the property is read-only and its value is false.

|||I just went thru this...if you're using an OLE DB provider, don't worry about it. Just put the SP call as your sql statement and it will work. You don't need to set that property.