Getting the list of expressions from a package

Does anyone have any ideas on quickly getting a list of all expressions used in a package? For the BIDSHelper add-in (http://www.codeplex.com/bidshelper) we need to get a list of all the expressions used in the package and update the list whenever an expression is added through the IDE. Currently, we are iterating through the package, and checking every property of every task to see if it has an expression. On big packages, this is slow, as you might imagine.

Any suggestions would be appreciated. Maybe one of our MVPs could flag this for Microsoft follow-up, to see if they have any suggestions.

I'd be happy to do that for you.

[Microsoft follow-up]|||

Tasks or components generally are responsible for their own expressions -- there is no central repository. As such, I can't think of any other way to accomplish your goal.

The only potential shortcut I can think of is for components in a dataflow. Instead of iterating through the dataflow, you might be able to just look at the dataflow properties to see all expressionable objects in that dataflow (properties named something like [<component name>].[<object name>].[FriendlyExpression])

Thanks
Mark

|||Is there any event that I could hook into from the IDE to let me know when a task or data flow component has been updated by the user? If that was possible, I could minimize the rescanning of the entire package, by just checking individual tasks.

Getting the count of rows for 3 tables in Single Execute SQL task

hi frnds,

im very new to SSIS package .my package consists of Single Exceute task.

In Single Exceute SQL task i had 3 seperate queries to get the count of rows of 3 tables.

the Query goes like dis ...

select count(*) AS precheckcount1 from new_main_dts where cust_nbr like '875%'

like dis for another 2 tables i had written with the alias name precheckcount2 and 3

i mapped the variable user::precheckcount1 to precheckcount1 in the resultSet and for other two alias name i did the same.

while executing the package the error is thrown : [Execute SQL Task] Error: An error occurred while assigning a value to variable "precheckcount2": "Unable to find column precheckcount2 in the result set.".

please help me its very urgent

It is simplest if you get all values in a single result set. Set the ResultSet property to single row, then use a query like this to get the combined result sets-

SELECT (SELECT COUNT(*) FROM sysobjects) AS C1, (SELECT COUNT(*) FROM sysobjects WHERE type = 'U') AS C2

Then on the Result Set tab set the mappings using the column indexes, Result Name of 0 maps to the variable name for the first column's value. Result Name 1 maps to the variable name for the second column's value.

|||

thanks a lot.

my problem got resolved.

getting started with a SQL Server

Hi all,
I will be getting a third party pre packaged software program that requires
an SQL server. The vendor will support the software package, but my questio
n
is:
Never had an SQL server before so I need to know the basic things that I
will need to know to maintain the MS 2000 SQL server. (the differences of
SQL verses say a domain controller running 2003 Server). It will be a membe
r
server on my domain.
Any tutorials on setup and maintenance on this out there?
Thanks in advance for any help.
SherThe SQL Server 2000 Operations Guide is a good start:
http://www.microsoft.com/technet/pr...in/sqlops0.mspx
You can find more guides for SQL Server on:
http://www.microsoft.com/technet/pr...00/default.mspx
Jacco Schalkwijk
SQL Server MVP
"Sher" <Sher@.discussions.microsoft.com> wrote in message
news:C8390847-549C-4478-83A1-1E618BBCC6F2@.microsoft.com...
> Hi all,
> I will be getting a third party pre packaged software program that
> requires
> an SQL server. The vendor will support the software package, but my
> question
> is:
> Never had an SQL server before so I need to know the basic things that I
> will need to know to maintain the MS 2000 SQL server. (the differences of
> SQL verses say a domain controller running 2003 Server). It will be a
> member
> server on my domain.
> Any tutorials on setup and maintenance on this out there?
> Thanks in advance for any help.
> Sher
>

getting started with a SQL Server

Hi all,
I will be getting a third party pre packaged software program that requires
an SQL server. The vendor will support the software package, but my question
is:
Never had an SQL server before so I need to know the basic things that I
will need to know to maintain the MS 2000 SQL server. (the differences of
SQL verses say a domain controller running 2003 Server). It will be a member
server on my domain.
Any tutorials on setup and maintenance on this out there?
Thanks in advance for any help.
SherThe SQL Server 2000 Operations Guide is a good start:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops0.mspx
You can find more guides for SQL Server on:
http://www.microsoft.com/technet/prodtechnol/sql/2000/default.mspx
--
Jacco Schalkwijk
SQL Server MVP
"Sher" <Sher@.discussions.microsoft.com> wrote in message
news:C8390847-549C-4478-83A1-1E618BBCC6F2@.microsoft.com...
> Hi all,
> I will be getting a third party pre packaged software program that
> requires
> an SQL server. The vendor will support the software package, but my
> question
> is:
> Never had an SQL server before so I need to know the basic things that I
> will need to know to maintain the MS 2000 SQL server. (the differences of
> SQL verses say a domain controller running 2003 Server). It will be a
> member
> server on my domain.
> Any tutorials on setup and maintenance on this out there?
> Thanks in advance for any help.
> Sher
>sql

getting started with a SQL Server

Hi all,
I will be getting a third party pre packaged software program that requires
an SQL server. The vendor will support the software package, but my question
is:
Never had an SQL server before so I need to know the basic things that I
will need to know to maintain the MS 2000 SQL server. (the differences of
SQL verses say a domain controller running 2003 Server). It will be a member
server on my domain.
Any tutorials on setup and maintenance on this out there?
Thanks in advance for any help.
Sher
The SQL Server 2000 Operations Guide is a good start:
http://www.microsoft.com/technet/pro...n/sqlops0.mspx
You can find more guides for SQL Server on:
http://www.microsoft.com/technet/pro...0/default.mspx
Jacco Schalkwijk
SQL Server MVP
"Sher" <Sher@.discussions.microsoft.com> wrote in message
news:C8390847-549C-4478-83A1-1E618BBCC6F2@.microsoft.com...
> Hi all,
> I will be getting a third party pre packaged software program that
> requires
> an SQL server. The vendor will support the software package, but my
> question
> is:
> Never had an SQL server before so I need to know the basic things that I
> will need to know to maintain the MS 2000 SQL server. (the differences of
> SQL verses say a domain controller running 2003 Server). It will be a
> member
> server on my domain.
> Any tutorials on setup and maintenance on this out there?
> Thanks in advance for any help.
> Sher
>

Getting SQL Server 2000 to listen only to the loopback device

Is it possible to get SQL Server 2000 to listen only to connections on
the Microsoft loopback device?
I am running a proprietary analysis package on top of SQL Server that
requires that a TCP/IP connection. I will never connect to SQL Server
remotely, so I would like to avoid having SQL Server listen for
connections on my network adapter.
I am running SQL Server Personal Edition on Windows 200 SP4.
Any thoughts on how to set this up?
Thanks,
Chris Green
joeionnojitsu AT comcast.netThe MS loopback device is network address 127.0.0.1 ' Is that what
you are referring to'
Have you looked at the following articles?
http://support.microsoft.com/?id=287932
http://support.microsoft.com/default.aspx?kbid=814130
Since the entire point behind a server is to provide for several
clients, your goal doesn't seem feasible unless you have a firewall
that blocks everything destined for the server, and not coming from
the local host.
Lou Arnold
On Thu, 24 Jun 2004 06:13:27 GMT, c green
<joeionnojitsu@.NOSPAM.comcast.net> wrote:
>Is it possible to get SQL Server 2000 to listen only to connections on
>the Microsoft loopback device?
>I am running a proprietary analysis package on top of SQL Server that
>requires that a TCP/IP connection. I will never connect to SQL Server
>remotely, so I would like to avoid having SQL Server listen for
>connections on my network adapter.
>I am running SQL Server Personal Edition on Windows 200 SP4.
>Any thoughts on how to set this up?
>Thanks,
>Chris Green
>joeionnojitsu AT comcast.net

Getting SQL Server 2000 to listen only to the loopback device

Is it possible to get SQL Server 2000 to listen only to connections on
the Microsoft loopback device?
I am running a proprietary analysis package on top of SQL Server that
requires that a TCP/IP connection. I will never connect to SQL Server
remotely, so I would like to avoid having SQL Server listen for
connections on my network adapter.
I am running SQL Server Personal Edition on Windows 200 SP4.
Any thoughts on how to set this up?
Thanks,
Chris Green
joeionnojitsu AT comcast.netThe MS loopback device is network address 127.0.0.1 ' Is that what
you are referring to'
Have you looked at the following articles?
http://support.microsoft.com/?id=287932
http://support.microsoft.com/default.aspx?kbid=814130
Since the entire point behind a server is to provide for several
clients, your goal doesn't seem feasible unless you have a firewall
that blocks everything destined for the server, and not coming from
the local host.
Lou Arnold
On Thu, 24 Jun 2004 06:13:27 GMT, c green
<joeionnojitsu@.NOSPAM.comcast.net> wrote:

>Is it possible to get SQL Server 2000 to listen only to connections on
>the Microsoft loopback device?
>I am running a proprietary analysis package on top of SQL Server that
>requires that a TCP/IP connection. I will never connect to SQL Server
>remotely, so I would like to avoid having SQL Server listen for
>connections on my network adapter.
>I am running SQL Server Personal Edition on Windows 200 SP4.
>Any thoughts on how to set this up?
>Thanks,
>Chris Green
>joeionnojitsu AT comcast.netsql

Getting SQL Server 2000 to listen only to the loopback device

Is it possible to get SQL Server 2000 to listen only to connections on
the Microsoft loopback device?
I am running a proprietary analysis package on top of SQL Server that
requires that a TCP/IP connection. I will never connect to SQL Server
remotely, so I would like to avoid having SQL Server listen for
connections on my network adapter.
I am running SQL Server Personal Edition on Windows 200 SP4.
Any thoughts on how to set this up?
Thanks,
Chris Green
joeionnojitsu AT comcast.net
The MS loopback device is network address 127.0.0.1 ? Is that what
you are referring to?
Have you looked at the following articles?
http://support.microsoft.com/?id=287932
http://support.microsoft.com/default.aspx?kbid=814130
Since the entire point behind a server is to provide for several
clients, your goal doesn't seem feasible unless you have a firewall
that blocks everything destined for the server, and not coming from
the local host.
Lou Arnold
On Thu, 24 Jun 2004 06:13:27 GMT, c green
<joeionnojitsu@.NOSPAM.comcast.net> wrote:

>Is it possible to get SQL Server 2000 to listen only to connections on
>the Microsoft loopback device?
>I am running a proprietary analysis package on top of SQL Server that
>requires that a TCP/IP connection. I will never connect to SQL Server
>remotely, so I would like to avoid having SQL Server listen for
>connections on my network adapter.
>I am running SQL Server Personal Edition on Windows 200 SP4.
>Any thoughts on how to set this up?
>Thanks,
>Chris Green
>joeionnojitsu AT comcast.net

Getting SQL result in a variable

Hi there

I have a global variable say cnt in SSIS package, now I want to get total number of rows from a table say emp in that variable cnt.

how do we achieve that?

thanks and regards

Rahul Kuamr

Hello Rahul,

You can use a row count transformation just before the destination component in your dataflow, you a have to declare a variable of type int32 and assign this variable in the row count transformation.

Regards,

Raju

|||

on the same line,another query

If we wish to get result of some SQL query say select name from emp where id='234'

thanks and regards

Rahul kumar

|||

Hi,

To get the result of a query into a variable first you have to declare a variable of the same type which is returned by the query, and use an execute sql task in the control flow, Double click the execute sql task and click on the general tab and make the result set property to "Single Row" and write the query in this way: "Select name as empname from emp where empid = '234'. " Now you can assign the name alias "empname" used in the query to a result set. To do that click on the result set which is available in the execute sql task and give the name of the result set as "empname" and assign to a variable which is of the same type.

Note: property to "Single Row" only works when the query is returning a single row. If the query is returning mutiple rows, you have to declare a variable of the type object and select the property of the result set as "Full result set". and assign the result name to the variable which has been declared as object.

Regards,

Raju

|||

Thanks a lot buddy.

It works!!

Regards

Rahul Kumar

|||

Hello, found this post and am hoping that you can help with assigning a variable with an Execute SQL Task. My task runs, but the variable doesn't get a value, stays blank.

I'm trying to retrieve a string value from a SQL table called tblSys_Config. There's only one row in the table. The column I want is called is an nvarchar(3) field called Config_Code.

I've defined a user variable called TestVar with type as string.

I've set up my Execute SQL Task with a SingleRow result set, and the SQL Statement is "select Config_Code As ConfigCode from tblSys_Config".

The ResultSet for the SQL Task has Resultname ConfigCode (same in "As ConfigCode" in SQL statement), with variable name user::TestVar.

When I execute the task, it runs but just doesn't update the variable, it stays blank. Maybe I'm not looking for the value correctly, just right clicking in design space in VS and choosing Variables to see the value of TestVar, always blank.

I did try setting the result set to Full Result Set, variable to Object, and the Result Name to 0, and got a very helpful value of System.Object. Maybe that's what I have to work with, but I just have a single value to retrieve that should work with SingleRow. I've also tried different naming conventions and making sure Result Name is same case as the column name.

Thanks for any advice you can give me, maybe I do have use an Object Variable.

Chera

|||

cboom wrote:

Hello, found this post and am hoping that you can help with assigning a variable with an Execute SQL Task. My task runs, but the variable doesn't get a value, stays blank.

I'm trying to retrieve a string value from a SQL table called tblSys_Config. There's only one row in the table. The column I want is called is an nvarchar(3) field called Config_Code.

I've defined a user variable called TestVar with type as string.

I've set up my Execute SQL Task with a SingleRow result set, and the SQL Statement is "select Config_Code As ConfigCode from tblSys_Config".

The ResultSet for the SQL Task has Resultname ConfigCode (same in "As ConfigCode" in SQL statement), with variable name user::TestVar.

So far, so good.

cboom wrote:

When I execute the task, it runs but just doesn't update the variable, it stays blank. Maybe I'm not looking for the value correctly, just right clicking in design space in VS and choosing Variables to see the value of TestVar, always blank.

I think the problem is the way you are looking for the value. You may want to set a breakpoint and execute the whole package; then at the break point time, examinate the varibale by looking at the output window.

cboom wrote:

I did try setting the result set to Full Result Set, variable to Object, and the Result Name to 0, and got a very helpful value of System.Object. Maybe that's what I have to work with, but I just have a single value to retrieve that should work with SingleRow. I've also tried different naming conventions and making sure Result Name is same case as the column name.

You don't need to use a Object type variable...

|||

Thanks Rafael, you were right, just needed to find a new place to check the value of the variable, so played more with variables in Script Tasks and Componenets and got quite a bit more figured out. Had to learn about specifying the ReadOnlyVariables and ReadWriteVariables for the scripts.

Thanks, appreciate you pointing me in the right direction.

Chera

Getting problem in AcquireConnections

Hi All,

I just started looking at the SSIS programming, wanted to created a package having table to table data transfer. My source and destination databases are in Oracle.

I gone throught the code samples and started creating the source component. And the whatever samples i've seen the code i have written (copied) looks correct to me but still getting following error.

The basic questions i have is,

1. Do i need to setup something to start programming in SSIS. I am using 'Microsoft Visual C# Express Edition' for programming.

I have all the dll's in place.

And after compiling the code the exception i got is,

{"Exception from HRESULT: 0xC020801C"} System.Runtime.InteropServices.COMException was caught

Message="Exception from HRESULT: 0xC020801C"

Source="Microsoft.SqlServer.DTSPipelineWrap"

ErrorCode=-1071611876

StackTrace:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.AcquireConnections(Object pTransaction)

The Code is as Follows:

public static void CreateSource()

{

Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();

Executable e = package.Executables.Add("DTS.Pipeline.1");

Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = e as Microsoft.SqlServer.Dts.Runtime.TaskHost;

MainPipe dataFlow = thMainPipe.InnerObject as MainPipe;

// Add an OLEDB connection manager that is used by the component to the package.

ConnectionManager cm = package.Connections.Add("OLEDB");

cm.Name = "OLEDB ConnectionManager";

cm.ConnectionString = @."Data Source=pqdb9i;User ID=srcDbUserId;Provider=MSDAORA.1;Persist Security Info=False;Integrated Security=SSPI;Auto Translate=False;";

package.DelayValidation = true;

cm.DelayValidation = true;

component = dataFlow.ComponentMetaDataCollection.New();

component.Name = "OLEDBSource";

component.ComponentClassID = "DTSAdapter.OleDbSource.1";

// Get the design time instance of the component.

CManagedComponentWrapper instance = component.Instantiate();

// Initialize the component

instance.ProvideComponentProperties();

// Specify the connection manager.

if (component.RuntimeConnectionCollection.Count > 0)

{

component.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[0].ID;

component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);

}

// Set the custom properties.

//instance.SetComponentProperty("AlwaysUseDefaultCodePage", false);

instance.SetComponentProperty("AccessMode", 2);

instance.SetComponentProperty("SqlCommand", "Select * from srcTable");

// Reinitialize the metadata.

try

{

instance.AcquireConnections(null);

instance.ReinitializeMetaData();

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

}

Console.WriteLine(component.InputCollection.Count);

}

