Solved

MS SQL Integration Services import from flat file

Posted on 2011-03-11
3
606 Views
Last Modified: 2012-05-11
Hi, I am new to SSIS and need some guide lines from experts to achieve the following:
-      read flat file from ftp account. / Line by line
-      Read certain values from the line, values are on certain positions e.g.
-      Value 1, start position 2 till 10,
-      Value 2, start from 15 till 20,
-      Value 2, start from 34 to 40. And so on.
Then pass values to execute store procedure with parameters,
Repeat the execution of the SP for each line / each time with new parameters  values read from file until end of the file.
The task should run every day automatically,
Can anyone draw me some guidelines so I can easily and quickly do it?
Thank you for your help.




0
Comment
Question by:rkanabus
3 Comments
 
LVL 18

Accepted Solution

by:
lludden earned 250 total points
ID: 35110607
I would parse the flat file into a table.  You can use SSIS to import a fixed lenth field file to do this

After that, run a query through a cursor calling the SP with each line and looping through the table created in step 1
0
 
LVL 1

Assisted Solution

by:rcharlton
rcharlton earned 250 total points
ID: 35116103
Firing a stored procedure which updates rows line by line can take a long time, depending on complexity.

I've done the following:

1. Create an SSIS package, which has a Script Task. There are examples on line to do this. Have the script task use variables at the package level. This allows you to control the file names and such via an XML file. The only thing this FTP task should do is get the file from the ftp server, and drop it into a directory for step 2. Set this up as a job for SQL Agent. Use a package configuration for the variables -- file name, user name, password, etc., this will allow you to change it without having to redeploy the package.

2. Create an SSIS package which picks up the file(s). A Data flow task should import the file, do all the cleanup, transform your data into fields and such, and insert the values into a table. The Derived Column editor will alllow you to extract the substrings of the line(s) into each destination field. Remember to convert your fields as well (String beceomes BigInt, etc.) prior to inserting into your table(s). Use a SQL Client destination to insert values into your table(s). If you have mutiple tables that the data is bound for, split the data off for those table destinations, use a Union to exclude the fields you don't want (that way you don't get warnings about unsed fields), and then insert into your table(s). Unless you need some sort of special logic in your stored procedure, and all your doing is dumping the data into the table(s), you won't need a stored procedure. Also, if you STILL need to perform some sort of special logic for the stored procedure, and you think that SSIS can't handle it, then put that logic into an INSERT trigger on the table -- that way each time SSIS commits your rows, your committing 10,000 rows at a time (much faster), rather than processing one row at a time (much slower).

SSIS can handle virtually any type of task you can put to it -- even data rules and / or business rules. Consider doing that for your imports -- I've done it, and it works nicely.
0
 

Author Comment

by:rkanabus
ID: 35138827
Hi, Thank you for explanations, it is really helpful.

As suggested I have started to importing the file into SQL Destination, but each time I test and execute the package I get the error:

 flow
It stops on SQL Server Destination.

Can any one tell me why?

0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now