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!

pass all data in a file as a string into a stored procedure

Hi,
Using SSIS 2005 how is it possible to loop through a folder on the network, look at each file, pass the data inside each file as a string into a stored procedure.
Thanks

Use the For Each Loop container to enumerate the files. I'd probably just use a Script Task top open the file and grab the data into a variable. Use the Exec SQL Task to call the procedure with a parameter mapped to the variable.

If it was large amounts of data then I'd probably put the procedure call in the script too to more effectively handle the large size, and save passing it around as much. Use an ADO.NET connection manager, you can work with this in the script.

Partner transaction manager has disabled its support for remote/network transactions

sorted on LAN.The partner transaction manager has disabled its support for remote/network
transactions.
I have tried this but did not work:
First verify the "Distribute Transaction Coordinator" Service is
running on both database server computer and client computers
1. Go to "Administrative Tools > Services"
2. Turn on the "Distribute Transaction Coordinator" Service if it is
not running
If it is running and client application is not on the same computer as
the database server, on the computer running database server
1. Go to "Administrative Tools > Component Services"
2. On the left navigation tree, go to "Component Services > Computers
> My Computer" (you may need to double click and wait as some nodes
need time to expand)
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client",
"Allow Inbound/Outbound", "Enable TIP" (Some option may not be
necessary, have a try to get your configuration)
7. The service will restart
8. BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN'T WORK
(This is the thing drove me crazy before)
On your client computer use the same above procedure to open the
"Security Configuration" setting, make sure you check "Network DTC
Access", "Allow Inbound/Outbound" option, restart service and computer
if necessary.
On you SQL server service manager, click "Service" dropdown, select
"Distribute Transaction Coordinator", it should be also running on
your server computer.
Thank you
Ed|||sorted on LAN.

Partner transaction manager has disabled its support for remote/network transactions

The partner transaction manager has disabled its support for remote/network
transactions.
I have tried this but did not work:
First verify the "Distribute Transaction Coordinator" Service is
running on both database server computer and client computers
1. Go to "Administrative Tools > Services"
2. Turn on the "Distribute Transaction Coordinator" Service if it is
not running
If it is running and client application is not on the same computer as
the database server, on the computer running database server
1. Go to "Administrative Tools > Component Services"
2. On the left navigation tree, go to "Component Services > Computers
> My Computer" (you may need to double click and wait as some nodes
need time to expand)
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client",
"Allow Inbound/Outbound", "Enable TIP" (Some option may not be
necessary, have a try to get your configuration)
7. The service will restart
8. BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN'T WORK
(This is the thing drove me crazy before)
On your client computer use the same above procedure to open the
"Security Configuration" setting, make sure you check "Network DTC
Access", "Allow Inbound/Outbound" option, restart service and computer
if necessary.
On you SQL server service manager, click "Service" dropdown, select
"Distribute Transaction Coordinator", it should be also running on
your server computer.
Thank you
Edsorted on LAN.