SSIS error

Question

SSIS error

Viewing 3 posts - 16 through 17 (of 17 total)

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

Just curious - but you stated you are using SSIS, but here you are using a stored procedure to execute a BULK INSERT?

I have to ask why you decided not to use the built-in functionality in SSIS to load the file. The file appears to be a simple CSV file - no reason you couldn't use a standard file connection. If you had use that, then a derived column transformation could be used to add the additional columns with default values and output directly to the table.

Just seems more complicated than it needs to be.

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

water490 SSC Eights! Points: 959 More actions · Answer 2

Ed B wrote:

Does this work?

'INSERT INTO DBO.DataTable
SELECT *, NULL, ''N'' AS PROCESSED_FLAG
FROM #TEMP_TABLE'

it worked! thank you so much!

This reply was modified 8 months, 3 weeks ago by water490.

water490 SSC Eights! Points: 959 More actions · Answer 3

Jeffrey Williams wrote:

Just curious - but you stated you are using SSIS, but here you are using a stored procedure to execute a BULK INSERT?
I have to ask why you decided not to use the built-in functionality in SSIS to load the file. The file appears to be a simple CSV file - no reason you couldn't use a standard file connection. If you had use that, then a derived column transformation could be used to add the additional columns with default values and output directly to the table.
Just seems more complicated than it needs to be.

when I started this project a few years ago I knew nothing about SS. I learned as I go along. In hindsight there are many things that I question now only because I have more experience now than I did when I first started.

I need to download files from SFTP server. SSIS has no way to do this with the tools so I had to create a C# script with the help of a kind member on this forum. The other thing I want is to direct the downloaded files to different folders depending on outcome. If the SP is successful then move to processed folder. If fails then move file to failed folder. I wasn't sure how to do this using the SSIS tools so I started with C#. I was already so invested in C# that I decided to finish with it.

Hindsight is always 20/20. There is probably easier way to do what I need. I know for my next project what to do and what to not do.