Passing error messages from stored procedure to osql command

I have a process that is running on the os. This process is picking up FTP files every 5 min, it renames them so not to confuse them with the new files, copies all renamed files into one file to be used by bulk insert, runs the bulk insert to populate a table, and then runs the stored procedure that scrubbing the data and insert it into another table. For every transaction that I do in my stored procedure, I do the error checking as follows:

IF @.@.error <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN

If my stored procedure encounters an error, return statement will stop it from running. If this happens, I need to stop the process that is running on the os as well.

Questions:

How can that be accomplished?

How to restart the stored procedure ones the error has been corrected?

Thank you for your help.Use "/b" with your OSQL, and use RAISERROR in your error trapper. In the batch file check "ERRORLEVEL 1":

osql ..... /b
if errorlevel 1 goto blah-blah|||Do you have a sample that I can look at? Thanks|||Watch out for word wrapping.
This one runs DBCC INDEXDEFRAG on specified server + database.

@.echo off
set server=%1
set db=%2
set uid=-U%3
set pwd=-P%4
if "%1"=="" goto ServerError
if "%2"=="" set db=master
if "%3"=="" set uid=-E
if "%4"=="" set pwd=
echo Checking for existence of a view on server %server% database %db%...
osql -S %server% %uid% %pwd% -d %db% -l 1 -Q"if object_id('dbo.vw_DBCC_INDEX_DEFRAG') is not null drop view dbo.vw_DBCC_INDEX_DEFRAG" -b
if errorlevel 1 goto LoginFailure
echo Creating a view on server %server% database %db%...
osql -S %server% %uid% %pwd% -d %db% -i"Create_vw_DBCC_INDEX_DEFRAG.SQL" -b
if errorlevel 1 goto CreateViewError
if "%uid%"=="-E" set uid=-T
echo Generating the final script for server %server% database %db%...
bcp %db%.dbo.vw_DBCC_INDEX_DEFRAG out %server%_%db%_DBCC_INDEX_DEFRAG.SQL -S %server% %uid% %pwd% -c
if "%uid%"=="-T" set uid=-E
echo Processing INDEXDEFRAG script on %server% database %db%...
osql -S %server% %uid% %pwd% -d %db% -i %server%_%db%_DBCC_INDEX_DEFRAG.SQL -h-1 -n -w 256 -o %server%_%db%_DBCC_INDEX_DEFRAG.LOG -b
if errorlevel 1 goto ScriptProcessingError
echo Check %server%_%db%_DBCC_INDEX_DEFRAG.LOG for any errors!
goto end
:ServerError
echo No server and/or database specified!
echo Execution returned Error Code %ERRORLEVEL%
goto end
:LoginFailure
echo Failed to login to %server%!
echo Execution returned Error Code %ERRORLEVEL%
goto end
:CreateViewError
echo Failed to create vw_DBCC_INDEX_DEFRAG!
echo Execution returned Error Code %ERRORLEVEL%
goto end
:ScriptProcessingError
echo Failed to process the script: %server%_%db%_DBCC_INDEX_DEFRAG.SQL
echo Execution returned Error Code %ERRORLEVEL%
goto end
:end
@.echo on

pass filename to flatfilesource inside foreach loop

Hi,
I am using a foreach loop to go through the .txt files inside a folder.
Using a variable I can pickup the filenames the loop is going through.
At present there is a sql task inside the foreach loop which takes the filename as a parameter and passes this filename to a stored procedure.
Now I would like to add one extra step before this sql task. Would like to have a dataflow with flatfile source which connects to oledb destination.

The question is:
While in the loop, how is it possible to pass the filename to the flatfile source using the FileName variable which I have created?

Please note, this is a different question to my other post.

Many Thanks

Hi

create a flat file connection and in expression give connectionstring as the variabe you have created in for each loop

for give this flat file connection as flat file source

|||

Hi,

I've done the first part already.

Not sure what you mean by:

"for give this flat file connection as flat file source"

|||

Are all of your files going to be the same record structure / column layout? If so then all you should have to do is add the filename variable to your connectionstring property of the flat file connection manager. (I would also put the full path into this expression).

If you have done this already, is it working? If not, what errors are you getting?

|||

Hi,

The question is:
While in the loop, how is it possible to pass the filename to the flatfile source using the FileName variable which I have created?

|||

You would create the filename variable at the package level and use it in both the flat file connection and the foreach loop.

It will set the variable in the loop and use the variable in the connection manager.

In other words, in the foreach loop, make sure that the foreach file enumerator is selected, choose the file name convention you would like to return (preferably fully qualified if you are using this with a flat file connection), go to the variables tab and choose your variable name (User::FileName which should be declared at the package level). In your flat file connection go to your expressions and choose connectionstring as the property to edit. Select the User::FileName as the variable.

That should do it.

pass filename

Using SSIS foreach loop I get the files names inside a folder on the network.
How do I pass this variable i.e. file name to a stored procedure?
Thanks

I've achived something similar to this by doing the following:

(i've assumed you've already got to the stage of populating the variable with the name of the file)

Within your loop:-

1) add an execute sql task and enter the following in your SQLStatement section: - EXEC yourproc ? (the ? is the placeholder for the parameter)

2) set up the parameter to your variable in the Parameter Mapping section remembering to set the correct type. I don't believe the parameter name is important in this scenario.

Hope that makes sense.

|||

This is the error I get:

[Execute SQL Task] Error: Executing the query "exec uspMarketValuesUploadXMLfileReader ?" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

|||Did you fill out the parameter mapping tab?|||

Yes. Still the same error.

These are my settings

SQLSourceType: DirectInput

ParameterMapping --> User::FileName

datatype: varchar

|||Use a "0" for the parameter name. Also, you won't be able to parse the query.|||

Ah, so it looks like my comment about parameter name not mattering is not quite correct. Apologies. In the parameter mapping section, set the parameter name to be a valid sql parameter eg @.filepath. Hopefully that should do the trick.

Cheers

|||

Solved.

Had to replace 0 with ?

Thanks guys.

|||

Don't forget to mark posts as answered...

Good luck!