query

hi,

i have a problem as a sql developer.i have never used DTS packages for
sending data in SQL databases. but i used SSIS which is a new feature
in SQL server 2005.
so do any of you think SSIS is better now a days and mostly used
compared to DTS.

jittu..On Feb 18, 7:51 pm, rrgh...@.gmail.com wrote:

Quote:

Originally Posted by

hi,
>
i have a problem as a sql developer.i have never used DTS packages for
sending data in SQL databases. but i used SSIS which is a new feature
in SQL server 2005.
so do any of you think SSIS is better now a days and mostly used
compared to DTS.
>
jittu..

I believe that SSIS in SQL 2005 is the replacement for DTS in SQL 2000
and older. I myself have not yet had the opportunity to use SSIS as
we do not yet have any production instances of SQL 2005, but from what
I have heard SSIS is more robust then DST was.

Queries using tables from diffrent Databases or SQL instances

Hello,

I am new in SSIS.

I am using an OLEDB source and setted as SQL Command.

The Query is a JOIN between different databases.

How can I make the QUERY with different source (different databases or SQL Servers)?

I mean, any solution is OK, the important is to make queries against different databases with SSIS.

Thank

You can always use two or more OLE DB sources and then use a union all or Merge Join transformations.|||

As Phil wrote in the previous post, you can add several OLEDB sources or others sources and join the data adding a UNION ALL or MERGE JOIN.

In merge Join the input data must be sorted and could ne joined by LEFT or RIGHT.

Regards!

|||

Thank|||

If you have to use an Execute SQL Task, you can do this by creating Linked servers, it can be SQL server or not.

Then use fully qualified names.

I use this to compare tables content side by side accross similar servers.

i.e.

Select s1.ColumnA as Server1_Status, s2.ColumnA as Server2_Status

From Server1 s1

Join Server2 s2 on s1.Pkey = s2.Pkey

Where Server2 is an entry in Server Objects, Linked Servers

For everything else, I use the other options like multiple pumps and Union All.

It performs better, especially when pulling from non-SQL databases.

Regards,

Philippe

queation about foreach loop

All,

I’m new to SSIS and have a question about foreach loop container.

Basically, I have 4 data flows and each of them has a variable that need be pass on to the next step, each of the variables will call a same stored procedure.

What should I use here? I guess I can use foreach loop container with the stored procedure inside of it, is that right? I tried all day yesterday and cannot make it work.

The foreach loop container did not call the procedure at all. Could someone kindly tell me how to do it?

If you are calling the same sproc 4 times, each time with different parameters then yes, the ForEach loop can be of help here.

You will need an execute SQL Task inside your ForEach loop container. The ForEach loop container itself doesn't actually call the sproc as you seem to think it will as suggested by the last line of your post.

-Jamie

|||

Jamie,

Thanks for your reply, Your blog helped me a lot. Thank you so much for putting such helpful information out there.

Back to my question, IF the foreach container is not supposed to call the Execute SQL task, then what should make it work?

Below is what I did, I don't know what i'm missing. I just cannot make it work!!!

*******************

ForeachLoop Editor:

Collection Tab:Foreach fromVariable Enumerator is the Enumerator

In Enumerator configuration, I created a new variable (V_EF) with the same datatype as the variable from the data flows

Variable Mappings tab: I added the 4 variables (come from each data flow) and the variable index are all 0

Expressions Table: I didn’t anything here because the procedure doesn’t return any value, what it does is update tables

Execute SQL Task Editor:

General Tab: ConnnectionType is OLEDB

SqlsourceType is Direct input

SQL statement isEXEC myProcedureName ?

bypassPrepare is false

Parameter Mapping: I use the variable (V_EF) I defined in Enumerator configuration

Result Set Tab: nothing here because the procedure will not return anything back

Expressions Tab: Nothing here

Many thanks!!

Jessie

|||please........|||

Jessie,

Under what circumstances do you not want to execute the Execute SQL Task?

Once you have answered that question then hopefully you can achieve what you need using expressions on your Precedence Constraints. Darren and Allan have a great demo of this here: http://www.sqlis.com/default.aspx?306

-Jamie

QI for IEnumVARIANT failed on the unmanaged server

I have an SSIS project created with Beta 2 of VS2005. When I try to open the SSIS Package in the Designer with the final version of VS2005 I get the error message:

Microsoft Visual Studio is unable to load this document
QI for IEnumVARIANT failed on the unmanaged server

Before that happens there is a warning Message: "Document contains one or more extremely long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still want to open the file?"

When click on Yes to this question the above Error occurs. In the Beta I got the same warning message, but the package was still able to load in the designer.

Is there a way to recover or migrate my package so that I can use it in the final VS2005?

Torsten

I get a very similar problem. I'm just starting on my learning curve for SSIS and am trying to create a new package using the SQL Server Import Export Wizard. The wizard fails and reports the exact same error you mention.|||

Possible problem is InstallShield. Here is their solution: http://support.installshield.com/kb/view.asp?articleid=q106194

|||Bien!
Esa es la solución.

Recomendación: No usar InstallShield, un fallo como este es imperdonable

Prueba y aprende NSIS|||

RoMoVi wrote:

Bien!
Esa es la solución.

Recomendación: No usar InstallShield, un fallo como este es imperdonable

Prueba y aprende NSIS

These are English based forums. Please avoid the use of Spanish.

I don't believe NSIS is a solution to the InstallShield problem as a simple upgrade to InstallShield should suffice.

QI for IEnumVARIANT failed on the unmanaged server

I have an SSIS project created with Beta 2 of VS2005. When I try to open the SSIS Package in the Designer with the final version of VS2005 I get the error message:

