How do I get SSIS to do this...?

I feel like I'm losing my mind. I can write code in 7 different languages, but I can't figure out how to create an SSIS package to do the following:

Table A, 6 columns: EmpID, Code1, Code2, Code3, LocationCode, ScriptPath

Table B, 5 columns: Code1, Code2, Code3, LocationCode, ScriptPath

Using the values in the Code columns for lookup, I need to pull the most specific value from Table B and store it in Table A. So the task is to fill in the LocationCode and ScriptPath columns in Table A from Table B using the following logic:

If

Table B has a row where Code1,

Code2 and Code3 all match the

appropriate values in the Table A

row then copy the LocationCode

from Table B to that row in Table A.

Else If

Table B has a row where Code1

and Code2 both match the appropriate

values in the Table A row then copy

the LocationCode from Table B to

that row in Table A.

Else If

Table B has a row where Code1

matches the appropriate value

Table A row then copy the

LocationCode from Table B to

that row in Table A.

Else

Place a default value in that row

in Table A.

Same logic for the ScriptPath.

The logic is pretty straight forward, but I can't wrap my brain around how to turn this into an SSIS package.

Can someone give me a quick sketch of what components to use to create something like this?

Thanks.

J

Roughly,

Lookup on 1, 2, 3

->Successful lookup -> Union All

->Failed lookup -> Lookup on 1, 2

->Successful lookup -> Union All

->Failed lookup -> Lookup on 1

->Successful lookup -> Union All

->Failed lookup -> Derived Column (for default) -> Union All

It's the same Union All for all branches.

Honestly, I think I'd do this in a Exec SQL, and not a data flow. If I am understanding your question properly, you will be updating Table A, so you'd either have to use an OLE DB Command in the data flow to issue the update, or write it to a temp table and issue an Exec SQL after the data flow.

|||

I'm writing it as a stored procedure now, but I just wanted to get my hands dirty with the SSIS stuff - so I gave it a shot.

I can't even figure out how to get the lookups to work properly in SSIS. When I play with it some more, I'll post the error that I'm getting. Maybe you can tell me what I'm doing wrong at that point...

Thanks for the response.

J

How do I force new databases to be created on another drive on same server?

I successfuly installed SQL Server 2005 Enterprise Edition today (on the first shot!) but can't figure out how to force new databases, etc to be created on another drive on the server. SQL installed on the c: drive (I had no choice!) but it is a very small partition. I need to install Sharepoint Portal 2003 next (on a different server) and want to make sure everything goes on the other drive.

Help me, please!!

Thanks :-)

As far as I can remember Sharepoint let you choose the destination of the data files. Anyway the default data directory can be changed in the registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||Thanks, I'll give it a try.|||

I just tried it and it is still creating new databases on c: drive. We had to slick the server for another reason, so I have a fresh install and told it to install to d:\Program Files\.... during installation and the registry entry did not change; it still pointed to c: drive. I created a d:\Program Files\Microsoft SQL Server\Data directory and changed the registry and no joy. Even restarted the server a few times and it didn't matter.

Shouldn't there be a way to change this default in the Server Management Studio?!?

If any can help me, it would be appreciated! This problem is holding up installation of a few more apps.

Thanks!!

|||Sorry for that I copied the wrong key :-(

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

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi,

Thanks. I actually found out where to do it in the SQL Server Management Studio!

SERVER--PROPERTIES--DATABASE SETTINGS

The path for databases and logs are blank. After I selected my folders on the D: drive I created a new db and it went to the correct path! Whew. Glad that's over with :-)

How do I figure out where SSIS logs are?

Dear all,

I was wondering how to open .LOG files created for a SSIS execution package.

Thanks in advance,

Open them in a text editor. I'm a bag fan of Textpad (www.textpad.com)

You control where they are. You tell tell the package where to log to.

-Jamie

|||

Hi Jamie,

Come on man, textpad? Is my favourite without a doubt, I like it a lot too.

I was saying, in what path ssis leave its logs. where?

Thanks again

|||

Like I said previous, the logs are put wherever you tell SSIS to put them. That can be a text file (it is you, not SSIS, that defines where that file resides), SQL Server (in which case they'll be in the msdn..sysdtspackageslog90 table if memory serves), event log, SQL Profiler or an XML file.

-Jamie

How do I figure out time and date at the same time?

Dear all,
I've got some problems with date and time (very silly, I know)
select * from crm_1 where log < datepart(yyyy,getdate())
and log < datepart(mm,getdate())
and log < datepart(dd,getdate())
The aforementioned query doesn't find this value:
2005-06-09 08:27:17.810
CREATE TABLE [dbo].[CRM_1] (
[ipcliente] [varchar] (15) COLLATE Traditional_Spanish_CI_AS NOT NULL ,
[log] [datetime] NOT NULL)
GO
Thanks a lot and regards,I've elaborated on that topic here: http://www.karaszi.com/SQLServer/in...ime
.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:59611C1B-3D45-407E-B593-3D4CB5C9A0E8@.microsoft.com...
> Dear all,
> I've got some problems with date and time (very silly, I know)
> select * from crm_1 where log < datepart(yyyy,getdate())
> and log < datepart(mm,getdate())
> and log < datepart(dd,getdate())
> The aforementioned query doesn't find this value:
> 2005-06-09 08:27:17.810
> CREATE TABLE [dbo].[CRM_1] (
> [ipcliente] [varchar] (15) COLLATE Traditional_Spanish_CI_AS NOT NULL ,
> [log] [datetime] NOT NULL)
> GO
> Thanks a lot and regards,|||Try this:
select * from crm_1 where datepart(yyyy,log) < datepart(yyyy,getdate())
and datepart(mm,log) < datepart(mm,getdate())
and datepart(dd,log) < datepart(dd,getdate())
Or better yet, do some more reading as Tibor suggests.
ML|||It has been very useful. thanks a milion
"Tibor Karaszi" wrote:

> I've elaborated on that topic here: http://www.karaszi.com/SQLServer/in...i
me.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:59611C1B-3D45-407E-B593-3D4CB5C9A0E8@.microsoft.com...
>