May 2, 2008 at 11:08 pm
Hi all,
I am pretty much new to the sql server, I have a problem.. Every day i will get around 10 text file from some other source and they are named as deposit followed by the date and time. I have to create job to transfer the data from the text files to one of the table in my server. This job should be run for every six hours. Once the job is done all the data from my text files, which i have got before the execution of job should be dump into my table. can anyone suggest me how to create a job ?
May 3, 2008 at 10:53 am
This question is more for the SSIS forum than the backup forum.
High level will be:
-Create two connection managers inside you ssis package. 1) connection to SQL, 2) connection to a flat file (point it to one of the deposit files that already exist so you can set up your pipeline.
-Create two package variables: 1) fileList (Object type) 2) filepath (String type)
1. Create a script task to loop through a directory to obtain the list of files. You could return that list of files as an object to an object variable within the package.
Dim fileList as object
'code to populate the variable
'set the package variable so the list can be used by other components
dts.variables("User::fileList").value = fileList 'make sure to create this as an object and set it as a read/write variable on the script task
2. Create a for each loop to loop through the file list contained in the variable (look up for the for each container on MSDN). Map the filepath variable to the resultset of the object in the for each container settings.
3. Create a script task inside the for each to dynamically set the filepath property on the "flat file connection" connection manager. (Look up how to dynamically set connection manager properties)
4. Create a data flow task that pulls from the flat file connection manager into your SQL table.
In the end you would have this workflow:
1. Script Task 1
2. For Each Container
a. Script Task 2
b. Data flow task
That is the high level, but that should be able to get you in the right direction.
The basic idea is that you want the flat file connection manager to be able to get set dynamically with each new file (granted that they are all formatted the same way).
Hope that helps,
Steve
May 3, 2008 at 5:46 pm
thanks... trying to get ur answer
May 3, 2008 at 8:41 pm
can i accomplish the same task by using xp_cmdshell and bulk insert... because we are using sql server 2000
May 3, 2008 at 9:15 pm
you left that important detail out. I haven't done too much recently with 2000, but i can't see why you couldn't use a bulk insert, but you'd still have to get the file names somehow...
you may be able to capture output from xp_cmdshell and use that output to get the list of file names. I also don't have familiarity with xp_cmdshell because we very rarely use it in my company.
Sorry.
May 3, 2008 at 9:16 pm
I would really post this on a 2000 forum somewhere. This is a 2005 forum discussing backups.
May 3, 2008 at 11:56 pm
thanks for the reply
i would like to learn about variables in ssis ... could you please provide me some links
May 4, 2008 at 9:39 am
susheereddy (5/3/2008)
can i accomplish the same task by using xp_cmdshell and bulk insert... because we are using sql server 2000
DTS in SQL Server 2000 is kinda like SSIS in SQL Server 2005...
I will admit... I don't know how to use DTS because I usually use Bulk Insert, as you suggest, because it's faster.
What you ask isn't all that difficult, but I do have a couple of questions... you wrote...
they are named as deposit followed by the date and time
1. Would list list a dozen file names so that we can see the real format, please?
2. You need to tell us what format the files are in... are they comma separated, tab separated, or ??? If the rows are "fixed length" in nature, please provide the record layout.
3. Once you've successfully loaded a file, what do you want to do with it? Do you want to move the file to another directory or ???
4. Is there any personal information in these files? If not, can you attach one of the files to a post so we can "play" for a solution for you?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 4, 2008 at 10:16 am
the files that are coming are text file and we have a format file.. so no need of worrying about format. I just wanna accomplish that task even if we create one or two new folders that's not a problem
thanks
May 4, 2008 at 10:44 am
Here is an idea that might work for you. Take a look at Powershell and SMO. With Powershell, you can easily create a script to loop through a directory and pull files to be processed and then you can use SMO to access SQL Server and bulk insert the data.
Even without SMO - you could call bcp.exe from the command line.
Simple powershell script to loop through a directory and call bcp for each file:
PS C:> set-location yourdirectory; get-childitem | % {bcp.exe -i $_.Name ...}
Using SMO, you would set a reference to the SMO library - build up the BULK insert command, connect to SQL Server and run the command using ExecuteNonQuery method.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 4, 2008 at 9:23 pm
susheereddy (5/4/2008)
the files that are coming are text file and we have a format file.. so no need of worrying about format. I just wanna accomplish that task even if we create one or two new folders that's not a problemthanks
Then, answer my questions, please...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply