Clarification Required in SSIS implementation

Question

Clarification Required in SSIS implementation

prisaras

Grasshopper

Points: 14
More actions
May 15, 2008 at 9:33 am

#154355

Hi,
We need clarifications in SSIS implementation for the below mentioned queries
Requirement : 1
The flat file which is coming from the upstream server will be placed in the particular directory of the loadserver linux Box.
Then,This flat file will be moved to windows shared drive folder(s:\) by SSIS FTP task. Once the flat files comes to shared drive,It should be referred dynamically by flat file connection manager one by one for the loading process.
Problem Area :
The flat file name will be like cmbwipsc00077.dat.The Sequence number in red color will keep on changing.In flat file connection manager,We just browse through and select a file name.But it should be automated.That is,Once file is placed in shared drive folder,The unique file name should be refered automatically in order to initiate the LOADING (loading into sql server 2005 staging tables)process .
Questions:
1. Is there any way for the flat file connection manager to select filename dynamically one by one from the particular
folder?
2. Is there any other way to accomplish this requirement through SSIS?
Requirement : 2
1 .we need to validate the flat file whether it is having header and trailer information.
2.The flat file name (eg. cmbwipsc00077.dat) will have sequence number ie 00077.This sequence number(00077) will be checked against flat file header information which also has the sequence number.
eg.. flat file header content : 0HEADER AARDCMBC2007-02-25-04.22.1000077
(We should get the value from 36th position to 40th position(ie 00077) from the flat file header information and check with filename sequence numbber)
If both number matches(Header number and filename sequence number),The Loading will be initiated.Same way checking happens with Trailer.If anything goes wrong,mail will be sent and Loading will not happen.
Questions:
1. Is there any way to accomplish this validation through SSIS package tasks? If so , please provide us the details.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jim SSCrazy Eights Points: 8720 More actions · Answer 1

Your first requirement is easy. A Foreach Loop Container can be setup to pull all files (one at a time) from a particular directory you specify. Within that container, you should be able to do whatever you need to do with each file.

As far as your second requirement, validating the file, I can only assume there is a programmatic method of retrieving the first line and checking it the way you need before making a decision on how to handle the file.

prisaras Grasshopper Points: 14 More actions · Answer 2

First of all, Thanks for the inputs u have given,

1. The first requirement i implemented as you said using FOREACH enumerator task.

2. You have mentioned that it should be programmatically resolved.

My question is,

Should we write a .net script to accomplish the requirement i mentioned and calling it by Script Task in SSIS? Or Is there any other way to do this?

Appreciate if you resolve this too.

With Regards

Prabhu.M

Jim SSCrazy Eights Points: 8720 More actions · Answer 3

Your 2nd requirement is where I'm hoping someone else can chime in. I have never had a requirement to do conditional control flow in an SSIS package. We have always proceeded from the standpoint that if any item fails, the entire package fails. You can probably look into Event Handlers. These should allow you to control the execution flow based on a task outcome. Sorry I do not have any further expertise on this.