///End Code

Thanks in advance

-Yuwaraj

Does Oracle use Windows Authentication in your case? If not, where's the password for the database user?|||Hi

I have the same problem, did u find any solution?

Getting mdb file path at runtime for Dts package

Hi everybody..

I am creating a Dts package for transferring data from an Access mdb file to SQL server 2000 database.

The package itself is being invoked from a .Net program using dtsrun.

What i want to know is that I need to get the mdb files' path at runtime and accordingly run the Dts package for that mdb file path..

So how do i do that?
Thanks

try to include the mdb files in the same path as the .NET program is. then make an iterators or any trick to find the files at run time.

Getting Flatfile col data into a variable

I have a flat file with several columns and maybe half a dozen rows
I want the value of the first column of the last row captured in a package variable for other processing.

In the old DTS I ran the data through a transformation from the flat file to a dummy table in tempdb and captured the value for that field via the Active X code inside the transform:

DTSGlobalVariables("DistrictVersion").Value = DTSSource("Col001")

by the time the transform was done I had the last value of 'DTSSource("Col001")' stored in my package variable.

I am stumbling round trying to find an equivalant in SSIS

Vanna Can I Buy a Clue?

Paul,
The best way I can think of doing this is to read the file into a variable of type "Object" using a data-flow with a recordset destination.

