Showing posts with label filename. Show all posts
Showing posts with label filename. Show all posts

Friday, March 9, 2012

pass variable with a space

Hi,

I am passing a filename to a command in sql.

The problem is that it gives an error if the @.FullFilePath has a space in it.

set @.cmd = 'dtexec /f ' + @.FullPackagePath + ' /set \Package.Variables[User::FileName].Properties[Value];' + @.FullFilePath + '"'

print @.cmd

Thanks

As with everything, use quotes... ""|||

This is the stored procedure which passes the filename

uspCEMTradeExecutePackage 'd:\sysappl\CEM\SSIS\Imports\Trades\csa.dtsx', 'CSA data.csv'

Notice the filename, i.e. 'CSA data.csv'

It has a space in it.

Do you mean the call to the stored procedure should be:

uspCEMTradeExecutePackage 'd:\sysappl\CEM\SSIS\Imports\Trades\csa.dtsx', '"CSA data.csv"'

this does not seem correct.

Thanks

|||

That is correct, you need the single quotes outside of the double quotes. When you call DTEXEC with a variable or path that has spaces in it, it has to be wrapped in double quotes.

Or you could change the command line to include the double quotes:

Code Snippet

set @.cmd = 'dtexec /f ' + @.FullPackagePath + ' /set \Package.Variables[User::FileName].Properties[Value];"' + @.FullFilePath + '"'

Saturday, February 25, 2012

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!