A Custom component for use as a VIEW in SSIS- Is it possible to create one MERGE like component

Hi all

I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.

Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting?

(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)

You have a few options. You can implement views in the database engine (not sure why it breaks your data flow, but it's perhaps the best option), you can join your tables via SQL in an OLE DB Source component, or you can use multiple OLE DB Source connections and then use one or more merge join transformations.

Just an FYI - If your data is sorted with an ORDER BY clause in the SQL statement, you can set the ISSORTED flag to true (1) on the OLE DB source and a SORT component won't be required.|||

Thanks Phil for the quick response..

I said "using Views breaks my data flow" - I meant all views cannot be represented in SSIS data flow right? so it becomes difficult later on to understand/change the data flow coz I'll have to check views in database manager and other stuff in SSIS. OR is there a way we can export the whole data flow into some kinda diagram so I can see it all at once? (I don't think there is right?)

So my problem boils down to creating a VIEW type custom component. I have seen other custom component samples but don't know how to do JOINs via code. Can you give me a brief idea for, say, if I have to join 2 tables how many PipelineBuffers I'll need (2 for input and 1 for output?) and how do I match values between the two joining columns(col1.value==col2.value?)?

Guess this is a big question but Im sure it'll help a lot of developers.

thanks

Ravi

|||

If you want to power and performance gain of views and T-SQL, then use T-SQL. Use the relational engine for what it is good for is my opinion. A component to avoid having a view/SELECT when it would do the job best of all is just daft.

If you want some of the re-use of a view but without the object itself, you may want to look at Data Source Views. They are a design-time only feature, but are stored in your SSIS project so may meet your requirement. So you would use the same SELECT statement as in the view, but it would not be a SQL object.

|||

Hi Darren, I think u didnt get my problem right?

I just want help creating a MERGE type component which can join more than 2 inputs. I understand I can use more than one MERGEs to do the same, which ill do if creating the component is a pain ..

A Custom component for use as a VIEW in SSIS- Is it possible to create one MERGE like component

Hi all

I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.

Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting?

(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)

You have a few options. You can implement views in the database engine (not sure why it breaks your data flow, but it's perhaps the best option), you can join your tables via SQL in an OLE DB Source component, or you can use multiple OLE DB Source connections and then use one or more merge join transformations.

Just an FYI - If your data is sorted with an ORDER BY clause in the SQL statement, you can set the ISSORTED flag to true (1) on the OLE DB source and a SORT component won't be required.|||

Thanks Phil for the quick response..

I said "using Views breaks my data flow" - I meant all views cannot be represented in SSIS data flow right? so it becomes difficult later on to understand/change the data flow coz I'll have to check views in database manager and other stuff in SSIS. OR is there a way we can export the whole data flow into some kinda diagram so I can see it all at once? (I don't think there is right?)

So my problem boils down to creating a VIEW type custom component. I have seen other custom component samples but don't know how to do JOINs via code. Can you give me a brief idea for, say, if I have to join 2 tables how many PipelineBuffers I'll need (2 for input and 1 for output?) and how do I match values between the two joining columns(col1.value==col2.value?)?

Guess this is a big question but Im sure it'll help a lot of developers.

thanks

Ravi

|||

If you want to power and performance gain of views and T-SQL, then use T-SQL. Use the relational engine for what it is good for is my opinion. A component to avoid having a view/SELECT when it would do the job best of all is just daft.

If you want some of the re-use of a view but without the object itself, you may want to look at Data Source Views. They are a design-time only feature, but are stored in your SSIS project so may meet your requirement. So you would use the same SELECT statement as in the view, but it would not be a SQL object.

|||

Hi Darren, I think u didnt get my problem right?

I just want help creating a MERGE type component which can join more than 2 inputs. I understand I can use more than one MERGEs to do the same, which ill do if creating the component is a pain ..

sql

A Custom component for use as a VIEW in SSIS- Is it possible to create one MERGE like component

Hi all

I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.

Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting?

(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)

You have a few options. You can implement views in the database engine (not sure why it breaks your data flow, but it's perhaps the best option), you can join your tables via SQL in an OLE DB Source component, or you can use multiple OLE DB Source connections and then use one or more merge join transformations.

Just an FYI - If your data is sorted with an ORDER BY clause in the SQL statement, you can set the ISSORTED flag to true (1) on the OLE DB source and a SORT component won't be required.|||

Thanks Phil for the quick response..

I said "using Views breaks my data flow" - I meant all views cannot be represented in SSIS data flow right? so it becomes difficult later on to understand/change the data flow coz I'll have to check views in database manager and other stuff in SSIS. OR is there a way we can export the whole data flow into some kinda diagram so I can see it all at once? (I don't think there is right?)

