Install DTS packages on development machine

I would like to install the components I need to develop a SSIS custom component. Is there an SDK or install option for just the visual studio components?

There is no seperate redistributable or SDK for SSIS. There is actually an SDK folder that gets installed, normally C:\Program Files\Microsoft SQL Server\90\SDK which has SSIS related assemblies for referencing, but I am not sure which install option gives you this. I suggest you run setup and select "Integration Services" and also "Workstation Components..."

A nice blog post about installing SSIS and some of the options-

Michael Entin's WebLog : Why do I get "product level is insufficient..." error when I run my SSIS package?
(http://blogs.msdn.com/michen/archive/2006/11/11/ssis-product-level-is-insufficient.aspx)

Insertion / updation problem in SSIS

“I have a scenario where i am trying to insert 200,000 lac records & update 200,000 lac record in destination table using SISS Package (SQL SERVER 2005) but I am not able to neither update nor insert the records . while executing the package its not showing any error also . what could be the problem ? “

We have business logic in Package creation 1) Insert New records and 2) Update Existing Records using the follow Data flow diagram

For update we are using OLEDB command, for insert we are using OLEDB Destination.

We are using merge join for spliting record into insert and update.

Perhaps there is blocking on the destination table. This can often happen if you're attempting 2 operations simultaneously.

Execute sp_who2 to see if there's any blocking going on.

-Jamie

|||

is there any other solution for this problem, is this not possible to run for achive both insertion and updation in the same package? if i try to run the package for less records, it is succeded, if i try to run the package for more records, then same problem coming again and again

Thanks & Regards

S.Nagarajan

|||

In that case I am even more sure that blocking is a problem. Did you bother to execute sp_who2 like I advised?

There is an easy fix to this problem. Continue to do the insert but push teh adta to be updated into a raw file. You can then use the contents of the raw file in another data-flow in order to do the update,.

-Jamie

|||I ran sp_who2 and found the blocking, how can i remove the blocking, is there any query to remove the blocking. I couldn't get your solution clearly, please brief me your alternate solution.|||

Do you know what raw files are? If not, go away and study them. When you have finished read #1 here: http://blogs.conchango.com/jamiethomson/archive/2006/02/17/2877.aspx

It describes a different scenario for using raw files but the usage is the same.

-Jamie

|||

We have completed upto move to flat destination files using raw files, could you please help to move this flat files data (for update) to database. what logic we need to use? is it required to add dataflow diagram after raw file destination component

|||

Please stop writing the same question in multiple threads simultaneously. People are here to help and don't want to waste their time clicking through and reading the same thing more than once.

-Jamie

Inserting XML with SSIS - VERY URGENT!!!

Hello everybody,

I have a problem. I need to insert an unknown number of xml files in a database (all files are always in the same folder), in different tables, each file has the same name that the corresponding table. For example:

Files Tables

user.xml user

purchase.xml purchase

...and so

but the number of files is not always the same, I mean, it can be 6 one day and only 4 the next day.
Can I insert the data in the xml files into the tables with a Foreach Loop Container or any other way? If it's possible, how?

Thanks in advance for your help,

Radamante71

You might want to post your question at SQL Server Integration Services forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1.

Inserting to Dimension on Failed Lookup

First, I'm very new to SSIS, so forgive any stupid questions.

I'm trying to normalize some data by creating rows in a dimension table as I find new values in the original table.

My first thought is to use a Lookup to replace string values with id's to the Dimension table, and when the Lookup fails, insert the value into the Dimension table with a new key.

Does that make sense? This would be relatively easy to do with TSQL, but surely there is a way to do this in SSIS.

Thoughts?

Greg,

Why you do not, first hand, create a default(error, unknown, etc) member in every dimension and then if the lookup fails assign the id of the default. Anyway, I think that has been disscussed in previous threads; here is one.

http://forums.microsoft.com/msdn/showpost.aspx?postid=75211&siteid=1

Rafael Salas

|||Thanks! I will read that thread...

Responding to your suggestion, however, I can't use a default value because if the lookup doesn't work then a new record needs to be inserted into the Dimension table.

For example, if my row has "XXX" as a value and that value isn't in the Dimension table, I want to insert the row with the KEY rather than the VALUE("XXX") and insert the KEY/VALUE pair into the dimension table

Basically, I want to do an insert into dimension (select distinct value from facts) before I do a lookup to ensure that all lookups will be successful.|||

I read that thread, and the example that Thomas Pagel blogs on is what I'm looking for...

however, this really seems like an unnecessarily complicated approach.

Since I'm new to this, I'm inclined to think that I'm making it more difficult that it needs to be, but can't think of another approach.

If I was scripting this in TSQL, or SQL, I'd do a select of the distinct values from the FACTs table, insert those into the dimension, and then for each row in the FACTS table replace the value with the key from the new Dimension table.

Is this approach inconsistent with how SSIS is intended to be used?

|||

Greg Akins wrote:

however, this really seems like an unnecessarily complicated approach.

Since I'm new to this, I'm inclined to think that I'm making it more difficult that it needs to be, but can't think of another approach.

I really think this approach over complicates things. Not matches between a fact and its dimensions should be an exceptional case rather than the driver of your dimension load process.

Greg Akins wrote:

If I was scripting this in TSQL, or SQL, I'd do a select of the distinct values from the FACTs table, insert those into the dimension, and then for each row in the FACTS table replace the value with the key from the new Dimension table.

I would to exactly the same in SSIS; in general dimensions should be processed before the facts, even if like in your case, they come from the same source.

Greg Akins wrote:

Is this approach inconsistent with how SSIS is intended to be used?

Not sure if there is an answer for that...

Rafael Salas

|||OK. Thanks alot. I appreciate your advice.