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 walkthroughs

Hi all,

I was wondering if anyone knew of any good walkthroughs to help me get started with SQL 2005, SSIS in particular.

I have checked through the newsgroups and Blogs but they all go way to far above myhead.

I found a Teched 05 one which was good, but cant seem to find any more

Does anyone know of a place for begineers to go ?

Or even any books I could get.

Im not trying to do anything complicted, all I want to do is create a table and then import a text file to it.

Also it is possible to download ready made packages so I can play around with them and see how they work ?

Sorry for asking so many questions, any help with any of them would be greatly appreciated :)

Thanks

I also found this walkthrough in the BOL

"Creating a Simple ETL Package" which also is good I thought, dont know how I missed it the first time around.

also found the following which were very good, pictures and everything !! ;)

http://www.extremeexperts.com/sql/Yukon/YukonDTS.aspx

Getting Started - What do I need to use SSIS?

OK. I am new to SSIS. I do not have control over the servers, so I have to provide a list of what features we need installed and such.

In order to use SSIS to import data files (from multiple flat file sources), what do I need installed?

I have SQL Server 2005 installed on a server. But the Business Intelligence Development Studio isn't present. Do I need to install that on the server? How about any services?

Also, can I/Should I install Business Intelligence Development Studio on my local development machine? What about licensing requirements for it?

Thank you for your help in this matter. Usually I have full control over the server and I can just poke through the install to get what I need.

Wow -- this is a big question, and you're going to get a lot of answers from different people.

The "Business Intelligence Development Studio" (BIDS) does not have to be installed on the server. The output from BIDS is a mangled XML file with a .DTSX extension. This file is processed by either the DTExec or DTExecui utility on the server. These utilities are installed along with SQL Server by default (in the Binn folder, I believe).

As a minimum, you'll need BIDS installed on your development machine. Because of licensing issues, DTExec and DTExecui only run on licensed servers. This means that, although you can create and run DTSX packages from your development machine from within BIDS, you won't be able to emulate server behavior on your local machine unless you've got a licensed copy of DTExec/DTExecui on your machine. IOW, you won't be able to run your packages at the Windows level without DTExec and DTExecui licensed on your development machine (this has hung up a lot of people -- the "insufficient product level" issue has been posted dozens of times on this forum)

Most of the SSIS tutorials you'll find on the Internet are laughably simplistic, and ignore real-world situations. You'll find dozens of brain-dead examples that import a well behaved 2- or 3-column flat file into an existing SQL Server table. While these examples are good for getting started, it'll take you 10 minutes of working with your own data to encounter a real-world situation such as permissions, ill-behaved data (missing delimiters, for instance), and issues with non-compliant data (such as column that look like dates, but aren't acceptable to SQL Server).

That's what this forum is all about. Lots of good help here.

|||

Thank you for the response. That answers a lot of my questions. I couldn't find anything that talked about getting started with this.

I'll go find the CD's to install BIDS locally. I think I can test everything well enough through BIDS before deploying to the server. This is also a new app, so we will have a full testing phase on the server to catch anything I miss.

I'll definately be back if I get stuck on writing the SSIS packages. I have 15+ file formats to massage all into the same table... :)

|||

That's precisely the sort of situation where the majority of examples you'll find will be of little help. Stuffing 15 or more formats into a single table will be challenging, depending on what you have to deal with.

Obviously, as with anything else, start with something you expect will be easy, such as plain text to a varchar or char column, and work up to the more challenging issues. In my case, I had some trouble with date values -- some of the columns I import are given as numbers (20070104), while others are text "01042007", and some include punctuation (01/03/2007 or 1-3-07). Cooercing these values into a format that's acceptable to SQL Server can be a challenge, sometimes.

