[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 991
  • Last Modified:

SSIS Package Automation

Hi Experts, I had a Question.Theres a remote server which spits out 3 text files and 3 csv files on to a shared drive every day and all the file names will have a name and date like "textA02/12/2009", "csvA02/12/2009" and files for the last 30 days will be stored in this shared drive. Now I have to create an SSIS Package to fetch the file for that day from the shared drive and loadsit into the concerned table in the database every day.
this has to happen daily. there are no data manipulations involved in this and the data from these files has to be loaded into the data base AS it was in the Textfile. My first problem is creating the package and the second problem is Scheduling the package to run every day. Can any one give a solution to this. Its very urgent to me..Even a small help is very much appreciated.. Thanks in Advance
0
KuldeepReddy
Asked:
KuldeepReddy
  • 10
  • 8
  • 4
  • +1
1 Solution
 
Alpha AuCommented:
You may refer to this
SSIS: Importing data from a text file using a package
http://decipherinfosys.wordpress.com/2008/07/28/ssis-importing-data-from-a-text-file-using-a-package/

for the scheduling part, you can made the ssis package to run everyday by sql server job agent.
Scheduling SSIS Packages with SQL Server Agent
http://decipherinfosys.wordpress.com/2008/09/17/scheduling-ssis-packages-with-sql-server-agent/

hope this help
0
 
FiveForFightingCommented:
You'll need to use the BIDS (Business Intelligence Design Studio) to create your SSIS package.  It is installed w/ the client tools like SSMS.

One tool from the toolbox in the SSIS designer that can be very beneficial to look at is the For-Each Loop Container.

Once you create the package in the designer - you can connect to Integration Services on your server  and import the Package.dtsx file (or similar name).  

Scheduling the job to run is simple too - once you know how.  Look under your SQL Agent \ Jobs Node in SSMS.  Create a new job and add a step.  In the step you can specifiy that you want to run an SSIS package.  You enter all the needed info and set the schedule and away you go.

Please don't hesitate to ask if you need clarification or want more information.  Good luck!
0
 
KuldeepReddyAuthor Commented:
But the name of the Test/Csv file changes every day on the shared drive. Like if its today it will be like "textA-02/12/2009" and tomorrow it will be "textA-02/13/2009... like that so the source file will change every day so what type of logic should I write to take the source every day and files for 30 days will be stored on the shared drive. So what logic should I use in the source to identify the correct file for that day.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
FiveForFightingCommented:
Yes - the file names change - shouldn't be a problem.

What the ForEach Loop will give you is a way to iterate through all the files that are in the directory.  The file name gets mapped into a package variable that you can then test to see if the date string in the name is something you want to process or not.

Personally I'd probably log the file name in a table after I process it so that I can simply query the table to see if the file name the loop currently has has already been processed.

Do all the text files have the same file layout - i.e. known columns, etc.
0
 
FiveForFightingCommented:
I am trying to attach a Pacake.txt file that you'll need to rename to .dtsx and then open w/ BIDS. You'll need to add your SQL Server Connection, etc... but it may get you started.
0
 
FiveForFightingCommented:
Can't get it uploaded... but can email it to you if you want.
0
 
KuldeepReddyAuthor Commented:
Thanks for your concern my email id is mkr.mkreddy@gmail.com
0
 
PedroCGDCommented:
To  Scheduling the package create a SQL Job.

You can get the file dinamically... give more details about what you have, and what you want and I create a SSIS Package example for you.

regards,
Pedro
www.pedrocgd.blogspot.com
0
 
KuldeepReddyAuthor Commented:
Hi Pedro, I have 6 files coming in every day and these 6 files will be stored in a shared folder on the network. all files are comma Delimited files, I have to load this data into 6 tables in my Sql Server DB. The file naming conventions are "TableA1-02/13/2009", "TableB-02/13/2009"... And the next day they will be "TableA1-02/14/2009", "TableB-02/14/2009". Now I have to create some SSIS packages to Load this data into the SS database.  and the second thing is I have to create a job in Sql Agent and to schedule this every day.
My doubt is regarding the creation of the package. even I dont know proper basics of SSIS.
0
 
PedroCGDCommented:
Check the package I made for you to get data from files.
Add attached package to an existent project
Update path from share inside ForEachLoop container
Test!
Helped?
regards,
Pedro
www.pedrocgd.blogspot.com
SSIS-Interface1.JPG
SSIS-Interface2.JPG
Package-EE89-dtsx.txt
TableA1-02132009.txt
TableA1-02142009.txt
0
 
PedroCGDCommented:
0
 
KuldeepReddyAuthor Commented:
Thanks Pedro..
0
 
PedroCGDCommented:
when you try, give feedback!
Cheers!
Pedro
0
 
KuldeepReddyAuthor Commented:
Hi Pedro, I have downloaded those files which you have attached and renamed the first one to .dtsx and opened up in VS2005. I have configured the Test file source and pointed it to one test filewhich u hav attached and executed the package and my doubt now is (It may sound wierd) Actually what if the file name changes tomorrow and the second issue is configuring the destination. Do I need to add the destination in that package..
0
 
PedroCGDCommented:
1. "Actually what if the file name changes tomorrow"
No problem. The example I made read all textfile in a share

2. "and the second issue is configuring the destination. Do I need to add the destination in that package.."
Yes, you need to add a destination to insert data
0
 
KuldeepReddyAuthor Commented:
Thanks, I will connect the destination to the union all transformation in the package
0
 
PedroCGDCommented:
Good!
:-)
0
 
KuldeepReddyAuthor Commented:
Hi Pedro, Thanks for the solution which you have provided to me, To give a feed back, I am still confused a bit(Pls dont think otherwise I am a beginner). I am here attaching the exact source files I will be getting everyday on to the shared drive. And the Naming convention of those files are <TabName_Date_Time.Dat>. Can I use the same package which you have sent earlier to read these files.Should I create 4 seperate packages for the four seperate tables. And all these files will be saved in the same place/ folder.  And the files wont have Headers. And the Data will be in Double Quotes Seperated by Pipe(|). I have to first load these tables into a temporary table and then into the mail table Since I have to do Insert/Update based on the ID Column. Please guide me on this.. Thanks in Advance
SrcFiles.zip
0
 
PedroCGDCommented:
Your delimiter is not usuall and you need to use script component as source in spite os flat file source.
I made an example for you for the file Senroll_20090217_114047.txt.

If the schema is the same for all other files you can still use ForEach container, if not, You should create 3 different dataflows

Helped?
regards,
Pedro
www.pedrocgd.blogspot.com

SSIS-ScriptSource.JPG
SSIS-DataViewer.JPG
Package-EE89b-dtsx.txt
0
 
KuldeepReddyAuthor Commented:
Thanks Pedro, All the target tables are on the same Schema and in the same server ansd all me source files will be kept in the same shared folder.
0
 
PedroCGDCommented:
Ok.
Sources differents? Use 3 different script components as sources as I did in example
Files are in the same share? Use ForEach container to read each file and moved the appropriated dataflow.
Target is the same? ok use always the same destination... repeated in each dataflow.

answered your question?
regards,
Pedro
0
 
KuldeepReddyAuthor Commented:
This is the best solution
0
 
KuldeepReddyAuthor Commented:
Hi Pedro, The package which you have sent is fine But I am Very much confused in making use of that, I mean changing the Variable and the paths and its throwing some errors, so I wrote a stored proc and even this is throwing the error.
Pls have a look at this Question which I nave posted on EE now

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_24185927.html

Thanks in Advance
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 10
  • 8
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now