Share via

ssis need advise

Farhan Jamil 421 Reputation points
2020-12-21T18:54:07.133+00:00

Hi Guys

I have an SSIS package which is already running without any issues.

My existing package is copying data from csv to sql server with transformations as per requirement.
When the file is copied from csv to sql , I am also archiving the file at the same time.

These are all running ok.

The only gap i see is that I have to manually run the job as I cant automate it because we don't know the frequency of the file receiving..

What I am sure off that we will receive one file at a time. Their wont be two files in our directory at the same time.
Is their a possibility that i can automate the step.

What i was thinking(if possible in the simplest of way possible) is when I receive the file and when the file is present in our file directory I then want to trigger my sql job which will run my ssis package and then dump the data into sql server.

One solution i can see is WMI EVENT WATCHER. But is thier any other alternative i can use apart from WMI EVENT WATCHER.

Any simplest way of achieving this task.. Please let me know

Thanks for the help and advise

Regards
Farhan Jamil

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,525 questions
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Monalv-MSFT 5,896 Reputation points
    2020-12-22T06:02:36.387+00:00

    Hi @Farhan Jamil ,

    The only gap i see is that I have to manually run the job as I cant automate it because we don't know the frequency of the file receiving..
    What I am sure off that we will receive one file at a time. Their wont be two files in our directory at the same time.
    Is their a possibility that i can automate the step.

    May I know if these files have same structures?

    If so, we can use Foreach Loop Container to check if the files exist and then extract the existing files to load to the sql table.

    Please refer to the following link and pictures:

    FOREACH LOOP CONTAINER IN SSIS WITH AN EXAMPLE STEP BY STEP

    50235-cf.png

    50333-foreachfiles.png

    50335-expressionforconnectionstring.png

    Best Regards,
    Mona

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues in November--What can I do if my transaction log is full?
    Hot issues in November--How to convert Profiler trace into a SQL Server table?

    0 comments
  2. Farhan Jamil 421 Reputation points
    2020-12-22T09:58:50.697+00:00

    HI Monalv-msft

    Thank you for your reply. I have a script task which checks if the file exist or not and then I have a for each loop container which loops through all the file. and then after ETL processing dumps data into sql server one after the other and then archives the file processed sequentially at the same time. This is all happening in one ssis package.

    I am not sure whether i have stated the requirement correctly or not

    but the issue i see is

    1. we dont know the frequency of the file receiving in our location.
    2. The input file is auto system generated which will ftp into our location straight after once the file is generated.

    It could be a possibility that the file may be generated in 5 min and be present into our ftp location in the next 5 min,.

    The next file may be generated in 10 min ,next one may be in 3 min so on and so forth.

    This is the step right now which requires manual intervention. So i monitor the file directory and process the file by running the SQL JOB.
    and i dont know whether this step can be automated or not.

    Regards
    Farhan Jamil

  3. Jeffrey Williams 1,891 Reputation points
    2020-12-22T23:01:48.403+00:00

    I think you need to make sure that multiple files are not named the same - that way your package can look in the directory for any matching files and loop over each one and process it.

    You can then schedule your job to run every hour (or more frequently if needed) and anything delivered into the directory will be processed.

    BTW - you can also setup the FTP process in SSIS so it pulls the file(s) from the FTP server and processes the file...that way it is all contained in a single package.

    0 comments
  4. shayshank rathore 1 Reputation point
    2021-05-26T09:13:09.323+00:00

    Hi @Farhan Jamil

    let me try to summarize your question and present you an answer with it.
    Question:- you get the files from the FTP location which are in CSV format and you are loading them in your SQL server using SSIS package. you want to automate that process.

    Answer:- you can schedule a job in SQL server 2008. please follow below link for that.
    https://www.webdevdesigns.com/newsroom/scheduling-an-ssis-package-in-sql-server-2008/

    1. you can schedule that every hour to run or you can fire that job when you receive mail that file has arrived at the location.
    2. you can create BATCH file to run.

    please let me know if my answer was helpful to you.

    Thanks,
    Shayshank.

    0 comments