So my problem boils down to creating a VIEW type custom component. I have seen other custom component samples but don't know how to do JOINs via code. Can you give me a brief idea for, say, if I have to join 2 tables how many PipelineBuffers I'll need (2 for input and 1 for output?) and how do I match values between the two joining columns(col1.value==col2.value?)?

Guess this is a big question but Im sure it'll help a lot of developers.

thanks

Ravi

|||

If you want to power and performance gain of views and T-SQL, then use T-SQL. Use the relational engine for what it is good for is my opinion. A component to avoid having a view/SELECT when it would do the job best of all is just daft.

If you want some of the re-use of a view but without the object itself, you may want to look at Data Source Views. They are a design-time only feature, but are stored in your SSIS project so may meet your requirement. So you would use the same SELECT statement as in the view, but it would not be a SQL object.

|||

Hi Darren, I think u didnt get my problem right?

I just want help creating a MERGE type component which can join more than 2 inputs. I understand I can use more than one MERGEs to do the same, which ill do if creating the component is a pain ..

A big, big SSIS for everything?

Dear all,

I would like to hear point of views, thoughts and ideas about the following request.

As some of us would have seen from a couple a days ago I’ve been setting posts one after one in order to obtain some answers about our business basis.

We’ve got a service running all day long (to accomplish this it was wrote by Visual Basic 6.0) which is checking up a file structure system for a several requirements such a verify if exists a .nul or .txt in each folder and therefore fires a DTS package. Up to here that’s fine, that system works amazing and even is be able to run till three packages at the same time. 400 Dts are scheduled this way and 100 are scheduled by Agent.

Problem comes naturally with SQL2005 where such control is not possible (it seems so) from old tools belonging to Visual Studio 6.0 because the aforementioned version uses assemblies instead of dll, bla,bla.

Keeping in mind that .NET is not possible for reasons that going beyond of any technical matter or whatever I’ve thought do that service by SSIS. A big SSIS running forever. Its responsabilities might be:

1-Look in each folder for the criteria specified in the scheduling of that SSIS

2-Launch a specified SSIS in the case that criteria has matched.

3-Update SQL tables with LOGS, times, executions, etc,

4-Look again

The point 3 will be visible from our Intranet via ASP 3.0 pages as currently we do.

Pros and cons?

Sorry for this painful request but I don’t believe that such issue can be of that kind of odd issues that nodoby suffer in its organizations.

Who else is needed for answers?

Thanks a lot for your time,

I'm not quite sure what your question is. Are you saying you want to run a SSIS package forever that simply keeps looping whenever a file is found? I don't see a problem with that. it would be interesting to see how it goes.

-Jamie

|||

When finds a Jamie.Nul file in d:\server25k\a\ launch SSIS package B and then would delete that .NUL.

Sensible information attached in that SSIS launched as flat file for example, accounts info would be stored in a backup folder. After one execution come back to verify and so on

64-bit SSIS

Newbie question on SSIS and 64-bit....Looking for pointers ...Thx in advance...
Can we develop SSIS pkgs on 32-bit machines and deploy to 64-bit?
Any software needed on 64-bit machine , like .NET 2.0 64-bit, etc...?
Any knobs/switches to do during deployment, to ensure pkg runs on 64-bit...?
Any gotchas...things to be aware of...?

If accessing SAP, SQL, Oracle, do OLE DB .NET drivers need to be 64-bit as well, if PKG is running on 64-bit machine...?

if developing SSIS pkgs on a 64-bit machine, any issues to be aware of?

Thx again

RC

RCHINTH wrote:

Can we develop SSIS pkgs on 32-bit machines and deploy to 64-bit?

Yes, in fact that is the way you're supposed to do it for Itanium servers since VS isn't supported on IA64.

RCHINTH wrote:

Any software needed on 64-bit machine , like .NET 2.0 64-bit, etc...?

No, 64-bit .NET 2.0 will be installed by your SQL 2005 setup process.

RCHINTH wrote:

Any knobs/switches to do during deployment, to ensure pkg runs on 64-bit...?

Not really. Script tasks have to have PreCompile=true to run on 64-bit. This is the default setting.

RCHINTH wrote:

Any gotchas...things to be aware of...?

Maybe. In my experience the Excel OLE DB Provider doesn't work on X64 (haven't tried it on IA64). Also, debugging Control Flow script tasks doesn't work when PreCompile=true (which is req'd for 64-bit). You can't debug Data Flow script components on either platform.

RCHINTH wrote:

