SSIS and running on a schedule

Question

Post reply

SSIS and running on a schedule

Darryl Wilson

Mr or Mrs. 500

Points: 526
More actions
August 31, 2011 at 11:44 am

#247360

This is my first attempt at SSIS, unfortunately its for work. I have a folder with csv's in it, i need a script to loop through this and import into the database once every month.
I have managed to script the bulk insert, but how do i now loop through the files and pass the name of each file into the SP that runs the bulk insert and run on a schedule.
Many thanks
Darryl Wilson
darrylw99@hotmail.com

Viewing 9 posts - 1 through 8 (of 8 total)

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

Neal Sivley SSCommitted Points: 1766 More actions · Answer 1

Neal Sivley

SSCommitted

Points: 1766

August 31, 2011 at 1:25 pm

#1375667

Use the for each loop container.

Darryl Wilson Mr or Mrs. 500 Points: 526 More actions · Answer 2

I am totally new to SSIS, i have no idea even where to start.

I know i need to loop through list of files in a folder and i need to run a package every month, but after that i am lost.

DW

Darryl Wilson
darrylw99@hotmail.com

Neal Sivley SSCommitted Points: 1766 More actions · Answer 3

Start with this link on the for each loop container

Once you have the package working you can create a SQL Job and schedule it to run when needed...once a month ect.

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 4

If you are new to it you will love this[/url]. Good luck!

My blog

sam.alexander Ten Centuries Points: 1135 More actions · Answer 5

You can loop through the directory using SSIS, which isn't too crazy to do, but if you're still learning SSIS and just need a quick and dirty solution you can use xp_cmdshell if you have it enabled on the server.

Here's an example:

DECLARE @tmpDIR TABLE (DirOutput NVARCHAR(255))

INSERT INTO @tmpDIR

EXECUTE xp_cmdshell N'dir e:\test1'

SELECT RIGHT(DirOutput,LEN(DirOutput)-39) FROM @tmpDIR WHERE DirOutput LIKE '%.txt'

And your output will be all files in E:\Test1 with TXT extension. You may need to play with the spacing as on our server the DIR listing is fixed width with 39 characters to the left of the file name, but I think it may be alittle different depending on which version of windows you're using. But either way once you get the DIR listing into SQL you can parse it however you please.

For more info on xp_cmdshell see http://msdn.microsoft.com/en-us/library/ms175046.aspx since there can be some security boo-yeah's you might want to read-up on.

Sam

Ashok Teotia SSCrazy Points: 2407 More actions · Answer 6

sam.alexander (8/31/2011)
You can loop through the directory using SSIS, which isn't too crazy to do, but if you're still learning SSIS and just need a quick and dirty solution you can use xp_cmdshell if you have it enabled on the server.
Here's an example:
DECLARE @tmpDIR TABLE (DirOutput NVARCHAR(255))
INSERT INTO @tmpDIR
EXECUTE xp_cmdshell N'dir e:\test1'
SELECT RIGHT(DirOutput,LEN(DirOutput)-39) FROM @tmpDIR WHERE DirOutput LIKE '%.txt'
And your output will be all files in E:\Test1 with TXT extension. You may need to play with the spacing as on our server the DIR listing is fixed width with 39 characters to the left of the file name, but I think it may be alittle different depending on which version of windows you're using. But either way once you get the DIR listing into SQL you can parse it however you please.
For more info on xp_cmdshell see http://msdn.microsoft.com/en-us/library/ms175046.aspx since there can be some security boo-yeah's you might want to read-up on.
Sam

::--EXECUTE xp_cmdshell N'dir e:\test1'

---------------------

use it like this

EXECUTE xp_cmdshell N'dir e:\test1\*.csv /b'

and it will be better if you use BCP instaead of bulk nsert in this case..

Regards

Ashok

Darryl Wilson Mr or Mrs. 500 Points: 526 More actions · Answer 7

I have managed to create a package and loop through the files by dragging a For..loop container. then added a data flow task. but the next 3 things i need to do are baffling me:

i have created a variable filename (which hopefully) contains the name of the interated file:

1. How can i insert this into a field in my output database.

2. How can I insert this filename into another table (as a lookup).

Also I had to change the variable type in my database to varchar instead of date because my input data is in the form of yyyymmdd (20110101). Is it possbible to convert this before it's inserted.

With the filename that comes in, is it possible to say if file like '__BB__.csv' then do this data flow task or if file like '__ITEM__' then do this other data flow task.

Many thanks again.

Darryl Wilson
darrylw99@hotmail.com

Neal Sivley SSCommitted Points: 1766 More actions · Answer 8

Variable Information

[/url]

I am not clear what you are asking on questions 1 and 2.

Changing the date can be done using the Data conversion flow task.

The for each loop conatiner has and enumeration configuration that will allow you to say only look for files like *BB*.csv. Using this method you would need two for each containers one for each file "type".