Once the recordset is in a variable you can shred it using a Foreach loop using a technique similar to what is described here: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/03/27/9722.aspx

Or you could shred it using a script task as described here: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx
Regards
Jamie|||Thanks Jamie,
the FOR Each shredder was just what I needed. And many thanks to Kirk for all the direct and indirect input he has had on this solution.

Getting Error in 32bit DTEXEC on 64bit machine

I'm getting

will not acquire

a connection because the package OfflineMode property is TRUE. When the Offline

Mode is TRUE, connections cannot be acquired."

I'm running the 32bit DTEXEC on a 64bit machine since I'm using the Jet provider.

I see in the forum and other places that this error usually occurs when you try to run in 64bit.

I don't get this error when I've tried it, running the 64bit DTEXEC or when I run from BIDS on a 32bit machine.

Any help appreciated.

David

Hi, We are investigating this particular issue. Its unclear the various conditions/configurations which cause this to happen as in general what your attempting to do does work. As a work around try populating the package property OffLineMode as true with a configuration.

Thank you for your patience.

|||

Apologies, a compatriot pointed out I mistyped, and it should be false not true.

|||

The error is misleading. Two possible solutions. 1) try reinstalling the client tools. 2) Use package configuration to force System::OfflineMode = False

</DTSConfigurationHeading><Configuration ConfiguredType="Property" Path="\Package.Properties[OfflineMode]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration></DTSConfiguration>