If accessing SAP, SQL, Oracle, do OLE DB .NET drivers need to be 64-bit as well, if PKG is running on 64-bit machine...?

I don't know for sure, but the Excel problem above is probably a decent indication. Might depend on the provider.

RCHINTH wrote:

if developing SSIS pkgs on a 64-bit machine, any issues to be aware of?

I've not had any platform-related problems developing packages on an X64 server. Again, Visual Studio isn't supported on IA64.

Jay|||The absence of the Jet OLE DB Provider (which means Excel too) and the requirement of precompiled scripts seem to be the 2 most obvious limitations that you're going to run into on 64-bit.

Please see the BOL topic, "Integration Services Considerations on 64-bit Computers," for a summary of other considerations, most of which were touched on above.

-Doug

64 bit(part II)

In a recent post, I've read that Jet Provider is only provided with 32-bit.

I wonder, is there any problem if you run a SSIS package 32-bit which read from Access and then call another SSIS package 64 bit? We've got quite 2000 dts which pulling data from MDB and within a time it'll disappear in favour 2005 environment .

Another question, what sort of Providers don't offer us 64-bit versions?

Any link or thought will be as usual welcomed

It will work - you can run first package in 32-bit mode, move data to staging table or flat file, then call second package in 64-bit mode and do further processing of the data.|||The msdaora drivers also don't function in 64bit. This is a bit of a hassle since the oracle provided 64bit drivers don't work correctly. ODBC is also a bit tricky since you have to create dsn's for 32 bit and 64 bit since the desinger is 32 and the runtime is 64.|||Thanks to both for the info provided.

64 bit(part II)

In a recent post, I've read that Jet Provider is only provided with 32-bit.

I wonder, is there any problem if you run a SSIS package 32-bit which read from Access and then call another SSIS package 64 bit? We've got quite 2000 dts which pulling data from MDB and within a time it'll disappear in favour 2005 environment .

Another question, what sort of Providers don't offer us 64-bit versions?

Any link or thought will be as usual welcomed

It will work - you can run first package in 32-bit mode, move data to staging table or flat file, then call second package in 64-bit mode and do further processing of the data.|||The msdaora drivers also don't function in 64bit. This is a bit of a hassle since the oracle provided 64bit drivers don't work correctly. ODBC is also a bit tricky since you have to create dsn's for 32 bit and 64 bit since the desinger is 32 and the runtime is 64.|||Thanks to both for the info provided.

64 Bit SQL Server try to access 32-Bit

Hi All,

I have a scenario where today we are migrating the existing code to new production server which is 64 Bit server.

The SSIS packages are put on new server and we are trying to run.

I tried to run a simple select query to fetch data from a 32bit remote server from cmd prompt usings a .sql file and it gives me error.

But this is the known issue of

‘a 64-bit SQL server trying to communicate to a 32-bit sql server’ and the error message is

The error message is similar to the following:

The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "<LinkedServerName>". The provider supports the interface, but returns a failure code when it is used

I saw the help for this in support.microsoft and it is suggested the resolution is

‘To resolve this problem, install SQL Server 2000 SP3 or SQL Server 2000 SP4 on the 32-bit SQL Server 2000 server. Then, manually run the Instcat.sql script that is included with SQL Server 2000 SP3 or SP4 on the 32-bit SQL Server 2000 server.’

I cannot do this as the remote server which am trying to access is not under my control and other issues.

Please suggest me what is the work around and what modifications do I need to do for my SSIS packages?

Help much appreciated.

Thanks and Regards

Sai

Hello,

I’ve just had the same problem. I was trying to access a SQL2K from a SQL2K5 64bits server. Although I had it a as a linked server, I had the same error.

Reading information, I found the patch to solve this problem here

http://support.microsoft.com/kb/906954

But, maybe if you don’t have the SQL2K server under your control, you won’t be able to execute the patch.

There is another way to make this, using OPENQUERY

select * from openquery([RemoteServer],'select * from DB.dbo.table')

And you won’t have the error. I tried it before install the patch and it worked for me.

Krgrds

64 Bit SQL Server try to access 32-Bit

Hi All,

I have a scenario where today we are migrating the existing code to new production server which is 64 Bit server.

The SSIS packages are put on new server and we are trying to run.

I tried to run a simple select query to fetch data from a 32bit remote server from cmd prompt usings a .sql file and it gives me error.

But this is the known issue of

‘a 64-bit SQL server trying to communicate to a 32-bit sql server’ and the error message is

The error message is similar to the following:

The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "<LinkedServerName>". The provider supports the interface, but returns a failure code when it is used