Good luck! Jamie Thomsons blog (SSIS Junkie at http://blogs.conchango.com/jamiethomson/) is an invaluable source. By and large the support you'll get on this forum is top-notch and you shouldn't have too much trouble getting over the initial humps.

BTW: SSIS continues to surprise me an obvious paradox: while it's very powerful, it's got some of the quirkiest quirks I've ever seen in a Microsoft product. For instance, although the Script Task editor looks a lot like the standard Visual Studio editor, many of the menu commands do nothing -- you can't, for instance, "build" your script to check for errors. The only way I've found to validate my VB code is to actually run the package, or, at least, run the Script Task (right-click on a task in the Control Flow tab and select "Execute Task" from the shortcut menu) by itself to see if it's working.

Just a few of those "oh, so that's how it works" surprises now and then, but basically a good development platform. Much, much more powerful and flexible than DTS, to be sure.

sql

Getting SSIS running on Web Server

I need to be able to run SSIS packages form an asp.net (win 2k3) web server. Wrox has a book out "Professional SQL Server 2005 Integration Services" where they call the dtsx package directly using the following vb.net snipette:

Imports Microsoft.SqlServer.Dts.DtsClient

Dim ssisConn As New DtsConnection
ssisConn.ConnectionString = String.Format("-f ""{0}""", strMyFilePath)
ssisConn.Open()

As you would expect this works great on a workstation that has BIDS installed on it but does not work on a web server where sql client tools are not installed. Without install sql tools on the server what needs to be done to get this functioning as coded? How about calling packages that are installed on the server?

If anyone knows of any sites or books that cover this in detail I would appreciate the info. I only seem to be able to find bits and pieces.

thanks in advance

You can't. To my knowledge, you'll need the SSIS client tools installed. The packages are executed using dtexec (or a variant, I suppose).|||Searching turned this up.

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

I figured it wouldn't be possible to run packages from a web server w/out having ssis installed. I wanted to make sure that some combonation of dll's copied to the bin directory wouldn't get me what I needed. I opted to expose a web service on the sql server and consume the service from the web box. The following kb article describes how to do it via a job and a web service.

http://msdn2.microsoft.com/de-de/library/ms403355.aspx

Getting SSIS running on Web Server

I need to be able to run SSIS packages form an asp.net (win 2k3) web server. Wrox has a book out "Professional SQL Server 2005 Integration Services" where they call the dtsx package directly using the following vb.net snipette:

Imports Microsoft.SqlServer.Dts.DtsClient

Dim ssisConn As New DtsConnection
ssisConn.ConnectionString = String.Format("-f ""{0}""", strMyFilePath)
ssisConn.Open()

As you would expect this works great on a workstation that has BIDS installed on it but does not work on a web server where sql client tools are not installed. Without install sql tools on the server what needs to be done to get this functioning as coded? How about calling packages that are installed on the server?

If anyone knows of any sites or books that cover this in detail I would appreciate the info. I only seem to be able to find bits and pieces.

thanks in advance

You can't. To my knowledge, you'll need the SSIS client tools installed. The packages are executed using dtexec (or a variant, I suppose).|||Searching turned this up.

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

I figured it wouldn't be possible to run packages from a web server w/out having ssis installed. I wanted to make sure that some combonation of dll's copied to the bin directory wouldn't get me what I needed. I opted to expose a web service on the sql server and consume the service from the web box. The following kb article describes how to do it via a job and a web service.

http://msdn2.microsoft.com/de-de/library/ms403355.aspx

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 OLE DB connection properties in script

I have SSIS packages that send success/failure email upon completion, and I'd like to add a note that identifies the server and database used. I can certainly add variables to the packages and use them when constructing the email, but I'd prefer to get the information directly from the OLE DB connection itself. Is there a way to access the connection string from within a control-flow VB script task? Furthermore, can I get the data source and initial catalog from that connection string, or do I need to parse it myself?

Thanks!

Phil

Yeah you can do that. See here: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

If you don't want to parse it yourself you could try using the Properties collection using something like the following:

Dts.Connections("Errors").Properties("Format").GetValue(o)

Unfortunately that doesn't actually work cos I don't know what you're supposed to supply to the GetValue() method and the docs are a bit thin. But you can havea go with it if you like.

|||

Thanks, Jamie. You're right - I couldn't make it work with GetValue. However I was able to skin this particular cat thusly:

Dim _dbCxcn As String
Dim _inx As Integer
Dim _dbServer As String
Dim _dbCatalog As String

_dbCxcn = Dts.Connections("My_OLEDB_Connection").ConnectionString
_inx = InStr(_dbCxcn, "Data Source=")
_dbServer = Mid(_dbCxcn, _inx + 12, InStr(_inx, _dbCxcn, ";") - (_inx + 12))
_inx = InStr(_dbCxcn, "Initial Catalog=")
_dbCatalog = Mid(_dbCxcn, _inx + 16, InStr(_inx, _dbCxcn, ";") - (_inx + 16))

Then I constructed the email message using the _dbServer and _dbCatalog variables.

Thanks again!

Phil

Getting ID after Insert from AutoIncrement column in MS Access

I am inserting new records stored in SQL Server into a legacy MS Access application using SSIS. During the transformation, I need to get the ID MS Access assigned to the autoincrement column in the MS Access table I am inserting the row into. Is this possible? Can someone give me an example?

Thanks,

Steve

Not possible in a batch type insert like SSIS does.

Why not make your own "AutoIncrement" column inside SSIS? http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/|||

Thanks Phil. I did not think so, but thought I would ask. I need the actual ID from the Access table so I can update the record in SQL server. The solutions is migrating to SQL server but in the meantime, information can be updated in either Access or a web interface to SQL Server. We have to sync the data between the two.

|||It may be possible if you insert one row at a time, but I'm not sure how to return the last AutoIncrement value in Access. In SQL Server it's @.@.identity, but not sure in Access.

You can still calculate your own autoincrement number. If nothing else is inserting into that Access table, you can turn auto-increment off. Then using the page I listed earlier, you calculate the max value which seeds the starting number for your upcoming inserts. Just a thought.|||

How can I force the transaction to complete within a dataflow? To solve this problem of retrieving the primary key assigned by Access, I added another column to the Access table to write the SQL key. When I add the record from SQL Server to Access, I write the SQL Server key to this column. The next step of the data flow is to re-read the record using the SQL key and retrieve the Access key assigned to the autonumber column. The problem seems to be that when I get to this step of the dataflow, the record hasn't actually been written so it does not complete the Lookup transformation.

Is there a way to force the transaction or do I need to move this step to a new Control Flow?

Thanks,

Steve

|||You'll have to move it to a new data flow. While the data flow does process "row by row", rows are processed in buffers. So one buffer (of ~10000 rows by default) has to be processed through the lookup before the same buffer can be written to the destination.

getting error on Mail Task while attaching file of size > 2MB

Hi,

can any one help me , i am facing an issue on SSIS mail task, i want to attach a file of size 2 MB. it's getting error :

[Send Mail Task] Error: An error occurred with the following error message: "The operation has timed out.".

thanks,

sun.

Double check things like virus scan, the SMTP server, network issues, etc... First, try a smaller file and see if that works (if you haven't already).|||i already try for smaller file it's working fine. and there is no issue like virus scan, SMTP Server and networks..|||

I know that 2MB limits have been placed on attachments frequently (a setting somewhere) as a way to help prevent bandwidth consumption and malicious activity. Check with your SMTP server administrator.

I'm, of course, just trying to help you troubleshoot. I can't test this at the moment and it will have to wait till the morning here in the central US for me to be able to do so.

|||thank u so much for ur helping..Normally, i mean through my outlook i can send the same file.

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