Configuration

Hi,

In my package I am using some 3 to 4 database connections. Hence I can say 3 to 4 connection managers I have. Other than that I have 2 Flat file connection manager.

What will be the best way to configure the same? and Please let me knowwhy that is the best choice.

Thanks

There is no "best" way. It depends on your environment and what works for you.

Personally I use XML configuration files. And I like to have a seperate configuration file for each connection manager. And I like to use indirect configurations.

but that's just me!

-Jamie

Conditionally entering flat file data into relational Stucture

Hi,

I'm working on importing some data into my database. The data is currently in a SQL Server Table. I'm new to working with triggers (or performing more complex sql commands for that matter) but am trying to use a trigger on a view(that is identical to the table being imported) to handle a bulk insert.

I was under the impression that the "inserted" table handles one record at a time. When I figured out that isn't the case, I felt stumped as to how to make importing decisions on each record.

I have a Research Project Table consisting of ID,ProjectNum,Title
Also, there is a ResearchProjectPeriod table consisting of ID,ProjectID,From,To,DirectCost,IndirectCost

I'd like to check for projectNum on the records to import and if it exists in my database then only create associated entry in ResearchProjectPeriod table. If there is no associated ProjectNum then I would like to create the project as well as create an associated ResearchProjectPeriod Record.

Thanks for your help.

StephenHi,

You could inport the data into a temp table and then go through the imported rows one by one or by batch.

Hope it helps!

Conditional split with dependence?

I have setup a SSIS package that takes a flat file fixed width input, and stores it to two SQL server tables in the same database. The flat file contains two types of records, lets call them Type1 and Type2. The two types of records are formatted differently, and the first character determines what type the record is. I used a conditional split to send record type1 down one path, and type2 down the other. On each of those I use a derived column task to build all the fields and then store to the table with the OLE destination. I put any errors that occur (like truncation) into an error table by setting the "redirected row" feature vs "Fail Component". This all works well and I have no issues.

The dilema is as follows. Type1 is essentially a parent record and the Type2 record is a child. There is a shared primary key / foreign key relationship field. I want errors when processing type1 to cause the associated type2 to also be redirected to the error table vs being inserted.

If anyone has suggestions on how this could be done, reference articles, etc... please let me know.

Thanks.

Perhaps use a merge join on the error output of the Type1 flow together with the Type 2 data flow. Then use a conditional split to look for matches. If you have a match, you direct the Type 2 record (along with the Type1 record) down a separate error-handling flow. If you don't have a match, the Type2 records can be processed accordingly.|||

I'm trying your suggestion and I think it will work. But I am having an issue. I have my original flat file source, which I read into the SSIS package as just rows. So I do CRLF search to bring in as one column. I then send it to a derived column component after a conditional split to perform all the "substrings" to get the actual columns out of the data.

In order to do a merge join you must use sorted columns. I was able to set sorted column on the flat file data source and single column, which does me no good. I need to be able to set the sorted column on the derived columns after the data has been put into columns. Is there any way to set the sort column on a derived column? If I can do that it will solve my issues.

Thanks.

Conditional Split Transformation

Hi all,

I have set up a conditional split task which i want to use with a flat file data source. The flat file consists of multiple rows of data where the first column is an ID. The conditional split is based on the first column value.

What i'd like to know is if in the conditional split once it splits the data can the output be transformed. e.g. If one of the values coming from the flat file requires to be either split up into two values or requires to be passed into a stored procedure to manipulate it, can this be done?

Hope that makes sense.

All help is greatly appreciated, TIA.

Cheers,

Grant

Well in least words, YES!

Output of conditional split can be simply passed to any other control to manipulate in whatever way u like

|||Hi,

thanks for the reply. I have just realised what a stupid question it was. I have just dragged a constraint from the conditional loop and see that a dialog box allows you to select the output. My apologies, and thanks for the help.

Cheers,

Grant|||Hi Again,

Out of interest once i have the row of data i want to process, how would i go about doing the actually processing.

The first this i need to do is to pass once of the row values into a stored procedure and return a variable. Whta would be the best command for this. In the control flow i would have used an Execute SQL task, but this doesn't appear to be available.
Do i have to script anything like this once i have the row?

Thanks again,

Grant

Conditional Column Mapping

Hey all! I have a bunch of questions, but let's start with this one:

Incoming from my flat file, I have two columns:

employee_id
dept_id

These indicate who did the work, and for which department (people can work for more than one department). In my destination table, I have the following two columns:

employee_id_sales
employee_id_wrhs

I want to map the employee id either to employee_id_sales or employee_id_wrhs, depending on the dept_id from the flat file.

How do I specify conditional column mapping?

I'm really new to SSIS, so I might be missing something obvious.

Thanks!

-- Jim
I'd use a derived column transformation...

New column name: employee_id_sales
Expression: dept_id == 1 ? employee_id : NULL(DT_WSTR,20)

New column name: employee_id_wrhs
Expression: dept_id == 2 ? employee_id : NULL(DT_WSTR,20)

The NULL() function should represent whatever data type you are really working with. I just used DT_WSTR as an example.

Then coming out of the derived column transformation, you have your two columns that you simply map to the similarly named column in the destination.|||Outstanding! Thanks!

Cheers!

-- jim

Concatination of empty field and removal of "" symbols

Hello...

I am going crazy trying to figure out how to do this. I have a flat file which I am massaging the data and loading into a table here is an example of a line out of the flat file:

"ABC NUTRITIONAL PRODUCTS","550","","","N","FAIR OAKS","","","COLORADO SPRINGS","C0","","","","","","","","A","","",""

My problem is that I have one field which is this address in a concatinated form. The fields that do not apply to this entry are suite#, floor# and other columns which are designated by the "" characters. The final concatinated addres field looks like this:

"550""""N""FAIR OAKS"""

I would like to remove the "" characters in the concatinated string. I just don't know the best way to do this? I was told DTS had a way of removing the "" from the flat file source. Since I have not used DTS extensively I am not sure if this is true. I was wondering how in SSIS I could go about removing the "" marks without removing the "" say if someone is quoted eg. John said "This is only a test". Removing the quotation marks in this instance would be changing the data. I am not sure how to do this and any help or advice is greatly appreciated!

Thank you...

SSIS has a powerful expression language that allows you to carry out many operations, including text manipulation.

In your case, you can use this expression language in a Derived Column Transform.

-Jamie