I saw the help for this in support.microsoft and it is suggested the resolution is

‘To resolve this problem, install SQL Server 2000 SP3 or SQL Server 2000 SP4 on the 32-bit SQL Server 2000 server. Then, manually run the Instcat.sql script that is included with SQL Server 2000 SP3 or SP4 on the 32-bit SQL Server 2000 server.’

I cannot do this as the remote server which am trying to access is not under my control and other issues.

Please suggest me what is the work around and what modifications do I need to do for my SSIS packages?

Help much appreciated.

Thanks and Regards

Sai

Sai,

You will need to contact the system administrator for the remote server and convince them to run the instcat.sql. I had exactly the same issue at our site, and this resolved the issue. Assuming the remote system is running SQL Server 2000 with at least SP3, there is no installation required, as the instcat.sql script will already exist on the machine. In fact, it isn't even necessary to get a console login to the box as the KB article suggests; the script can be run via Query Analyzer from a remote location. You will find the script in the <installation folder>\MSSQL\Install directory.

The script updates some of the Microsoft-delivered system stored procedures to the SP3 or SP4 version. It should not otherwise impact the normal functioning of any user databases or user stored procedures; at least, it caused no ill effects at our site, and it DID solve the problem. Perhaps you can use this information to help convince the other sysadmin of the benefit of executing the script.

Best regards,

Matt

64 Bit OLE vs. 32 Bit OLE

I have a SSIS package that works on Windows Server 2003 32 Bit with SQL Server 2005 32 Bit when I move it to a Windows Server 2003 64 Bit (AMD) with SQL Server 2005 64 Bit it fails with error 80040154 on the connection to an Access 2000 database. The solution maybe Jet 4.0 SP8 however I can not find SP8 for Windows Server 2003 64 Bit AMD (I can find Jet 4.0 SP8 for Windows Server 2003 32 Bit, Windows Server 2003 64 Bit Itanium however).

Sorry, but there is no Jet support for 64 bit so you would have to run this package as 32 bit only.

HTH,

Matt

|||I understand that Access is 32 Bit and the system I am replacing is running on this server using 32 Access (2000), the is also SP8 for Itanium 64 Bit so are you saying that the only 64 Bit OLE connection to an Access database that is supported is the Itanium 64 Bit system?|||

It is my understanding that there is no jet support for 64 bits. However, since this is an SSIS forum there could be new releases of Jet that are addressing this. To find out for sure you should ask on that forum as they would know better than we do.

Matt

|||I posted it here because all other access to these databases work. In fact SSIS Import Wizard will build a project for this, testing the connection works, selecting the desired table works, it all works until I run it. NOTE: If I preview the connection the preview works however it will general a "system out of memory"......|||

The problem is that the wizard uses different access mechanisms to get the information than the actual package. The package uses OLEDB to access jet and that (to the best of my knowledge) does not support 64 bit unless it has changed recently. If it has then you should ask on the jet forum what that specific error code means coming from their OLEDB provider because that is usually the error for class not registered and that has always been the error we get due to non 64 bit support for jet (as the class isn't registered in the 64 bit registry since it doesn't support 64 bit).

Thanks,

Matt

|||Ok, where do I find the jet forum?|||Just to make sure you know this - you can run the package as 32-bit and use 32-bit OLEDB providers on 64-bit system. You need to use 32-bit dtexec.exe from c:\Program Files (x86)\Microsoft Sql Server\...|||Thanks Michael! Based on that I found that there is a property in Visual Studio for a project "Run64BitRuntime" which is set to true by default, I set it to false and the OLEDB connection works!

64 Bit drivers IBM Db2

Hey guys .. I am trying to run SSIS on the WIn2k3 .. The server is a 64 bit machine. I could not find Db2 drivers for a 64 bit one .. any clues as to how , I can find it. By the way .. I have been running the package in 32 bit mode for now .. which I dont think is a very good idea.

Any suggestions are most welcome ..