|||

Solved.

We solved the issue by reinstalling just the client components onto the SSIS server.

This replaces the 32bit DTEXEC and now works.

What gave us a clue is the SSMS couldn't connect to the SSIS Service.

David

Getting error context from executed package

Background:

I am executing a package programmatically from ASP.NET using C#. The package is being loaded from SQL Server at runtime and then it is executed from a custom class written in C#.

Problem:

When a task fails, I'd like information on why it failed. I read about implemeting the IDTSEvents interface as a way to have my package call back to code. This works fine (I have code in the OnTaskFailed event handler), but I'm not sure how to find out why a task failed. Since my task is loading a flat file to SQL Server, failure will likely come in one of two flavors: either the file format will be wrong or the data will violate a database constraint. In etiher case, I'd like that feedback. I am looking that the TaskHost object that gets passed to my event handler and I haven't figured out how to access this information. Am I going about this the right way? If yes, how do I access the error information I am looking for?

Try the OnError event handler. There are variables scoped to that eventhandler that contain the error code and error description.

-Jamie

|||

OK. That works fine. I can now get the description of the errror. When I get an error, I'd like to stop executing the package and surface the error to the through the UI (which happens to be a web page). There are a couple of things I noticed with this call back to the OnError event handler:

The event handler is triggered multiple times even though I return false, which is supposed to cancel the task. What I'd like is to abort the execution of the package (not just cancel the task). I tried to throw a custom exception within the handler, but the exception doesn't propogate from the OnError handler to my calling code (the code that executes the package) and it doesn't stop the handler from being called multiple times.

Any ideas?

Getting error 0x80010112 while executing stored package

Hello everybody,

I can't find any posts concerning this error. While executing the stored package (using Execute Package Utility). I get error: SSIS Execution Properties "Could not load package "\File System\migration_package_1" because of error 0x80010112." The package is there and it was correctly loaded.

Please can anybody suggest what do do now or some workaround?

Thank you for any suggestions

Patrik Svestka

It seems this error was produced due to the SQL Server 2005 client & windows xp registry error. The only way out of this problem is complete system reinstalation :(.

Patrik Svestka