Microsoft Visual Studio is unable to load this document
QI for IEnumVARIANT failed on the unmanaged server

Before that happens there is a warning Message: "Document contains one or more extremely long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still want to open the file?"

When click on Yes to this question the above Error occurs. In the Beta I got the same warning message, but the package was still able to load in the designer.

Is there a way to recover or migrate my package so that I can use it in the final VS2005?

Torsten

I get a very similar problem. I'm just starting on my learning curve for SSIS and am trying to create a new package using the SQL Server Import Export Wizard. The wizard fails and reports the exact same error you mention.|||

Possible problem is InstallShield. Here is their solution: http://support.installshield.com/kb/view.asp?articleid=q106194

|||Bien!
Esa es la solución.

Recomendación: No usar InstallShield, un fallo como este es imperdonable

Prueba y aprende NSIS|||

RoMoVi wrote:

Bien!
Esa es la solución.

Recomendación: No usar InstallShield, un fallo como este es imperdonable

Prueba y aprende NSIS

These are English based forums. Please avoid the use of Spanish.

I don't believe NSIS is a solution to the InstallShield problem as a simple upgrade to InstallShield should suffice.

QI for IEnumVARIANT failed on the unmanaged server

I have an SSIS project created with Beta 2 of VS2005. When I try to open the SSIS Package in the Designer with the final version of VS2005 I get the error message:

Microsoft Visual Studio is unable to load this document
QI for IEnumVARIANT failed on the unmanaged server

Before that happens there is a warning Message: "Document contains one or more extremely long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still want to open the file?"

When click on Yes to this question the above Error occurs. In the Beta I got the same warning message, but the package was still able to load in the designer.

Is there a way to recover or migrate my package so that I can use it in the final VS2005?

Torsten

I get a very similar problem. I'm just starting on my learning curve for SSIS and am trying to create a new package using the SQL Server Import Export Wizard. The wizard fails and reports the exact same error you mention.|||

Possible problem is InstallShield. Here is their solution: http://support.installshield.com/kb/view.asp?articleid=q106194

|||Bien!
Esa es la solución.

Recomendación: No usar InstallShield, un fallo como este es imperdonable

Prueba y aprende NSIS|||

RoMoVi wrote:

Bien!
Esa es la solución.

Recomendación: No usar InstallShield, un fallo como este es imperdonable

Prueba y aprende NSIS

These are English based forums. Please avoid the use of Spanish.

I don't believe NSIS is a solution to the InstallShield problem as a simple upgrade to InstallShield should suffice.

QI for IEnumVARIANT failed on the unmanaged server

I have an SSIS project created with Beta 2 of VS2005. When I try to open the SSIS Package in the Designer with the final version of VS2005 I get the error message:

Microsoft Visual Studio is unable to load this document
QI for IEnumVARIANT failed on the unmanaged server

Before that happens there is a warning Message: "Document contains one or more extremely long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still want to open the file?"

When click on Yes to this question the above Error occurs. In the Beta I got the same warning message, but the package was still able to load in the designer.

Is there a way to recover or migrate my package so that I can use it in the final VS2005?

Torsten

I get a very similar problem. I'm just starting on my learning curve for SSIS and am trying to create a new package using the SQL Server Import Export Wizard. The wizard fails and reports the exact same error you mention.|||

Possible problem is InstallShield. Here is their solution: http://support.installshield.com/kb/view.asp?articleid=q106194

|||Bien!
Esa es la solución.

Recomendación: No usar InstallShield, un fallo como este es imperdonable

Prueba y aprende NSIS|||

RoMoVi wrote:

Bien!
Esa es la solución.

Recomendación: No usar InstallShield, un fallo como este es imperdonable

Prueba y aprende NSIS

These are English based forums. Please avoid the use of Spanish.

I don't believe NSIS is a solution to the InstallShield problem as a simple upgrade to InstallShield should suffice.

q; using SSIS

Where do I start to use SSIS in SQL 2005? Service seems running but I do not
know how to start using it.
You can use the SQL Server Business Intelligence Development Studio to
develop SSIS packages. BI Studio is the Visual Studio IDE with specialized
SQL Server BI project types.
See the SQL Server Books Online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlovr9/html/44003d30-de23-4493-9631-2b53936a8564.htm)
for more information..
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:96B427C0-034C-463B-8EA6-E60173E7777B@.microsoft.com...
> Where do I start to use SSIS in SQL 2005? Service seems running but I do
> not
> know how to start using it.

q; using SSIS

Where do I start to use SSIS in SQL 2005? Service seems running but I do not
know how to start using it.You can use the SQL Server Business Intelligence Development Studio to
develop SSIS packages. BI Studio is the Visual Studio IDE with specialized
SQL Server BI project types.
See the SQL Server Books Online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlovr9/html/44003d30-de23-4493-9631-2b53936a8564.htm)
for more information..
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:96B427C0-034C-463B-8EA6-E60173E7777B@.microsoft.com...
> Where do I start to use SSIS in SQL 2005? Service seems running but I do
> not
> know how to start using it.

q; using SSIS

Where do I start to use SSIS in SQL 2005? Service seems running but I do not
know how to start using it.You can use the SQL Server Business Intelligence Development Studio to
develop SSIS packages. BI Studio is the Visual Studio IDE with specialized
SQL Server BI project types.
See the SQL Server Books Online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlovr9/html/44003d30-de23-4493-9631-
2b53936a8564.htm)
for more information..
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:96B427C0-034C-463B-8EA6-E60173E7777B@.microsoft.com...
> Where do I start to use SSIS in SQL 2005? Service seems running but I do
> not
> know how to start using it.