Have you tried the Microsoft one (http://www.microsoft.com/downloads/details.aspx?familyid=D09C1D60-A13C-4479-9B91-9E8B9D835CDC&displaylang=en)

The standalone Microsoft OLE DB Provider 2005, which ships in the Feature Pack for SQL Server 2005, offers a single download package and setup program. The setup will detect the processor and install (1) X86 only on Windows X86, (2) x86 side-by-side X64 on Windows X64, or (3) x86 only on Windows IA64. The other Microsoft OLE DB Provider for DB2 that ships in HIS 2006, offers x86 only. The end result of running "either" setup program is an X86 only DB2OLEDB provider.

If you don't want the WOW64 "work-around" for SSIS, you must get the 64 bit provider for DB2 from IBM. Its part of their standard client install kit.

64 bit cluster cannot connect using dtutil

Hi,

I have a 64 bit Itanium SQL Cluster that I'm trying to set up. The database engine seems fine, but I'm trying to install some SSIS packages using dtUtil and I keep getting a connection error. The error is as follows

"Login timeout expired

An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default seetings SQL Server does not allow remote connections.

Named Pipes Provider: Could not open a connection to SQL Server [2}. (Microsoft SQL Native Client)"

Also, in Management studio, I can make a connection to my SSIS Server, but when I go down to the MSDB folder to look at packages I get the same connection error.

I think I was able to deploy a package using the Deployment Manifest created by VS, but I'm not sure because I can't see the package in Mgt Studio.

It almost seems like it may be related to the cluster. I'm connected in to one of the Servers using Terminal Services. But I am trying to connect to the Cluster name in Mgt Studio.

Any thoughts/ideas/Solutions greatly appreciated.

Thanks.

Andy

Another note, I was able to get the dtUtil working by using the argument /DestS, which is the destination server, and specifying the cluster name. However, I still have the issue that I can't actually use Management Studio to view these packages, I still get the above error. I'm able to run my packages using SQL Server Agent, and when I go to create a job that calls an ssis package, I'm able to see the list of packages installed on the cluster?!?! Very weird.

My main issue is I can't use Management studio to Manage my packages. Help!

Andy

|||

I found the problem. Because of the cluster, the MsDtsSrvr.ini.xml file had a "." for the servername (I found this from another post that was due to 2 instances on a machine. I had to change the "." to our cluster name and now it works fine.

|||

Hi,

Question for you - in your case Ingration Service runs under cluster? In my case I installed IS on 2 nodes separately, runs as a service and when I changed MsDtsSrvr.ini.xml to PC/node name, restart service it did not work. But when I changed in ini names of folder itself it presents on IS management studio.

Thanks

64 bit cluster cannot connect using dtutil

Hi,

I have a 64 bit Itanium SQL Cluster that I'm trying to set up. The database engine seems fine, but I'm trying to install some SSIS packages using dtUtil and I keep getting a connection error. The error is as follows

"Login timeout expired

An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default seetings SQL Server does not allow remote connections.

Named Pipes Provider: Could not open a connection to SQL Server [2}. (Microsoft SQL Native Client)"

Also, in Management studio, I can make a connection to my SSIS Server, but when I go down to the MSDB folder to look at packages I get the same connection error.

I think I was able to deploy a package using the Deployment Manifest created by VS, but I'm not sure because I can't see the package in Mgt Studio.

It almost seems like it may be related to the cluster. I'm connected in to one of the Servers using Terminal Services. But I am trying to connect to the Cluster name in Mgt Studio.

Any thoughts/ideas/Solutions greatly appreciated.

Thanks.

Andy

Another note, I was able to get the dtUtil working by using the argument /DestS, which is the destination server, and specifying the cluster name. However, I still have the issue that I can't actually use Management Studio to view these packages, I still get the above error. I'm able to run my packages using SQL Server Agent, and when I go to create a job that calls an ssis package, I'm able to see the list of packages installed on the cluster?!?! Very weird.

My main issue is I can't use Management studio to Manage my packages. Help!

Andy

|||

I found the problem. Because of the cluster, the MsDtsSrvr.ini.xml file had a "." for the servername (I found this from another post that was due to 2 instances on a machine. I had to change the "." to our cluster name and now it works fine.

|||

Hi,

Question for you - in your case Ingration Service runs under cluster? In my case I installed IS on 2 nodes separately, runs as a service and when I changed MsDtsSrvr.ini.xml to PC/node name, restart service it did not work. But when I changed in ini names of folder itself it presents on IS management studio.

Thanks

3rd party components/API support in SISS

Greetings,

I was just getting through the features of SSIS, i wanted to confirm a few things

can i make my own dll in .net (version 2.0 or earlier) and use them im SISS via Execute Script? thers is one thing i saw on msn that i can make my ssis packages reusable by adding them on mt tool box and drag drop use them on other ssis packages. correct? can i use any other 3rd party components like APIs, or some other ETL tool's package in SISS ?

regards,

adnan shamim

1. Yes, you can use the Script task or component to reference external .NET assemblies.

2. You can't add a package to your toolbar. You can call packages from other packages. And you can create custom tasks and components to reuse across multiple packages.

3. You can call outside APIs from the script task or component. You can also use Execute Process to run external executables.

|||Thank you very much for the timely response welch! . i really appreciate it.